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