X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8DE8E9DCD06 for ; Thu, 23 Feb 2006 19:57:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96353-07 for ; Thu, 23 Feb 2006 19:58:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id AF7119DCD00 for ; Thu, 23 Feb 2006 19:57:56 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id CEC7356428; Thu, 23 Feb 2006 17:58:29 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Thu, 23 Feb 2006 17:58:29 -0600 Date: Thu, 23 Feb 2006 17:58:29 -0600 From: "Jim C. Nasby" To: Robin Iddon Cc: pgsql-admin@postgresql.org Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table? Message-ID: <20060223235829.GP2068@pervasive.com> References: <43FD7312.5080701@edesix.com> <20060223211835.GZ86022@pervasive.com> <43FE4A1A.7050903@edesix.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43FE4A1A.7050903@edesix.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060223:robin@edesix.com::Kun+V8yxDTA+ck/Q:03D4f X-Hashcash: 1:20:060223:pgsql-admin@postgresql.org::EzJTJokl056ecWny:00000000000 0000000000000000000000005Le6 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.104 required=5 tests=[AWL=0.104] X-Spam-Score: 0.104 X-Spam-Level: X-Archive-Number: 200602/305 X-Sequence-Number: 20726 On Thu, Feb 23, 2006 at 11:49:46PM +0000, Robin Iddon wrote: > Jim C. Nasby wrote: > > >Please upgrade. > > > >Are you running with the default FSM settings? I'm guessing that > >pg_statistics has just gotten so large that it's blowing out the FSM. > >The last few lines from vacuumdb -av would verify that... > > > > > Jim, > > I am running this in a critical production environment, so I would like > to understand whether upgrading is my only option before I do it (I'll > need to do some testing first, then organise downtime, etc. - I will do > it if required, but would rather a workaround!). If I do upgrade, I > assume you mean upgrade to the current 8.1.x release? Well, as I mentioned elsewhere, there are some reasons to upgrade to 8.1.x. But no, my comment was specifically about upgrading to the latest 8.0 release. There have been both security and data-loss fixes that you really should get. > Yes, I am running with the default FSM settings. > > Here is the output from vacuumdb -v gems (I ran it against my tiny test > database, not the production database right now, but remember that even > this tiny test database had an enormous pg_statistic table): > > this is the last few lines as requested > INFO: free space map: 182 relations, 25120 pages stored; 98272 total > pages needed > DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB > shared memory. > You need 100k pages minimum here. > I don't actually understand exactly what this is telling me though! Take a look at http://www.pervasivepostgres.com/lp/newsletters/2005/Insights_opensource_Nov.asp#3 It's a good overview of the FSM, how it works, and how to understand vacuum verbose output. Though, I guess my opinion is biased since I wrote it... ;) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461