>> Wouldn't increasing the length between checkpoints result in the
>> checkpoint process taking even longer to complete?
>
> You don't really care how long it takes. What you want is for it not to
> be chewing a bigger fraction of your I/O bandwidth than you can spare.
> Hence, you want it to take longer. Trying to shorten it is just going
> to make the spike worse.
>
> regards, tom lane
>
I bumped the segments up to 15, and the timeout up to 10 minutes, and
changed the completion target to .7.
What I observe now is that I get a short (1-2 second) period where I
get slow queries - I'm running about 30 queries in parallel at any
given time - it appears that all 30 queries get paused for a couple of
seconds at the moment that a checkpoint begins. However, after the
initial slowdown, I don't get any additional slow queries logged while
the checkpoint process runs.
My takeaway is that starting the checkpoint process is really
expensive - so I don't want to start it very frequently. And the only
downside to longer intervals between checkpoints is a longer recovery
time if the system crashes?
Thanks,
Dan