On May 7, 2007, at 11:10 PM, Yudhvir Singh Sidhu wrote:
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).
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?
Depends on how many dead rows there are to be vacuumed. If there's a
lot, you could certainly be exceeding maintenance_work_mem. If you
look closely at the output of VACUUM VERBOSE you'll see the indexes
for a particular table being scanned more than once if all the dead
rows can't fit into maintenance_work_mem.
I vacuum daily.
If you've got high update rates, that very likely might not be often
enough.
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.
The tail end of vacuumdb -av will tell you exactly how much room is
needed in the FSM.
--
Jim Nasby jim@xxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
