Re: UPDATES hang every 5 minutes

Thanks for all the replies. As of right now I think I have it narrowed down to checkpoints based on the iostat activity I see when the hangs occur as well as the checkpoint_timeout defaulting to 5 min.

I've upped checkpoint_warnings to 3600 to confirm but also made a few other changes. I moved the pg_xlog dir to another disk (unfortunately it is the root volume) as well as made changes to the bgwriter settings as shown below (taken from a post in the pgsql-performance list)

bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers scanned/round
bgwriter_lru_maxpages = 200 # 0-1000 buffers max written/round
bgwriter_all_percent = 10.0 # 0-100% of all buffers scanned/round
bgwriter_all_maxpages = 600 # 0-1000 buffers max written/round

I won't know until tomorrow if this solves anything but will post back to this thread for others who may experience the same problem in the future.

Thanks again,
Marc

----- Original Message ----
From: Greg Smith <gsmith(at)gregsmith(dot)com>
Cc: mr19 <marc_rossi(at)yahoo(dot)com>; pgsql-general(at)postgresql(dot)org
Sent: Thursday, August 9, 2007 11:10:42 PM
Subject: Re: [GENERAL] UPDATES hang every 5 minutes

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD