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 8F34E9DC897 for ; Sat, 25 Feb 2006 19:00:43 -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 85653-03 for ; Sat, 25 Feb 2006 19:00:45 -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 38C6C9DC894 for ; Sat, 25 Feb 2006 19:00:39 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id E69925642F; Sat, 25 Feb 2006 17:01:28 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Sat, 25 Feb 2006 17:01:28 -0600 Date: Sat, 25 Feb 2006 17:01:28 -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: <20060225230128.GO82012@pervasive.com> References: <43FD7312.5080701@edesix.com> <20060223211835.GZ86022@pervasive.com> <43FE4A1A.7050903@edesix.com> <20060223235829.GP2068@pervasive.com> <44001213.6060005@edesix.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <44001213.6060005@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:060225:robin@edesix.com::LB7YKfq11j9m05uN:042Xj X-Hashcash: 1:20:060225:pgsql-admin@postgresql.org::IOQknfrJ6+yjfipN:00000000000 0000000000000000000000001IzI 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/312 X-Sequence-Number: 20733 On Sat, Feb 25, 2006 at 08:15:15AM +0000, Robin Iddon wrote: > From README.pg_autovacuum (and I think 22.1.4 of the 8.1.x document > says the same thing): > > - If the number of (deletes + updates) > VacuumThreshold, then a > vacuum analyze is performed. > > VacuumThreshold is equal to: > vacuum_base_value + (vacuum_scaling_factor * "number of tuples in > the table") By "number of tuples in the table", I'm pretty sure they mean visible tuples; ie: the row count. In any case, I've seen pg_autovacuum work with my own eyes, so as long as it stays running long enough there's no reason it shouldn't end up vacuuming pg_statistics. > In general deletes is 0 for the pg_statistic table so for the default > values we're looking at > > n_tup_upd >= 1000 + (2.0 * reltuples) > > to trigger a vacuum. > > We know for each increment of n_tup_upd, reltuples will receive the same > increment. Thus if the above expression isn't true for any non-zero > starting values of n_tup_upd and reltuples, it won't ever be true: > > If: > n_tup_upd < 1000 + (2.0 * reltuples) > Then: > n_tup_upd+X < 1000 + (2.0 * (reltuples+X)) > > I see that in 8.1.x this has been resolved by defaulting the scale to > 0.4. Rightly or wrongly I have set my scale to 0.3. You should probably drop the threshold to something closer to 200, too. -- 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