On May 5, 2007, at 5:57 PM, Yudhvir Singh Sidhu wrote:
Problem: Vacuum times jump up from 45 minutes, or 1:30 minutes to 6 + hours overnight, once every 1 to 3 months. Solutions tried: db truncate - brings vacuum times down. Reindexing brings vacuum times down.
Does it jump up to 6+ hours just once and then come back down? Or once at 6+ hours does it stay there?
Getting that kind of change in vacuum time sounds a lot like you suddenly didn't have enough maintenance_work_mem to remember all the dead tuples in one pass; increasing that setting might bring things back in line (you can increase it on a per-session basis, too).
Also, have you considered vacuuming during the day, perhaps via autovacuum? If you can vacuum more often you'll probably get less bloat. You'll probably want to experiment with the vacuum_cost_delay settings to reduce the impact of vacuuming during the day (try setting vacuum_cost_delay to 20 as a starting point).
-- Jim Nasby jim@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
