Skip site navigation (1) Skip section navigation (2)

Re: how to plan for vacuum?

From: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
To: Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: how to plan for vacuum?
Date: 2007-01-25 03:39:17
Message-ID: 20070125033917.GD64372@nasby.net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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(at)nasby(dot)net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2007-01-25 03:52:02
Subject: Re: how to plan for vacuum?
Previous:From: Shoaib MirDate: 2007-01-24 10:19:22
Subject: Re: slow result

pgsql-hackers by date

Next:From: Jeremy DrakeDate: 2007-01-25 03:40:10
Subject: Re: [pgsql-patches] unprivileged contrib and pl install
Previous:From: Jeremy DrakeDate: 2007-01-25 03:26:09
Subject: Re: [pgsql-patches] unprivileged contrib and pl install

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group