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

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