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.
