Re: How to Find Cause of Long Vacuum Times - NOOB Question

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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)