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 B0DA29DC84D for ; Sat, 25 Feb 2006 04:15:19 -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 04102-04 for ; Sat, 25 Feb 2006 04:15:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from blaster.systems.pipex.net (blaster.systems.pipex.net [62.241.163.7]) by postgresql.org (Postfix) with ESMTP id 0B35D9DC80E for ; Sat, 25 Feb 2006 04:15:15 -0400 (AST) Received: from flange.edesix.com (81-86-64-236.dsl.pipex.com [81.86.64.236]) by blaster.systems.pipex.net (Postfix) with ESMTP id EAE65E000221 for ; Sat, 25 Feb 2006 08:15:14 +0000 (GMT) Received: from host86-131-248-27.range86-131.btcentralplus.com ([86.131.248.27] helo=[192.168.101.208]) by flange.edesix.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.50) id 1FCuaA-0004Pf-KN for pgsql-admin@postgresql.org; Sat, 25 Feb 2006 08:15:14 +0000 Message-ID: <44001213.6060005@edesix.com> Date: Sat, 25 Feb 2006 08:15:15 +0000 From: Robin Iddon User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table? References: <43FD7312.5080701@edesix.com> <20060223211835.GZ86022@pervasive.com> <43FE4A1A.7050903@edesix.com> <20060223235829.GP2068@pervasive.com> In-Reply-To: <20060223235829.GP2068@pervasive.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200602/311 X-Sequence-Number: 20732 >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... ;) > > OK, I read that and understand now what is happening - thanks! (1) I think the FSM was a possible cause of the problem - the pg_statistics table had nowhere to store the space released by vacuuming. I have fixed that by increasing FSM space by 10x for now and will monitor vacuumdb -av output periodically to make sure the database stays within the bounds of the FSM. (2) I still think that the 8.0 default vacuum base threshold and scale mean that the pg_statistic table might never trigger the vacuum threshold and would like to check my understanding is correct: The process of analyzing my tables causes some or all of the rows in the pg_statistic table to be updated. . Each row update in postgres causes a new tuple to be created to represent the new row version. So each updated statistic makes the pg_stat_all_tables.n_tup_upd for pg_statistic climb by 1. It also makes the pg_class.reltuples for pg_statistic climb by 1 (new row version). 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") 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. Thanks for all the help you've offered so far. Robin