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@gregsmith.com http://www.gregsmith.com Baltimore, MD