X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 97B469FA159 for ; Tue, 9 May 2006 20:52:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 74912-06 for ; Tue, 9 May 2006 20:51:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from eastrmmtao05.cox.net (eastrmmtao05.cox.net [68.230.240.34]) by postgresql.org (Postfix) with ESMTP id 5E0939F9DCD for ; Tue, 9 May 2006 20:51:49 -0300 (ADT) Received: from [192.168.1.100] (really [68.99.49.95]) by eastrmmtao05.cox.net (InterMail vM.6.01.06.01 201-2131-130-101-20060113) with ESMTP id <20060509235147.ZOKU26910.eastrmmtao05.cox.net@[192.168.1.100]>; Tue, 9 May 2006 19:51:47 -0400 Subject: Re: From: "Mark R. Dingee" Reply-To: mark.dingee@cox.net To: Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <17435.1147208476@sss.pgh.pa.us> References: <15254425.1147204995912.JavaMail.root@eastrmwml05.mgt.cox.net> <17435.1147208476@sss.pgh.pa.us> Content-Type: text/plain Date: Tue, 09 May 2006 19:51:46 -0400 Message-Id: <1147218706.6056.0.camel@elrond> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 (2.2.3-4.fc4) Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/91 X-Sequence-Number: 21623 Tom, Thanks for the advice. I'll track it over the next couple weeks and see what comes up. Mark On Tue, 2006-05-09 at 17:01 -0400, Tom Lane wrote: > writes: > > I had an odd situation occur this morning with PGSQL 7.4 run on Red Hat Enterprise 4 (update 3) and could really use some wisdom. > > > ... Single postmaster running. > > ... vacuum full is run every night as part of a cron job > > ... At start, data files consume about 28 GB > > ... This morning I dropped the database and reloaded from current backup > > ... New instance consumes about 6 GB > > > I can only assume that the database was not compacted, but I thought vacuum full performed that function along with tuple maintenance. Can anyone expound on the problem and suggest a solution other than dropping and reloading the database? > > The evidence is mostly gone now, but what I'd suggest is waiting a while > to see if it bloats again, and if so finding out exactly *where* the > bloat is. Make some notes now about the sizes of your tables and > indexes, and see what's getting larger. > > My guess offhand is that the problem is index bloat. VACUUM FULL not > only doesn't help much with that, it tends to make it worse. If the > database size is supposed to be fairly stable, you'd probably be better > off with a maintenance regime that doesn't use VACUUM FULL but just > plain VACUUM. Make sure your FSM settings are high enough. > > regards, tom lane