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. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
