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 8A5AD9DC868 for ; Thu, 23 Feb 2006 04:32:26 -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 99911-06 for ; Thu, 23 Feb 2006 04:32:27 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 6566B9DC833 for ; Thu, 23 Feb 2006 04:32:24 -0400 (AST) Received: from astro.systems.pipex.net (astro.systems.pipex.net [62.241.163.6]) by svr4.postgresql.org (Postfix) with ESMTP id 234C35AF1CD for ; Thu, 23 Feb 2006 08:32:26 +0000 (GMT) Received: from flange.edesix.com (81-86-64-236.dsl.pipex.com [81.86.64.236]) by astro.systems.pipex.net (Postfix) with ESMTP id A9B81E00009B for ; Thu, 23 Feb 2006 08:32:21 +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 1FCAer-00021P-66 for pgsql-admin@postgresql.org; Thu, 23 Feb 2006 07:13:01 +0000 Message-ID: <43FD7312.5080701@edesix.com> Date: Thu, 23 Feb 2006 08:32:18 +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: 8.0.3 pg_autovacuum doesn't clear out stats table? 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 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200602/271 X-Sequence-Number: 20692 Hi, I wonder if anyone can help me to identify why my background pg_autovacuum doesn't appear to clear out the stats table entries? Here is the non-commented lines in my postgresql.conf: max_connections = 100 shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each vacuum_cost_delay = 10 # 0-1000 milliseconds stats_start_collector = true stats_row_level = true lc_messages = 'C' # locale for system error message strings lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting And here is how I start pg_autovacuum: # The PostgreSQL user is: PGUSER=postgres # LOG file is: PG_AUTOVACUUM_LOG=/var/lib/postgresql/data/pg_autovacuum.log # See the pg_autovacuum documentation for the details of how these # parameters affect pg_autovacuum's aggressiveness. VACUUM_BASE=1000 VACUUM_SCALE=2 SLEEP_BASE=300 SLEEP_SCALE=2 start-stop-daemon -o --chuid $PGUSER --start --quiet --exec /usr/bin/pg_autovacuum -- -D -v $VACUUM_BASE -V $VACUUM_SCALE -s $SLEEP_BASE -S $SLEEP_SCALE -L $PG_AUTOVACUUM_LOG The problem I have is that pg_autovacuum doesn't appear to release the unused space in the stats tables and so they grow without bounds. It isn't clear to me that this is costing me anything other than disk space, but when I run a vacuum full on the database it releases 2.5G of space accumulated over about 9 months. Most of my tables are update/append only (i.e. there are very few deletions from the database), and the space does appear to belong to the stats tables. Is this a permissions or configuration issue, or does pg_autovacuum not consider vacuuming of the stats tables? Thanks in advance for any help you can offer me ... Robin