X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A32FF3A5A7C for ; Fri, 3 Dec 2004 05:59:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60977-03 for ; Fri, 3 Dec 2004 05:59:51 +0000 (GMT) Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) by svr1.postgresql.org (Postfix) with ESMTP id B7C613A59F7 for ; Fri, 3 Dec 2004 05:59:51 +0000 (GMT) Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6738281; Thu, 02 Dec 2004 22:01:24 -0800 From: Josh Berkus Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org Subject: Re: Poor Performance on a table Date: Thu, 2 Dec 2004 21:57:00 -0800 User-Agent: KMail/1.6.2 Cc: Pallav Kalva , Tom Lane References: <41AF68F2.1050206@deg.cc> <29227.1102016219@sss.pgh.pa.us> <41AF72EB.1070900@deg.cc> In-Reply-To: <41AF72EB.1070900@deg.cc> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200412022157.00254.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/79 X-Sequence-Number: 9454 Pallav, > Yes, you are right this table is heavily updated, the whole database > size is of 1.5 gigs, right now i have default fsm settings how much > should i increase max_fsm_pages and max_fsm_relations to ? 1) fix the table (see below) 2) run the system for another day 3) run VACUUM FULL ANALYZE VERBOSE 4) if you're running 7.4 or better, at the end you'll see a total of FSM pages needed. If you're running something earlier, you'll need to get out a calculator and do the math yourself. Of course, if you're getting heavy update/delete activity, vacuuming more often might be wise. Post the output of the above command if you have questions. > I am hesitant to do vacuum full on the table because it is one of the > crucial table in our application and we cant afford to have exclusive > lock on this table for long time. we can afford not to have writes and > updates but we need atleast reads on this table . You're going to have to do at least one or the table will just keep getting worse. Schedule it for 3am. Once you've set FSM correctly, and are vacuuming with the right frequency, the need to run VACUUM FULL will go away. Oh, and it's likely that any indexes on the table need to be REINDEXed. -- Josh Berkus Aglio Database Solutions San Francisco