On Fri, Oct 2, 2009 at 2:19 PM, Greg Smith <gsmith@gregsmith.com> wrote:
> On Fri, 2 Oct 2009, Gerhard Wiesinger wrote:
>
>> In my experience flushing I/O as soon as possible is the best solution.
>
> That what everyone assumes, but detailed benchmarks of PostgreSQL don't
> actually support that view given how the database operates. We went through
> a lot of work in 8.3 related to how to optimize the database as a system
> that disproved some of the theories about what would work well here.
>
> What happens if you're really aggressive about writing blocks out as soon as
> they're dirty is that you waste a lot of I/O on things that just get dirty
> again later. Since checkpoint time is the only period where blocks *must*
> get written, the approach that worked the best for reducing checkpoint
> spikes was to spread the checkpoint writes out over a very wide period.
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.
> In most cases where people think they need more I/O from the background
> writer, what you actually want is to increase checkpoint_segments,
> checkpoint_completion_target, and checkpoint_timeout in order to spread the
> checkpoint I/O out over a longer period. The stats you provided suggest
> this is working exactly as intended.
I found that lowering checkpoint completion target was what helped.
Does that seem counter-intuitive to you?
