X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 30F3BD1B4B4 for ; Tue, 2 Dec 2003 20:38:22 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 94230-05 for ; Tue, 2 Dec 2003 16:37:56 -0400 (AST) Received: from yertle.kcilink.com (yertle.kcilink.com [216.194.193.105]) by svr1.postgresql.org (Postfix) with ESMTP id 1DB69D1D458 for ; Tue, 2 Dec 2003 16:37:50 -0400 (AST) Received: by yertle.kcilink.com (Postfix, from userid 100) id 4CE7C2178C; Tue, 2 Dec 2003 15:37:40 -0500 (EST) From: Vivek Khera MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <16332.63508.196365.801418@yertle.int.kciLink.com> Date: Tue, 2 Dec 2003 15:37:40 -0500 To: pgsql-performance@postgresql.org Subject: autovacuum daemon stops doing work after about an hour X-Mailer: VM 7.17 under 21.4 (patch 14) "Reasonable Discussion" XEmacs Lucid X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/34 X-Sequence-Number: 4894 I took advantage of last weekend to upgrade from 7.2.4 to 7.4.0 on a new faster box. Now I'm trying to implement pg_autovacuum. It seems to work ok, but after about an hour or so, it does nothing. The process still is running, but nothing is sent to the log file. I'm running the daemon as distributed with PG 7.4 release as follows: pg_autovacuum -d4 -V 0.15 -A 1 -U postgres -L /var/tmp/autovacuum.log -D the last few lines of the log are: [2003-12-02 11:43:58 AM] VACUUM ANALYZE "public"."msg_recipients" [2003-12-02 12:24:33 PM] select relfilenode,reltuples,relpages from pg_class where relfilenode=18588239 [2003-12-02 12:24:33 PM] table name: vkmlm."public"."msg_recipients" [2003-12-02 12:24:33 PM] relfilenode: 18588239; relisshared: 0 [2003-12-02 12:24:33 PM] reltuples: 9; relpages: 529132 [2003-12-02 12:24:33 PM] curr_analyze_count: 1961488; cur_delete_count: 1005040 [2003-12-02 12:24:33 PM] ins_at_last_analyze: 1961488; del_at_last_vacuum: 1005040 [2003-12-02 12:24:33 PM] insert_threshold: 509; delete_threshold 1001 [2003-12-02 12:24:33 PM] Performing: VACUUM ANALYZE "public"."user_list" [2003-12-02 12:24:33 PM] VACUUM ANALYZE "public"."user_list" [2003-12-02 12:43:19 PM] select relfilenode,reltuples,relpages from pg_class where relfilenode=18588202 [2003-12-02 12:43:19 PM] table name: vkmlm."public"."user_list" [2003-12-02 12:43:19 PM] relfilenode: 18588202; relisshared: 0 [2003-12-02 12:43:19 PM] reltuples: 9; relpages: 391988 [2003-12-02 12:43:19 PM] curr_analyze_count: 1159843; cur_delete_count: 1118540 [2003-12-02 12:43:19 PM] ins_at_last_analyze: 1159843; del_at_last_vacuum: 1118540 [2003-12-02 12:43:19 PM] insert_threshold: 509; delete_threshold 1001 Then it just sits there. I started it at 11:35am, and it is now 3:30pm. I did the same last night at about 10:58pm, and it ran and did work until 11:57pm, then sat there until I killed/restarted pg_autovacuum this morning at 11:35. The process is not using any CPU time. I just killed/restarted it and it found work to do on my busy tables which I'd expect. I'm running Postgres 7.4 release on FreeBSD 4.9-RELEASE. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/