Обсуждение: Re: BUG #2784: Performance serious degrades over a period of a month

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.

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.