Re: Checkpoint Tuning Question

On Wed, Jul 8, 2009 at 12:50 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dan Armbrust <daniel(dot)armbrust(dot)list(at)gmail(dot)com> writes:
>> However, once the checkpoint process begins, I get a whole flood of
>> queries that take between 1 and 10 seconds to complete.  My throughput
>> crashes to near nothing.  The checkpoint takes between 45 seconds and
>> a minute to complete.
>
> You sure this is 8.3?  It should spread out checkpoints over a couple of
> minutes by default.  [thinks...]  Maybe you need to increase
> checkpoint_segments some more.  If it's forcing the checkpoint in order
> to hold down the number of WAL segments used up, that would explain a
> fast checkpoint.
>
>                        regards, tom lane
>

Just checked - currently running 8.3.4 on the system I'm testing on.

With checkpoint_segments set to 10, the checkpoints appear to be
happening due to checkpoint_timeout - which I've left at the default
of 5 minutes.

If I double my test load, I end up with checkpoints happening about
every 4 minutes, with the log message that I read to indicate that it
used up all 10 segments.

So not much I can do to keep the checkpoint process from causing a
burst of slow queries?

Thanks,

Dan