X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E899F5688E for ; Fri, 18 Mar 2005 01:32:52 +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 49049-03 for ; Fri, 18 Mar 2005 01:32:47 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B81B55678E for ; Fri, 18 Mar 2005 01:32:46 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2I1Wm0G029317; Thu, 17 Mar 2005 20:32:49 -0500 (EST) To: Steve Crawford Cc: pgsql-admin@postgresql.org Subject: Re: Excessive growth of pg_attribute and other system tables In-reply-to: <200503171637.16975.scrawford@pinpointresearch.com> References: <200503171515.38214.scrawford@pinpointresearch.com> <28709.1111103484@sss.pgh.pa.us> <200503171637.16975.scrawford@pinpointresearch.com> Comments: In-reply-to Steve Crawford message dated "Thu, 17 Mar 2005 16:37:16 -0800" Date: Thu, 17 Mar 2005 20:32:48 -0500 Message-ID: <29316.1111109568@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/282 X-Sequence-Number: 16780 Steve Crawford writes: > On Thursday 17 March 2005 3:51 pm, Tom Lane wrote: >> Do you have the FSM settings set large enough to account for all >> the free space? > max_fsm_pages = 20000 > max_fsm_relations = 1000 That doesn't sound like nearly enough pages for a 2G database. 20000 * 8K = 160MB, so if more than 10% of the pages in your DB are dirty you are going to be leaking free space. You probably want max_fsm_pages up around 250,000. > I just poked through the cluster. I have 6 user databases plus > template0 and template1. Total tables in the cluster has reached > slightly over 1,000 user tables plus all the system tables in the 8 > databases. Does this indicate that I need to increase > max_fsm_relations to greater than the total number of tables in the > cluster? That would be a good plan too. > pg_autovacuum is from 7.4.6 release and is showing periodic vacuums of > the problem tables. I thought that bug was in some release prior to > 7.4.6. Does the bug allow it to show a vacuum taking place but not do > it? I don't recall the details of the changes; you could look at the CVS logs to see what got fixed when. But in any case I think we see the main problem: max_fsm_pages too small. regards, tom lane