Bruno Wolff III <bruno@xxxxxxxx> 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@xxxxxxxxxxxx> 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.
