X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8DFA0D1B440 for ; Tue, 25 Nov 2003 16:54:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 95033-06 for ; Tue, 25 Nov 2003 12:53:33 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id CAD60D1B438 for ; Tue, 25 Nov 2003 12:53:33 -0400 (AST) Received: (qmail 25386 invoked by uid 500); 25 Nov 2003 16:52:32 -0000 Date: Tue, 25 Nov 2003 10:52:32 -0600 From: Bruno Wolff III To: MK Spam Cc: pgsql-performance@postgresql.org Subject: Re: Where to start for performance problem? Message-ID: <20031125165232.GA25043@wolff.to> Mail-Followup-To: MK Spam , pgsql-performance@postgresql.org References: <2b1f01c3b2d6$c403ba70$0201a8c0@game> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <2b1f01c3b2d6$c403ba70$0201a8c0@game> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/241 X-Sequence-Number: 4801 On Mon, Nov 24, 2003 at 16:03:17 -0600, MK Spam wrote: > > The archives of this list provides many ideas for improving performance, but the problem we are having is gradually degrading performance ending in postgres shutting down. So it's not a matter of optimizing a complex query to take 5 seconds instead of 60 seconds. From what I can tell we are using the VACUUM command on a schedule but it doesn't seem to prevent the database from becoming "congested" as we refer to it. :] Anyway, the only way I know to "fix" the problem is to export (pg_dump) the db, drop the database, recreate the database and import the dump. This seems to return performance back to normal but obviously isn't a very good "solution". The slowdown and subsequent crash can take as little as 1 week for databases with a lot of data or go as long as a few weeks to a month for smaller data sets. A couple of things you might look for are index bloat and having FSM set too small for your plain vacuums. Upgrading to 7.4 may help with index bloat if that is your problem.