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 AB62456397 for ; Fri, 18 Mar 2005 04:11:31 +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 90326-03 for ; Fri, 18 Mar 2005 04:11:22 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 3044952AA7 for ; Fri, 18 Mar 2005 04:11:22 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j2I4BLxL091533 for ; Fri, 18 Mar 2005 04:11:21 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j2I3nwEB086620 for pgsql-admin@postgresql.org; Fri, 18 Mar 2005 03:49:58 GMT (envelope-from news) From: Christopher Browne X-Newsgroups: pgsql.admin Subject: Re: Excessive growth of pg_attribute and other system tables Date: Thu, 17 Mar 2005 22:28:47 -0500 Organization: cbbrowne Computing Inc Lines: 35 Message-ID: References: <200503171515.38214.scrawford@pinpointresearch.com> <28709.1111103484@sss.pgh.pa.us> <200503171637.16975.scrawford@pinpointresearch.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4 (Jumbo Shrimp, linux) Cancel-Lock: sha1:3xxmZ9ZFkaonqmontyG4c88MLVg= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200503/283 X-Sequence-Number: 16781 After takin a swig o' Arrakan spice grog, scrawford@pinpointresearch.com (Steve Crawford) belched out: > On Thursday 17 March 2005 3:51 pm, Tom Lane wrote: >> Steve Crawford writes: >> > My autovacuum config is running and I do see regular periodic >> > vacuums of these pg_ tables but still they grow. >> >> Do you have the FSM settings set large enough to account for all >> the free space? > > max_fsm_pages = 20000 > max_fsm_relations = 1000 20000 is definitely way too low. It's not enough to track the dead pages in pg_attribute alone, which looks to have the better part of 80K dead pages. I'd increase that to about 200000, straight off. It seems curious that you have so many tuples getting killed off in this table; are you generating a lot of temp tables continually? By the way, you should be vacuuming pg_attribute _way_ more often, as it shouldn't have gotten as big if you did so... Doing a reindex and doing (once!) a VACUUM FULL should help bring the size down; vacuuming the table more often should keep size down... -- output = reverse("moc.liamg" "@" "enworbbc") http://cbbrowne.com/info/postgresql.html "To do is to be." -- Aristotle "To be is to do." -- Socrates "Do be do be do." -- Sinatra "Do be a do bee." -- Miss Sally of Romper Room fame. "Yabba dabba do." -- Fred Flintstone "DO...BEGIN..END" -- Niklaus Wirth