Re: Limit of bgwriter_lru_maxpages of max. 1000?

On Fri, Oct 2, 2009 at 3:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> 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.