Message-ID: <4B879335.3090401@2ndquadrant.com>
Date: Fri, 26 Feb 2010 04:24:05 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Tory M Blue <tmblue@gmail.com>
CC: =?ISO-8859-1?Q?Devrim_G=DCND=DCZ?= <devrim@gunduz.org>,
	pgsql-performance@postgresql.org
Subject: Re: bgwriter, checkpoints, curious (seeing delays)
References: <8a547c841002252212t382cfeb4pde3e668a9d3f2f10@mail.gmail.com>	
	<1267166541.2094.52.camel@hp-laptop2.gunduz.org>	
	<8a547c841002252301g49d7f183ue0909929de09d20@mail.gmail.com>
	<8a547c841002252304v9da8589w719e4a3aa0e6d8e0@mail.gmail.com>
In-Reply-To: <8a547c841002252304v9da8589w719e4a3aa0e6d8e0@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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