Jim Nasby wrote:
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)
It ramps up and I have to run a db truncate to bring it back down. On
some machines it creeps up, on others it spikes. I have seen it climb
from 6 to 12 to 21 in 3 consequtive days. Well, what's one to do? I have
maintenance_work_mem set to 32768 - Is that enough? I vacuum daily.
I just turned vacuum verbose on on one of the systems and will find out
tomorrow what it shows me. I plan on playing with Max_fsm_ settings
tomorrow. And I'll keep you guys up to date.
Yudhvir
