From: Simon Riggs <simon@2ndquadrant.com>
To: Brad Nicholson <bnichols@ca.afilias.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: 7.4 Checkpoint Question
Date: Thu, 29 Nov 2007 16:14:21 +0000
Message-ID: <1196352861.4246.1302.camel@ebony.site> (raw)
In-Reply-To: <1196349054.5651.19.camel@bnicholson-desktop>
References: <1196349054.5651.19.camel@bnicholson-desktop>
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.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, bnichols@ca.afilias.info
Subject: Re: 7.4 Checkpoint Question
In-Reply-To: <1196352861.4246.1302.camel@ebony.site>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox