Re: bgwriter, checkpoints, curious (seeing delays)

[Greg Smith <greg(at)2ndquadrant(dot)com>]

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(at)2ndQuadrant(dot)com   www.2ndQuadrant.us