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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us