Re: Recommended optimisations slows down PostgreSQL 8.4

On 08/11/2011 07:35 PM, Waldo Nell wrote:
> Please explain why the system is slower with the recommended values for these two settings?

If the other parameters are at their defaults, the server is probably
executing a checkpoint every few seconds running your test.  I'd wager
your log is filled with checkpoint warnings, about them executing too
frequently.

Each time a checkpoint happens, all of shared_buffers is dumped out.  So
if you increase shared_buffers, but don't space the checkpoints out
more, it just ends up writing the same data over and over again.  Using
a smaller shared_buffers lets the OS deal with that problem instead, so
it actually ends up being more efficient.

Basically, you can't increase shared_buffers usefully without also
increasing checkpoint_segments.  All three of shared_buffers,
wal_buffers, and checkpoint_segments have to go up before you'll see the
expected benefit from raising any of them; you can't change a parameter
at a time and expect an improvement.  Try this:

shared_buffers=512MB
wal_buffers=16MB
checkpoint_segments=64

And see how that does.  If that finally beats your 50 minute record, you
can see if further increase to shared_buffers and checkpoint_segments
continue to help from there.  Effective upper limits on your server are
probably around 2GB for shared_buffers and around 256 for
checkpoint_segments; they could be lower if your application uses a lot
of transitory data (gets read/written once, so the database cache is no
better than the OS one).

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us