From: Tatsuo Ishii <t-ishii@sra.co.jp>
To: marcos-p-garcia@ptinovacao.pt
Cc: sszabo@megazone23.bigpanda.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Slow SELECT -> Growing Database
Date: Fri, 28 Jun 2002 10:46:48 +0900 (JST)
Message-ID: <20020628.104648.88473805.t-ishii@sra.co.jp> (raw)
In-Reply-To: <1025191444.5705.80.camel@sargao>
References: <20020624143954.K59728-100000@megazone23.bigpanda.com>
<1025191444.5705.80.camel@sargao>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: t-ishii@sra.co.jp, marcos-p-garcia@ptinovacao.pt, sszabo@megazone23.bigpanda.com
Subject: Re: Slow SELECT -> Growing Database
In-Reply-To: <20020628.104648.88473805.t-ishii@sra.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox