Re: Checkpoints and slow queries

Elanchezhiyan Elango <elanelango@gmail.com> writes:
>> The problem is that while this makes the checkpoints less frequent, it
>> accumulates more changes that need to be written to disk during the
>> checkpoint. Which means the impact more severe.

> True. But the checkpoints finish in approximately 5-10 minutes every time
> (even with checkpoint_completion_target of 0.9).

There's something wrong with that.  I wonder whether you need to kick
checkpoint_segments up some more to keep the checkpoint from being run
too fast.

Even so, though, a checkpoint spread over 5-10 minutes ought to provide
the kernel with enough breathing room to flush things.  It sounds like
the kernel is just sitting on the dirty buffers until it gets hit with
fsyncs, and then it's dumping them as fast as it can.  So you need some
more work on tuning the kernel parameters.

            regards, tom lane