Re: db growing out of proportion - Mailing list pgsql-bugs

I have a database with similar performance constraints. Our best
estimates put the turnover on our most active table at 350k tuples/day.
The hardware is a 4x1.4GHz Xeon w/ a RAID 1 disk setup, and the DB
floats around 500MB of disk space taken. Here is what we do to maintain
operations:

1) Cron job @ 4:00AM that runs a full vacuum analyze on the DB, and
reindex on the major tables. (Reindex is to maintain index files in SHM)
An alerting feature pages the administrator if the job does not complete
within a reasonable amount of time.

2) Every 15 minutes, a cron job runs a vacuum analyze on our five
largest tables. An alert is emailed to the administrator if a second
vacuum attempts to start before the previous completes.

3) Every week, we review the disk usage numbers from daily peaks. This
determines if we need to increase our shmmax & shared buffers.

Additionally, you may want to take a look at your query performance. Are
most of your queries doing sequential scans? In my system, the crucial
columns of the primary tables are int8 and float8 fields. I have those
indexed, and I get a serious performance boost by making sure all
SELECT/UPDATE/DELETE queries that use those columns in the WHERE have an
explicit ::int8 or ::float8 (Explain analyze is your friend). During
peak usage, there is an order of magnitude difference (usually 10 to
15x) between queries doing sequential scans on the table, and queries
doing index scans. Might be worth investigating if your queries are
taking 5 seconds when your DB is fresh. HTH.



Tomas Szepe wrote:
> Hello everybody,
>
> I'm facing a simple yet gravely problem with postgresql 7.3.2 on x86 Linux.
> My db is used to store IP accounting statistics for about 30 C's.  There are
> a couple truly trivial tables such as the one below:
>
> CREATE TABLE stats_min
> (
>     ip    inet        NOT NULL,
>     start    timestamp    NOT NULL default CURRENT_TIMESTAMP(0),
>     intlen    int4        NOT NULL default 60,
>     d_in    int8        NOT NULL,
>     d_out    int8        NOT NULL,
>
>     constraint "stats_min_pkey" PRIMARY KEY ("ip", "start")
> );
> CREATE INDEX stats_min_start ON stats_min (start);
>
> A typical transaction committed on these tables looks like this:
>
> BEGIN WORK
>     DELETE ...
>     UPDATE/INSERT ...
> COMMIT WORK
>
> Trouble is, as the rows in the tables get deleted/inserted/updated
> (the frequency being a couple thousand rows per minute), the database
> is growing out of proportion in size.  After about a week, I have
> to redump the db by hand so as to get query times back to sensible
> figures.  A transaction that takes ~50 seconds before the redump will
> then complete in under 5 seconds (the corresponding data/base/ dir having
> shrunk from ~2 GB to ~0.6GB).
>
> A nightly VACCUM ANALYZE is no use.
>
> A VACUUM FULL is no use.
>
> A VACUUM FULL followed by REINDEX is no use.
>
> It seems that only a full redump involving "pg_dump olddb | \
> psql newdb" is capable of restoring the system to its working
> glory.
>
> Please accept my apologies if I've overlooked a relevant piece of
> information in the docs.  I'm in an urgent need of getting this
> problem resolved.
>