Re: bgwriter, checkpoints, curious (seeing delays)

Tory M Blue wrote:
> 2010-02-25 22:10:41 PST    LOG:  checkpoint complete: wrote 44503
> buffers (23.2%); 0 transaction log file(s) added, 0 removed, 20
> recycled; write=148.539 s, sync=0.000 s, total=148.540 s
>
This one is typical for your list so I'll only comment on it.  This is
writing out 350MB spread over 148 seconds, which means your background
checkpoint I/O is about a 2.4MB/s stream.  That's a moderate amount that
could be tough for some systems, but note that your "sync" time is
nothing.  Generally, when someone sees a long pause that's caused by a
checkpoint, the sync number is really high.  Your disks seem to be
keeping up with the checkpoint overhead moderately well.  (Mind you, the
zero sync time is because you have 'fsync = off ', which will eventually
result in your database being corrupted after a badly timed outage one
day; you really don't want to do that)

My guess is your connections are doing some sort of DNS operation that
periodically stalls waiting for a 5-second timeout.  There's nothing in
your checkpoint data suggesting it's a likely cause of a delay that
long--and it would be a lot more random if that were the case, too.  Bad
checkpoint spikes will be seconds sometimes, no time at all others; a
heavy grouping at 5 seconds doesn't match the pattern they have at all.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us