Re: UPDATES hang every 5 minutes

On 8/9/07, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> On Tue, 7 Aug 2007, mr19 wrote:
>
> > I have a process that updates ~ 1500 rows in a table once a second. Every 5
> > minutes (almost exactly) the update takes ~ 15 seconds (normally < 1).
>
> Lots of updates will trigger checkpoints and, if you have auto-vacuum
> turned on, regular vacuum activity--either of which could contribute to
> your long delays. A 15 second long pause sounds more like checkpoints to
> me. Try increasing checkpoint_warning in your postgresql.conf file to its
> maximum of 3600 and restart the server when you can tolerate a small
> service disruption; that will get you a note in the logs every time one
> happens so you can see if they line up with the slowdowns.

Wouldn't that be the other way around, set checkpoint_warning to 1 so
it triggers every time the checkpoint happens?

Note you don't have to stop / restart, just reload, and the default
checkpoint timeout is 5 minutes.

Would increasing the checkpoint_timeout and adjusting the bgwriter
settings help here?