Bastian Voigt <post@bastian-voigt.de> writes:
> Now my big big problem is that the database gets really really slow
> during these 20 minutes and after the vacuum process is running for a
> short time, many transactions show state "UPDATE waiting" in the process
> list. In my Java application server I sometimes get tons of deadlock
> Exceptions (waiting on ShareLock blahblah). The web frontend gets nearly
> unusable, logging in takes more than 60 seconds, etc. etc.
Hmm. That's a bit weird --- what are they waiting on exactly? Look in
pg_locks to see what the situation is. A vacuum per se ought not be
blocking any updates.
Aside from the recommendation to make the vacuums happen more frequently
instead of less so, you should experiment with vacuum_cost_delay and
related parameters. The idea is to reduce vacuum's I/O load so that it
doesn't hurt foreground response time. This means any individual vacuum
will take longer, but you won't need to care.
regards, tom lane
