Re: dbt-2 tuning results with postgresql-8.3.5

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD