Mailbox-Line: From t-ishii@sra.co.jp  Thu Jun 27 21:46:59 2002
Date: Fri, 28 Jun 2002 10:46:48 +0900 (JST)
Message-Id: <20020628.104648.88473805.t-ishii@sra.co.jp>
To: marcos-p-garcia@ptinovacao.pt
Cc: sszabo@megazone23.bigpanda.com, pgsql-sql@postgresql.org
Subject: Re: Slow SELECT -> Growing Database
From: Tatsuo Ishii <t-ishii@sra.co.jp>
In-Reply-To: <1025191444.5705.80.camel@sargao>
References: <20020624143954.K59728-100000@megazone23.bigpanda.com>
	<1025191444.5705.80.camel@sargao>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> The problem isn't in the select.
> 
> I realize that my database is growing and growing.
> 
> I've two tables that have, lets say, 120.000 records each, and:
> - delete about 30.000 records a day from each table
> - insert about 30.000 records a day on each table
> - update each record at least 4 four times
>  
> I've two other ones, that were mentioned in my previous emails, that
> have 12.000 records each, and:
> - insert 48 records a day in each table
> - =~ 120.000 updates in the last inserted records.
> 
> Track the problem:
> 
> # df -h /var/lib/pgsql 	-> 7.8 GB (I create this database 4 month's ago)
> 
> # pg_dump dbnane > dbname.dump
> 
> # dropdb dbname
> 
> # createdb dbname
> 
> # psql dbaname < dbname.dump
> 
> # df -h /var/lib/pgsql 	-> 140 M
> 
> I don't understand why the database is growing????
> And is still growing.
> I make a vacuum -z -d dbname everyday (I checked if it really runs).

You have never mentioned PostgreSQL verison you are using, so...

(1) if that's 7.2.x, you need to slightly increase FSM parameters in
    postgresql.conf. As you have ~8GB database, probably you need to
    set max_fsm_pages to: 8*1024*1024*1024/8192 = 1048576. It will
    need about 7MB more shmem, but it would not be too much for modern
    PC.

(2) if that's 7.1.x, or earlier, you need to vacuum and reindex more
    frequently.
--
Tatsuo Ishii