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.
You may want to crank up log_min_messages to see what's going on as well, at least to INFO as a start. If you can tolerate the verbosity for a bit while testing, you'll probably find out more than ever wanted to know about what internals are happening during your slowdown point if you increase that to DEBUG2, then set log_min_duration_statement to 2 seconds so it logs all the long updates. Probably want to put "%m" somewhere in your log_line_prefix as well so you can see timestamps on all that data.
To get more specific advice, report if you see anything interesting in your log files and send some details about the PostgreSQL version you're using and the settings in the postgresql.conf file that you've changed from their defaults.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
