Re: Checkpoint Tuning Question

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
On Wed, 8 Jul 2009, Dan Armbrust wrote:
>> 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.

> If what you're getting nailed with is the pause during the initial
> checkpoint processing, the only real option you have on the database side
> is to lower shared_buffers.

He's only got 100MB of shared buffers, which doesn't seem like much
considering it's apparently a fairly beefy system. I definitely
don't see how one CPU spinning over the buffer headers in BufferSync
is going to create the sort of hiccup he's describing.

Dan, are you sure that this hiccup is happening at the *start* of a
checkpoint? Do you have log_checkpoints turned on, and if so what
does it show?

regards, tom lane