pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Todd Nemanich <todd@twopunks.org>
To: pgsql-bugs@postgresql.org
Subject: Re: db growing out of proportion
Date: Fri, 30 May 2003 10:25:42 -0400
Message-ID: <3ED769E6.3050300@twopunks.org> (raw)
In-Reply-To: <20030529163239.GA11101@louise.pinerecords.com>
References: <20030529163239.GA11101@louise.pinerecords.com>

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.
> 




view thread (15+ messages)  latest in thread

Message-ID: <3ED769E6.3050300@twopunks.org>
Permalink:  ../3ED769E6.3050300@twopunks.org/
Also on:    postgresql.org/message-id/3ED769E6.3050300@twopunks.org

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: todd@twopunks.org, pgsql-bugs@postgresql.org
  Subject: Re: db growing out of proportion
  In-Reply-To: <3ED769E6.3050300@twopunks.org>
  References: <20030529163239.GA11101@louise.pinerecords.com> <3ED769E6.3050300@twopunks.org>

* 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