On Tue, Feb 24, 2009 at 11:42 AM, Greg Smith <gsmith@xxxxxxxxxxxxx> wrote:
On Mon, 23 Feb 2009, Scott Marlowe wrote:Right, the useful thing to do in this case is to take a look at how big all the relations (tables, indexes) involved are at each of the steps in the process. The script at http://wiki.postgresql.org/wiki/Disk_Usage will show you that. That will give some feedback on whether the vacuum/reindex methodology is really doing what you expect, and it will also let you compare the size of the table/index with how much RAM is in the system.
well that's pretty normal as the indexes grow large enough to not fit in cache, then not fit in memory, etc...
Have you done any tuning of the postgresql.conf file? If you haven't increased shared_buffers substantially, you could be seeing buffer cache churn as the CPU spends all its time shuffling buffers between PostgreSQL and the OS once the working set involved exceeds around 32MB.
Shouldn't someone have ranted about RAID-5 by this point in the thread?
--
* Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
Right, i have done some more testing and I think its pretty conclusive.
1. Start with a known good copy of the database (some 3gb in size)
2. Run the Jmeter tests until ~7000 new rows are inserted equally over 3 tables. At this point performance goes to hell
3. delete the ~7000 rows from the db without re-indexing, (manually) analyzing or anything of the sort.
4. performance instantly returns to that of before the tests began (optimum).
So im thinking as Scott said it could be buffer/cache size filling up?
Here is my postgresql.conf, perhaps someone can make a few pointers.
The hardware is a Quad Xeon 2.0GHZ with 8GB RAM and 15K RPM SAS drives in RAID 5 (i know raid 5, dont tell me)
max_connections = 400
shared_buffers = 2048MB
temp_buffers = 8MB
max_prepared_transactions = 10
work_mem = 8MB
maintenance_work_mem = 128MB
max_stack_depth = 4MB
vacuum_cost_delay = 0
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 200
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
fsync = on
synchronous_commit = on
wal_sync_method = fsync
full_page_writes = on
wal_buffers = 128kB
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
track_counts = on
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = 20
autovacuum_vacuum_cost_limit = -1
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
escape_string_warning = off
