X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 007B852A5A for ; Thu, 31 Mar 2005 22:16:32 +0100 (BST) 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 76521-08 for ; Thu, 31 Mar 2005 21:16:23 +0000 (GMT) Received: from outbound.mailhop.org (outbound.mailhop.org [63.208.196.171]) by svr1.postgresql.org (Postfix) with ESMTP id 3C4235294A for ; Thu, 31 Mar 2005 22:16:22 +0100 (BST) Received: from ool-4350c7ad.dyn.optonline.net ([67.80.199.173] helo=[192.168.0.66]) by outbound.mailhop.org with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.44) id 1DH71c-000CZY-5e; Thu, 31 Mar 2005 16:16:24 -0500 Message-ID: <424C6897.1050206@zeut.net> Date: Thu, 31 Mar 2005 16:16:07 -0500 From: "Matthew T. O'Connor" Organization: Terrie O'Connor Realtors User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Steve Crawford , Jan Wieck , pgsql-hackers@postgreSQL.org Subject: Re: [ADMIN] Excessive growth of pg_attribute and other system tables References: <200503171515.38214.scrawford@pinpointresearch.com> <423F17D7.7040904@zeut.net> <1219.1111434030@sss.pgh.pa.us> <200503310944.50647.scrawford@pinpointresearch.com> <7739.1112295848@sss.pgh.pa.us> In-Reply-To: <7739.1112295848@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Mail-Handler: MailHop Outbound by DynDNS.org X-Originating-IP: 67.80.199.173 X-Report-Abuse-To: abuse@dyndns.org (see http://www.mailhop.org/outbound/abuse.html for abuse reporting information) X-MHO-User: zeut 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/1067 X-Sequence-Number: 65869 Tom Lane wrote: >Steve Crawford writes: > > >>On Monday 21 March 2005 11:40 am, Tom Lane wrote: >> >> >>>However, given that there are 9334 tuples in 82282 pages, I'd say >>>that autovacuum has already failed Steve rather badly :-(. There >>>shouldn't be more than a couple hundred pages given that number of >>>rows. Perhaps the FSM settings are too small? >>> >>> > > > >>Results time. FSM settings were too small but the real problem seems >>to be that pg_autovacuum isn't getting the job done. >> >> > >The light just went on ... system catalog updates don't generate >statistics reports. Hence, autovacuum doesn't know any work is needed. > >Should we fix that, or change autovacuum to special-case the system >catalogs somehow, or ??? > > Really?!?!?!? Wow, if that is true, that is a big gaping hole in the autovacuum design. Is that true for all types of system catalog updates? The reason I ask is that the stats system is reporting at least some of activity on pg_attribute in this example. So why would it report some but not all? Is there any chance fixing the stats system to include system catalog updates would be simple enough to put into the 8.0.x branch? I don't know a another way for pg_autovacuum know if it's time for an vacuum. Hopefully the autovacuum in 8.1 will be a fairly different animal.