On Fri, Oct 2, 2009 at 3:55 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Scott Marlowe <scott.marlowe@xxxxxxxxx> writes: >> The session servers we have at work are a perfect match for this. By >> increasing checkpoint segments to 100 (or more), timeout to 60 >> minutes, and setting completion target lower (currently 0.25) we have >> reduced our IO wait from 10 to 15% to nearly nothing. These are >> databases that update the same rows over and over with session data as >> the user navigates the system, so writing things out as early as >> possible is a REAL bad idea. > >> I found that lowering checkpoint completion target was what helped. >> Does that seem counter-intuitive to you? > > Once the checkpoint completion target time is high enough that the > checkpoint-induced I/O is just background noise for you, increasing the > target further won't make for any noticeable further improvement. I'm > not sure I see how it would make things *worse* though. Maybe, even > though the I/O wait is "nearly nothing", the I/O is still forcing enough > extra seeks to slow normal disk operations? If so, getting the > checkpoint out of the way sooner so that you can get back to full speed > operation sooner might be better than reducing the rate of checkpoint > I/Os below the nearly-noise level. I'm just guessing about that though. > What were you measuring --- the performance within checkpoint, the > performance outside it, or the whole-cycle average? I was measuring it over an extended period, say a few hours. This db is small enough to fit in memory easily (1.5Gig on a machine with 6Gig ram doing nothing else) so all the io is basically blocks out, with none in. Since the checkpoints should have a LOT of the same records updated over and over, I'm guessing that a very low completion target lets it collect a lot of those together and just write out the last one. The nice thing is I can benchmark one of these machines against the other, since they're basically identical twins doing the same job, and see how changes like this affect them. At first I was at something like 0.5 completion target, and increasing the checkpoint segments did make an effect, but lowering the completion target was as much of a gain as the increased checkpoint segments. So, I think the tuning of a small db like this that can fit in memory is a whole different ball game than one that's several times larger than memory. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
