Date: Tue, 28 Nov 2006 06:34:39 -0500
From: Bill Moran <wmoran@collaborativefusion.com>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Michael Simms <michael@tuxgames.com>,
  pgsql-performance@postgresql.org
Subject: Re: BUG #2784: Performance serious degrades over a period
Message-Id: <20061128063439.0311f002.wmoran@collaborativefusion.com>
In-Reply-To: <20061128042627.GA17575@wolff.to>
References: <200611261635.kAQGZqKE025405@wwwmaster.postgresql.org>
	<20061128042627.GA17575@wolff.to>
Organization: Collaborative Fusion Inc.
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Bruno Wolff III <bruno@wolff.to> wrote:
>
> This really should have been asked on pgsql-performance and would probably
> get a better response there..
> 
> On Sun, Nov 26, 2006 at 16:35:52 +0000,
>   Michael Simms <michael@tuxgames.com> wrote:
> > PostgreSQL version: 8.1.4
> > Operating system:   Linux kernel 2.6.12
> > Description:        Performance serious degrades over a period of a month
> > Details: 
> > 
> > OK, we have a database that runs perfectly well after a dump and restore,
> > but over a period of a month or two, it just degrades to the point of
> > uselessness.
> > vacuumdb -a is run every 24 hours. We have also run for months at a time
> > using -a -z but the effect doesnt change.
> > 
> 
> This sounds like you either need to increase your FSM setting or vacuum
> more often. I think vacuumdb -v will give you enough information to tell
> if FSM is too low at the frequency you are vacuuming.
> 
> > The database is for a counter, not the most critical part of the system, but
> > a part of the system nonetheless. Other tables we have also degrade over
> > time, but the counter is the most pronounced. There seems to be no common
> > feature of the tables that degrade. All I know is that a series of queries
> > that are run on the database every 24 hours, after a dump/restore takes 2
> > hours. Now, 2 months after, it is taking over 12. We are seriously
> > considering switching to mysql to avoid this issue. 
> 
> You probably will want to vacuum the counter table more often than the other
> tables in the database. Depending on how often the counter(s) are being
> updated and how many separate counters are in the table you might want to
> vacuum that table as often as once a minute.
> 
> Depending on your requirements you might also want to consider using a sequence
> instead of a table row for the counter.

Just to throw it in to the mix: you might also be in a usage pattern that would
benefit from a scheduled reindex every so often.