X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C2BE4D1C9F3 for ; Fri, 23 Apr 2004 15:16:08 -0300 (ADT) 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 22035-03 for ; Fri, 23 Apr 2004 15:16:07 -0300 (ADT) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id B1E40D1C9B7 for ; Fri, 23 Apr 2004 15:16:05 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 4929755; Fri, 23 Apr 2004 11:17:30 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Chris Hoover" , Subject: Re: Help with performance problems Date: Fri, 23 Apr 2004 11:15:23 -0700 User-Agent: KMail/1.4.3 References: <200404230931.17850.revoohc@sermonaudio.com> <4089561B.8010001@speedfc.com> <200404231353.17234.revoohc@sermonaudio.com> In-Reply-To: <200404231353.17234.revoohc@sermonaudio.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200404231115.23198.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/411 X-Sequence-Number: 6711 Chris, > Sorry for the confusion here. I can't run any sort of vacuum durin the day > due to performance hits. However, I have run vacuums at night. Several > nights a week I run a vacuumdb -f -z on all of the clusters. I can take > serveral hours to complete, but it does complete. Well, here's your first problem: since your FSM pages is low, and you're only vacuuming once a day, you've got to have some serious table and index bloat. SO you're going to need to do VACUUM FULL on all of your databases, and then REINDEX on all of your indexes. After that, raise your max_fsm_pages to something useful, like 1,000,000. Of course, data on your real rate of updates would help more. If you're getting severe disk choke when you vacuum, you probably are I/O bound. You may want to try something which allows you to vacuum one table at a time, either pg_autovacuum or a custom script. -- Josh Berkus Aglio Database Solutions San Francisco