From: "Charles.Hou" <ivan.hou@msa.hinet.net>
Subject: Re: Auto Vacuum question?
Date: Mon, 04 Jun 2007 17:07:16 -0700
Organization: http://groups.google.com
Lines: 50
Message-ID: <1181002036.120310.83340@r19g2000prf.googlegroups.com>
References: <1180966368.172224.250500@x35g2000prf.googlegroups.com>
	<20070604144906.GH4779@alvh.no-ip.org>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
In-Reply-To: <20070604144906.GH4779@alvh.no-ip.org>
User-Agent: G2/1.0
Complaints-To: groups-abuse@google.com
Injection-Info: r19g2000prf.googlegroups.com; posting-host=210.202.3.132;
	posting-account=Mznd-A0AAABITZ14OcULoTm5bpaBaOsm
To: pgsql-admin@postgresql.org

On 6 4 ,   10 49 , alvhe...@commandprompt.com (Alvaro Herrera) wrote:
> Charles.Hou wrote:
> > i set the auto vacuum option to enable. but my database size(hard-
> > disk
> > space) still increased from 420MB to 440MB in 8 hours. most of the
> > operations in this database are the "Select" query command, just few
> > of "Update or Insert".
> > why it can be increased so strongly?
> > after i executed the command " vaccumdb -f -z testdb", but the size
> > just decreased 1 or 2MB...
> > what's the problem?
>
> It is normal that the tables grow a bit to a stationary state of dead
> space (generated by UPDATE and DELETE).  However it is not normal if it
> loses track of that dead space.  One thing you can and should do is
> adjust the autovacuum settings (for example lowering the vacuum scale
> factor and threshold values).  Another is to correctly set the FSM
> values.
>
> Have you done both?
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

it's my postgresql.conf

autovacuum = on                         # enable autovacuum
subprocess?
autovacuum_naptime = 20                 # time between autovacuum
runs, in secs
autovacuum_vacuum_threshold = 1         # min # of tuple updates
before
autovacuum_analyze_threshold = 1        # min # of tuple updates
before
autovacuum_vacuum_scale_factor = 1      # fraction of rel size before
autovacuum_analyze_scale_factor = 1     # fraction of rel size before

#autovacuum_vacuum_cost_delay = -1      # default vacuum cost delay
for
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit
for