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

From: Jim Nasby <decibel(at)decibel(dot)org>
To: Yudhvir Singh Sidhu <ysidhu(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: How to Find Cause of Long Vacuum Times - NOOB Question
Date: 2007-05-09 17:14:26
Message-ID: F977A547-65B1-4041-A8CE-65CA06BDBB7C@decibel.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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)

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2007-05-09 17:40:06 Re: Nested loops overpriced
Previous Message Jim Nasby 2007-05-09 17:10:34 Re: Best OS for Postgres 8.2