On Mon, Dec 22, 2008 at 12:59 AM, Greg Smith <gsmith@xxxxxxxxxxxxx> wrote: > 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. The shared_buffers are the default, 24MB. The database parameters are saved, probably unclearly, here's an example link: http://207.173.203.223/~markwkm/community6/dbt2/baseline.1000.1/db/param.out > 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. I should probably run your pgtune script, huh? Regards, Mark -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
