On Sat, 20 Dec 2008, Mark Wong wrote:
Here are links to how the throughput changes when increasing shared_buffers: http://pugs.postgresql.org/node/505 My first glance takes tells me that the system performance is quite erratic when increasing the shared_buffers.
If you smooth that curve out a bit, you have to throw out the 22528MB figure as meaningless--particularly since it's way too close to the cliff where performance dives hard. The sweet spot looks to me like 11264MB to 17408MB. I'd say 14336MB is the best performing setting that's in the middle of a stable area.
And another series of tests to show how throughput changes when checkpoint_segments are increased: http://pugs.postgresql.org/node/503 I'm also not what to gather from increasing the checkpoint_segments.
What was shared_buffers set to here? Those two settings are not completely independent, for example at a tiny buffer size it's not as obvious there's a win in spreading the checkpoints out more. It's actually a 3-D graph, with shared_buffers and checkpoint_segments as two axes and the throughput as the Z value.
Since that's quite time consuming to map out in its entirety, the way I'd suggest navigating the territory more efficiently is to ignore the defaults altogether. Start with a configuration that someone familiar with tuning the database would pick for this hardware: 8192MB for shared_buffers and 100 checkpoint segments would be a reasonable base point. Run the same tests you did here, but with the value you're not changing set to those much larger values rather than the database defaults, and then I think you'd end with something more interesting. Also, I think the checkpoint_segments values >500 are a bit much, given what level of recovery time would come with a crash at that setting. Smaller steps from a smaller range would be better there I think.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
