Message-ID: <46656135.5050806@otg-nc.com>
Date: Tue, 05 Jun 2007 09:12:21 -0400
From: Chander Ganesan <chander@otg-nc.com>
User-Agent: Thunderbird 2.0.0.0 (Macintosh/20070326)
MIME-Version: 1.0
To: "Charles.Hou" <ivan.hou@msa.hinet.net>
CC: pgsql-admin@postgresql.org
Subject: Re: the right time to vacuum database?
References: <1181039589.643323.59660@r19g2000prf.googlegroups.com>
In-Reply-To: <1181039589.643323.59660@r19g2000prf.googlegroups.com>
Content-Type: text/plain; charset=Big5
Content-Transfer-Encoding: 7bit

Charles.Hou wrote:
> how can i know that it's the time to vacuumdb? i set the crontab to
> vacuumdb 3 times in one day. because my database size increase from
> 440MB to 460MB in 8 hours.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>   
What Version of PostgreSQL are you using? If it's recent ( > 8.1) you
might benefit from using AutoVacuum (AVD), which will take care of
vacuuming for you once table fragmentation (the ratio of dead tuples to
total tuples) becomes an issue. Otherwise, it's hard to tell based on
your metric above. You may have one small table that is updated a lot
(in which case a vacuum is a good idea on that table) or lots of tables
that are updated a little (in which its probably overkill). Your best
bet might be to turn on row level stats collection and look at the
statistics to see if one or more tables is becoming fragmented (BTW,
this is what AVD does). 440 to 460 doesn't seem to significant to me,
but it all depends on the size of your tables, update/delete profile, etc.

-- 
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com