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@xxxxxxxxxxxxx> Cc: mr19 <marc_rossi@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx Sent: Thursday, August 9, 2007 11:10:42 PM Subject: Re: 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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
