[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