From: Bill Moran <wmoran@collaborativefusion.com>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Michael Simms <michael@tuxgames.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: BUG #2784: Performance serious degrades over a period
Date: Tue, 28 Nov 2006 06:34:39 -0500
Message-ID: <20061128063439.0311f002.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <20061128042627.GA17575@wolff.to>
References: <200611261635.kAQGZqKE025405@wwwmaster.postgresql.org>
<20061128042627.GA17575@wolff.to>
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.
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-performance@postgresql.org
Cc: wmoran@collaborativefusion.com, bruno@wolff.to, michael@tuxgames.com
Subject: Re: BUG #2784: Performance serious degrades over a period
In-Reply-To: <20061128063439.0311f002.wmoran@collaborativefusion.com>
* 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