DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=jezPSUoemPnU8SHjwadb4CsrBrlAp6VBy9BOFEJVDrEC68gs+QJdDCEMkt/d7Dn/8P4XMbBeQxOzxltrJunztjR5UGi/mjJVcDD7IUW6OTNFwWZPJA04Wa3bACgl5D1/ugtqNBBCpKGBp2N0M2ZxrDDrGcOVmPjMJiHI855NT90=
Message-ID: <463FF82F.9040002@gmail.com>
Date: Mon, 07 May 2007 21:10:23 -0700
From: Yudhvir Singh Sidhu <ysidhu@gmail.com>
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: Jim Nasby <decibel@decibel.org>,  pgsql-performance@postgresql.org
Subject: Re: How to Find Cause of Long Vacuum Times - NOOB Question
References: <463D0BD5.3010404@gmail.com>
	<A47620C3-8814-4952-8DC5-532CFB04897E@decibel.org>
In-Reply-To: <A47620C3-8814-4952-8DC5-532CFB04897E@decibel.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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