[Please copy the mailing list on replies.] On Mon, Mar 06, 2006 at 09:38:20PM +0100, Joost Kraaijeveld wrote: > Michael Fuhr wrote: > > Have you tweaked postgresql.conf at all? If so, what non-default > > settings are you using? > > Yes, I have tweaked the following settings: > > shared_buffers = 40000 > work_mem = 512000 > maintenance_work_mem = 512000 > max_fsm_pages = 40000 > effective_cache_size = 131072 Are you sure you need work_mem that high? How did you decide on that value? Are all other settings at their defaults? No changes to the write ahead log (WAL) or background writer (bgwriter) settings? What version of PostgreSQL are you running? The paths in your original message suggest 8.1.x. > >>> Are your test results more consistent > > psql -c checkpoint > > pgbench -c 10 -t 150 test > > psql -c checkpoint > > pgbench -c 10 -t 150 test > > psql -c checkpoint > > pgbench -c 10 -t 150 test > > OK, that leads to a consistant hight score. I also noticed that > "psql -c checkpoint" results in I/O on the database partition but > not on the partition that has the logfiles (pg_xlog directory). Do > you know if that how it should be? A checkpoint updates the database files with the data from the write-ahead log; you're seeing those writes to the database partition. The postmaster does checkpoints every checkpoint_timeout seconds (default 300) or every checkpoint_segment log segments (default 3); it also uses a background writer to trickle pages to the database files between checkpoints so the checkpoints don't have as much work to do. I've been wondering if your pgbench runs are being affected by that background activity; the fact that you get consistently good performance after forcing a checkpoint suggests that that might be the case. If you run pgbench several times without intervening checkpoints, do your postmaster logs have any messages like "checkpoints are occurring too frequently"? It might be useful to increase checkpoint_warning up to the value of checkpoint_timeout and then see if you get any such messages during pgbench runs. If checkpoints are happening a lot more often than every checkpoint_timeout seconds then try increasing checkpoint_segments (assuming you have the disk space). After doing so, restart the database and run pgbench several times without intervening checkpoints and see if performance is more consistent. Note that tuning PostgreSQL for pgbench performance might be irrelevant for your actual needs unless your usage patterns happen to resemble what pgbench does. -- Michael Fuhr
