From: Bruno Wolff III <bruno@wolff.to>
To: Michael Simms <michael@tuxgames.com>
Cc: pgsql-bugs@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: BUG #2784: Performance serious degrades over a period of a month
Date: Mon, 27 Nov 2006 22:26:27 -0600
Message-ID: <20061128042627.GA17575@wolff.to> (raw)
In-Reply-To: <200611261635.kAQGZqKE025405@wwwmaster.postgresql.org>
References: <200611261635.kAQGZqKE025405@wwwmaster.postgresql.org>
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.
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: bruno@wolff.to, michael@tuxgames.com, pgsql-bugs@postgresql.org
Subject: Re: BUG #2784: Performance serious degrades over a period of a month
In-Reply-To: <20061128042627.GA17575@wolff.to>
* 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