Subject: Re: 7.4 Checkpoint Question
From: Brad Nicholson <bnichols@ca.afilias.info>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1196352861.4246.1302.camel@ebony.site>
References: <1196349054.5651.19.camel@bnicholson-desktop>
	<1196352861.4246.1302.camel@ebony.site>
Content-Type: text/plain
Date: Thu, 29 Nov 2007 11:18:39 -0500
Message-Id: <1196353119.5651.22.camel@bnicholson-desktop>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


On Thu, 2007-11-29 at 16:14 +0000, Simon Riggs wrote:
> On Thu, 2007-11-29 at 10:10 -0500, Brad Nicholson wrote:
> > I have a legacy system still on 7.4 (I know, I know...the upgrade is
> > coming soon).
> > 
> > I have a fairly big spike happening once a day, every day, at the same
> > time.  It happens during a checkpoint, no surprise there.  I know the
> > solution to the problem (upgrade to a modern version), but what I'm
> > looking for as an explanation as to why one particular checkpoint would
> > be so bad on a low volume system, so I can appease certain management
> > concerns. 
> > 
> > This is a _really _low volume system, less than 500 writes/hour.  Normal
> > operation sees checkpoint related spikes of around 200-300 milliseconds.
> > We always checkpoint at the checkpoint timeout (every 5 minutes).
> > During this one checkpoint, I'm seeing transactions running 2-3 seconds.
> > During this time, writes are < 5/minute.
> > 
> > Relevant settings:
> > shared_buffers = 10000
> > 
> > checkpoint_segments = 30
> > checkpoint_timeout = 300
> > 
> > What gives?
> 
> If the timing is regular, its most likely a human-initiated action
> rather then a behavioural characteristic.
> 
> VACUUM runs in background at that time, updates loads of blocks which
> need to be written out at checkpoint time. That slows queries down at
> that time but not others.

Bingo.  Big vacuum daily vacuum completes shortly before this chckpoint.

Thanks. 

-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.