pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed7.4 Checkpoint Question
4+ messages / 3 participants
[nested] [flat]
* 7.4 Checkpoint Question
@ 2007-11-29 15:10 Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 2 replies; 4+ messages in thread
From: Brad Nicholson @ 2007-11-29 15:10 UTC (permalink / raw)
To: pgsql-performance
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?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 7.4 Checkpoint Question
@ 2007-11-29 16:02 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Brad Nicholson <bnichols@ca.afilias.info>
1 sibling, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2007-11-29 16:02 UTC (permalink / raw)
To: pgsql-performance
On Thu, Nov 29, 2007 at 10:10:54AM -0500, Brad Nicholson wrote:
> 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.
> What gives?
pg_dump? Remember that it has special locks approximately equivalent
(actually eq? I forget) with SERIALIZABLE mode, which makes things rather
different.
A
--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 7.4 Checkpoint Question
@ 2007-11-29 16:14 Simon Riggs <simon@2ndquadrant.com>
parent: Brad Nicholson <bnichols@ca.afilias.info>
1 sibling, 1 reply; 4+ messages in thread
From: Simon Riggs @ 2007-11-29 16:14 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 7.4 Checkpoint Question
@ 2007-11-29 16:18 Brad Nicholson <bnichols@ca.afilias.info>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 4+ messages in thread
From: Brad Nicholson @ 2007-11-29 16:18 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-11-29 16:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-29 15:10 7.4 Checkpoint Question Brad Nicholson <bnichols@ca.afilias.info>
2007-11-29 16:02 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-11-29 16:14 ` Simon Riggs <simon@2ndquadrant.com>
2007-11-29 16:18 ` Brad Nicholson <bnichols@ca.afilias.info>
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