Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 6E26C9FA3B3 for ; Tue, 28 Nov 2006 00:25:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 72753-03 for ; Tue, 28 Nov 2006 00:25:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 626739FA0C4 for ; Tue, 28 Nov 2006 00:25:27 -0400 (AST) Received: (qmail 18659 invoked by uid 500); 28 Nov 2006 04:26:27 -0000 Date: Mon, 27 Nov 2006 22:26:27 -0600 From: Bruno Wolff III To: Michael Simms Cc: pgsql-bugs@postgresql.org, pgsql-performance@postgresql.org Subject: Re: BUG #2784: Performance serious degrades over a period of a month Message-ID: <20061128042627.GA17575@wolff.to> Mail-Followup-To: pgsql-performance@postgresql.org, Michael Simms , Bruno Wolff III References: <200611261635.kAQGZqKE025405@wwwmaster.postgresql.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200611261635.kAQGZqKE025405@wwwmaster.postgresql.org> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/220 X-Sequence-Number: 16355 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 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.