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 5D3369DC883 for ; Thu, 23 Feb 2006 15:04:07 -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 52131-01 for ; Thu, 23 Feb 2006 15:04:07 -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 C68269DCCF3 for ; Thu, 23 Feb 2006 15:04:04 -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 52A58E000200 for ; Thu, 23 Feb 2006 19:04:04 +0000 (GMT) Received: from visitor4.edesix.com ([192.168.1.154]) by flange.edesix.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.50) id 1FCLkx-0002mc-F9; Thu, 23 Feb 2006 19:04:03 +0000 Message-ID: <43FE0723.3070608@edesix.com> Date: Thu, 23 Feb 2006 19:04:03 +0000 From: Robin Iddon User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Robin Iddon Cc: pgsql-admin@postgresql.org Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table? References: <43FD7312.5080701@edesix.com> <43FDF046.2050609@zeut.net> <43FDFF9A.90603@edesix.com> In-Reply-To: <43FDFF9A.90603@edesix.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/283 X-Sequence-Number: 20704 > >> Third: Are you seeing in the autovacuum log that autovacuum is >> actually trying to vacuum tables? Turn up the debugging to -d2 that >> should give you some more info as to why autovac is (or is not) doing >> what it's doing. >> > I will turn it on and take a look. I am guessing it will tell me that > for whatever reason it isn't actually doing anything to the pg_catalog > tables ... So I ran pg_autovacuum with -d 2 on the command line and I see this at startup Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: Printing command_args Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->host=(null) Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->port=(null) Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->username=(null) Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->password=(null) Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->logfile=(null) Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->daemonize=0 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->sleep_base_value=300 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->sleep_scaling_factor=2.000000 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->vacuum_base_threshold=1000 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->vacuum_scaling_factor=2.000000 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->analyze_base_threshold=500 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->analyze_scaling_factor=1.000000 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->av_vacuum_cost_delay=(default) Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->av_vacuum_cost_page_hit=(default) Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->av_vacuum_cost_page_miss=(default) Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->av_vacuum_cost_page_dirty=(default) Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->av_vacuum_cost_limit=(default) Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: args->debug=2 This is the output for the pg_statistic table in the database that is tiny and is used for testing and has never been "vacuum full" as far as I know: Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: table name: gems."pg_catalog"."pg_statistic" Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: relid: 16408; relisshared: 0 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: reltuples: 709086.000000; relpages: 78341 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: curr_analyze_count: 500680; curr_vacuum_count: 500680 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: last_analyze_count: 500680; last_vacuum_count: 500680 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] INFO: analyze_threshold: 709586; vacuum_threshold: 1419172 Feb 23 18:46:42 helm daemon_pg_autovacuum: [2006-02-23 18:46:42 GMT] DEBUG: added table: gems."pg_catalog"."pg_statistic" The tuples and thresholds for the pg_statistic table look like they might be the reason it doesn't get vacuumed. The question is, why does the reltuples on the stats table keep climbing until you do a vacuum full, at which point they return to (near) zero? No matter what threshold and scale I set, I am not going to get that table vacuumed because it's delete/update rate are 0 (it's always inserted to I believe). After that I just see: DEBUG: 1 All DBs checked in: 478378 usec, will sleep for 300 secs. DEBUG: 2 All DBs checked in: 73336 usec, will sleep for 300 secs. Implying that there isn't anything doing right now (the staff are all gone, so there is no activity on the database as I type, and we know it won't vacuum the pg_statistics table due to thresholding). What am I missing? Thanks, Robin