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

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@nasby.net
> 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