Date: Wed, 24 Jan 2007 21:39:17 -0600
From: "Jim C. Nasby" <jim@nasby.net>
To: Galy Lee <lee.galy@oss.ntt.co.jp>
Cc: pgsql-performance@postgresql.org
Subject: Re: how to plan for vacuum?
Message-ID: <20070125033917.GD64372@nasby.net>
References: <45B6F0A8.1050705@oss.ntt.co.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <45B6F0A8.1050705@oss.ntt.co.jp>
User-Agent: Mutt/1.5.13 (2006-08-11)

On Wed, Jan 24, 2007 at 02:37:44PM +0900, Galy Lee wrote:
> 1.  How do we know if autovacuum is enough for my application, or should
>     I setup a vacuum manually from cron for my application?

Generally I trust autovac unless there's some tables where it's critical
that they be vacuumed frequently, such as a queue table or a web session
table.

> 2. How to set the GUC parameters for autovacuum?
> There are two sets of parameters for autovacuum:
>     - vacuum threshold and scale factor (500/0.2)
> ?$B!!  - analyze threshold and scale factor(250/0.1)
> Is there any guideline to set these parameters?  When does it need to
> change the default values?

I find those are generally pretty good starting points; just bear in
mind that it means 20% dead space.

> 3. How to tune cost-based delay vacuum?
> I had searched in performance list; it seems that most of the practices
> are based on experience / trial-and-error approach to meet the
> requirement of disk utilization or CPU utilization. Is there any other
> guild line to set them?

Unless you have a means for the database to monitor IO usage on it's
own, I don't know that we have a choice...

I'll generally start with a cost delay of 20ms and adjust based on IO
utilization.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)