On Thu, 9 Aug 2007, Scott Marlowe wrote:
Wouldn't that be the other way around, set checkpoint_warning to 1 so it triggers every time the checkpoint happens?
The log message appears if the checkpoints happen more frequently than the value, so setting to 1 would only trigger a warning if you got a checkpoint more than once a second. Using the max of 3600 will spit out a warning every time there's a checkpoint as long as they happen more than once per hour, which means in any normal configurations you'll get every one of them logged.
Note you don't have to stop / restart, just reload, and the default checkpoint timeout is 5 minutes.
I suggested a couple of other changes as well which is why I suggested touching things during a maintenance window, but as you and Tom have pointed out you can adjust checkpoint_warning without taking the server down. The fact that the default timeout matches exactly when he's seeing the slowdowns, once every five minutes, is almost certainly the smoking gun that this is a checkpoint issue, but Marc should confirm that before just assuming it's the case.
Would increasing the checkpoint_timeout and adjusting the bgwriter settings help here?
That's why I suggested he give some more configuration info before anyone could say how to solve the problem. For all we know, moving checkpoint_timeout upward will just shift the period between pauses to one based on checkpoint_segments instead, which might not be a big difference. Also, increasing the timeout has the potential to make the spikes even longer when they finally do happen.
Engineering the pauses out using the background writer in this sort of situation (very frequent updates) can be very hard to do. There's new code coming in 8.3 that addresses this issue head-on, it can be tricky to accomplish in the current production releases.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
