Mailbox-Line: From sszabo@megazone23.bigpanda.com  Thu Jun 27 14:13:41 2002
Date: Thu, 27 Jun 2002 10:11:00 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Marcos Garcia <marcos-p-garcia@ptinovacao.pt>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Slow SELECT -> Growing Database
In-Reply-To: <1025191444.5705.80.camel@sargao>
Message-ID: <20020627095011.W7587-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On 27 Jun 2002, Marcos Garcia wrote:

> On Mon, 2002-06-24 at 22:43, Stephan Szabo wrote:
> > On 24 Jun 2002, Marcos Garcia wrote:
> >
> > Hmm, that should only happen if you're doing alot of updates or deletes
> > I would guess (updates would cause it to slow down as well since it's
> > similar to a delete and insert under MVCC).  And frequent normal vacuums
> > should do that as well unless the table has high turnover.
> >
> > Well, if you haven't yet, you might try upping the sort_mem and
> > shared_buffers amounts, although I think the former would only
> > potentially cut down the difference between 32s and 59s and the
> > latter would probably only help on a later use of the call if the
> > buffer is big enough to hold a significant portion of the pages.
> >
>
>
> 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????

Well, I'd suggest starting with doing some reindex (or drop/create)
indexes on big indexes (their space doesn't get reclaimed by vacuum
currently).  Another possibility is that you've got more pages having
open space than the free space map has space for so you might want
to increase max_fsm_pages and see if that helps.  And, if you're
effectively updating the table entirely 4 times in the day, you might want
to run a vacuum more often (you probably don't need the analyze)