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 1E8923A5781 for ; Thu, 2 Dec 2004 19:54:26 +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 79132-08 for ; Thu, 2 Dec 2004 19:54:22 +0000 (GMT) Received: from mail.deg.cc (mail.deg.cc [64.139.134.201]) by svr1.postgresql.org (Postfix) with ESMTP id C84663A5452 for ; Thu, 2 Dec 2004 19:54:23 +0000 (GMT) Received: from deg.cc (kyle.deg.cc [198.70.16.205]) by mail.deg.cc (Postfix) with ESMTP id 8D3FC56562; Thu, 2 Dec 2004 14:54:16 -0500 (EST) Message-ID: <41AF72EB.1070900@deg.cc> Date: Thu, 02 Dec 2004 14:54:19 -0500 From: Pallav Kalva User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.2) Gecko/20040308 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Poor Performance on a table References: <41AF68F2.1050206@deg.cc> <29227.1102016219@sss.pgh.pa.us> In-Reply-To: <29227.1102016219@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200412/72 X-Sequence-Number: 9447 Tom Lane wrote: >Pallav Kalva writes: > > >> I have a table in my production database which has 500k rows and >>from the pg_class it shows the number of "relpages" of >>around 750K for this table, the same table copied to a test database >>shows "relpages" as 35k. I run vacuumdb on the whole >>database (not on the table individually but the whole database) daily. >> >> > >You're obviously suffering serious table bloat :-(. Depending on how >heavy the update traffic on that table is, it might be that once-a-day >vacuum is simply not often enough. Another likely problem is that you >need to increase the FSM settings (how big is your whole database?) > 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 ? > > > >> Is there any way to fix this problem ? >> >> > >VACUUM FULL will fix the immediate problem. You might well find CLUSTER >to be a faster alternative, though. > > 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 . How does CLUSTER benefit me ? excuse me, i am new to this feature. > regards, tom lane > > >