Skip site navigation (1) Skip section navigation (2)

Re: BUG #2784: Performance serious degrades over a period

From: Bill Moran <wmoran(at)collaborativefusion(dot)com>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: Michael Simms <michael(at)tuxgames(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: BUG #2784: Performance serious degrades over a period
Date: 2006-11-28 11:34:39
Message-ID: 20061128063439.0311f002.wmoran@collaborativefusion.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-performance
Bruno Wolff III <bruno(at)wolff(dot)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(at)tuxgames(dot)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.

In response to

pgsql-performance by date

Next:From: GopalDate: 2006-11-28 12:22:31
Subject: Re: Postgres scalability and performance on windows
Previous:From: Heikki LinnakangasDate: 2006-11-28 11:08:15
Subject: Re: BUG #2784: Performance serious degrades over a period of a month

pgsql-bugs by date

Next:From: Gurjeet SinghDate: 2006-11-28 12:27:43
Subject: BUG #2790: constructor for int2vector seems to be buggy
Previous:From: Heikki LinnakangasDate: 2006-11-28 11:08:15
Subject: Re: BUG #2784: Performance serious degrades over a period of a month

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group