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 1FC849DCA77 for ; Thu, 23 Feb 2006 19:49:50 -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 95512-03 for ; Thu, 23 Feb 2006 19:49:52 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id AF4FD9DC986 for ; Thu, 23 Feb 2006 19:49:47 -0400 (AST) Received: from ranger.systems.pipex.net (ranger.systems.pipex.net [62.241.162.32]) by svr4.postgresql.org (Postfix) with ESMTP id 439895AF83B for ; Thu, 23 Feb 2006 23:49:51 +0000 (GMT) Received: from flange.edesix.com (81-86-64-236.dsl.pipex.com [81.86.64.236]) by ranger.systems.pipex.net (Postfix) with ESMTP id 1609DE000289 for ; Thu, 23 Feb 2006 23:49:46 +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 1FCQDS-0006Je-Ds for pgsql-admin@postgresql.org; Thu, 23 Feb 2006 23:49:46 +0000 Message-ID: <43FE4A1A.7050903@edesix.com> Date: Thu, 23 Feb 2006 23:49:46 +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> In-Reply-To: <20060223211835.GZ86022@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/304 X-Sequence-Number: 20725 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? 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 section relates to vacuuming pg_statistic INFO: vacuuming "pg_catalog.pg_statistic" INFO: index "pg_statistic_relid_att_index" now contains 293 row versions in 15421 pages DETAIL: 2265351 index row versions were removed. 15072 index pages have been deleted, 7480 are currently reusable. CPU 3.25s/1.54u sec elapsed 304.19 sec. 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. Here is the output from vacuumdb -av on my standby server which has a recent copy of the main production data (via pg_dump/pg_restore) but has very few (if any) transactions executed on it since the copy was made. the section relates to the copy of the production database on the backup server INFO: vacuuming "pg_catalog.pg_statistic" INFO: index "pg_statistic_relid_att_index" now contains 3821225 row versions in 35676 pages DETAIL: 2795920 index row versions were removed. 19879 index pages have been deleted, 11950 are currently reusable. CPU 6.58s/7.35u sec elapsed 321.85 sec. INFO: "pg_statistic": removed 2795920 row versions in 81619 pages DETAIL: CPU 17.89s/4.45u sec elapsed 191.72 sec. INFO: index "pg_statistic_relid_att_index" now contains 1025283 row versions in 35676 pages DETAIL: 2795942 index row versions were removed. 28947 index pages have been deleted, 11950 are currently reusable. CPU 6.15s/6.77u sec elapsed 120.23 sec. INFO: "pg_statistic": removed 2795942 row versions in 82363 pages DETAIL: CPU 18.23s/4.48u sec elapsed 161.45 sec. INFO: index "pg_statistic_relid_att_index" now contains 1554 row versions in 35676 pages DETAIL: 1023729 index row versions were removed. 34045 index pages have been deleted, 11950 are currently reusable. CPU 3.01s/2.31u sec elapsed 46.31 sec. INFO: "pg_statistic": removed 1023729 row versions in 30288 pages DETAIL: CPU 6.96s/1.50u sec elapsed 69.42 sec. INFO: "pg_statistic": found 6615591 removable, 1554 nonremovable row versions in 395203 pages DETAIL: 0 dead row versions cannot be removed yet. There were 7545029 unused item pointers. 0 pages are entirely empty. CPU 64.16s/28.04u sec elapsed 1017.92 sec. All of the FSM sections from the whole cluster during the vacuumdb -av run. INFO: free space map: 6 relations, 19936 pages stored; 21632 total pages needed DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB shared memory. INFO: free space map: 6 relations, 19936 pages stored; 21632 total pages needed DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB shared memory. INFO: free space map: 6 relations, 19939 pages stored; 21648 total pages needed DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB shared memory. INFO: free space map: 6 relations, 19939 pages stored; 21648 total pages needed DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB shared memory. INFO: free space map: 6 relations, 19975 pages stored; 29600 total pages needed DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB shared memory. I don't actually understand exactly what this is telling me though! Thanks, Robin