DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=CZ8Dxwo5yFyyYmkZIhqf0Lq3cKNTUc2qBaWZKuabFQ4RUe0RZ3m7wEIoJ2+IJih8v4P6pPUCNbSG078Z+APODd1VqB2Ad2HcsELhN7CV2qJ50QiNKb7PZlqS/5C2PS8nJpS/EFdYOeJfCwUCdFB4qVbc5cE9wSGK3+eNfUtTbvU=
Message-ID: <dcc563d10708211501p559b73d3q123a35bdbce1b68d@mail.gmail.com>
Date: Tue, 21 Aug 2007 17:01:23 -0500
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Sachchida Ojha" <sojha@secure-elements.com>
Subject: Re: Autovacuum is running forever
Cc: "Michael Glaesemann" <grzm@seespotcode.net>,
	pgsql-performance@postgresql.org
In-Reply-To: <E099DB9EF394534CBA0F62ED03CF3BBF05FDBF1F@EXVS01.IceMAIL.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <C927A262-C7B0-4003-ABAE-570256F76B16@seespotcode.net>
	<E099DB9EF394534CBA0F62ED03CF3BBF05FDBF1F@EXVS01.IceMAIL.com>

On 8/21/07, Sachchida Ojha <sojha@secure-elements.com> wrote:


> vacuum_cost_delay                               = 200
> vacuum_cost_page_hit                    = 1
> vacuum_cost_page_miss                   = 10
> vacuum_cost_page_dirty                  = 20
> vacuum_cost_limit                               = 200
>
> autovacuum                                              = on
> autovacuum_naptime                              = 3600
>
> maintenance_work_mem            = 256MB                 # min 1024, size

That's a REALLY long naptime.  Better to let autovacuum decide if you
need vacuum more often, and just increase the vacuum_cost_delay and
decrease  vacuum_cost_limit so that vacuum doesn't slam your I/O.

Maintenance work mem on the other hand is plenty big.  and your fsm
settings seem large enough to handle your freed space.

But making vacuum wait so long between runs may be slowly bloating
your data store, and then vacuum becomes more and more expensive
because it takes longer and longer to run.