From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Recommended optimisations slows down PostgreSQL 8.4
Date: Thu, 11 Aug 2011 21:17:02 -0400
Message-ID: <4E447F0E.60208@2ndQuadrant.com> (raw)
In-Reply-To: <D9BE4D00-9571-4189-B24A-EC446895EC70@telkomsa.net>
References: <D9BE4D00-9571-4189-B24A-EC446895EC70@telkomsa.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: Recommended optimisations slows down PostgreSQL 8.4
In-Reply-To: <4E447F0E.60208@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox