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 0BF8FD1B4A7 for ; Thu, 4 Dec 2003 05:41:35 +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 73588-04 for ; Thu, 4 Dec 2003 01:41:04 -0400 (AST) Received: from mta11.srv.hcvlny.cv.net (mta11.srv.hcvlny.cv.net [167.206.5.86]) by svr1.postgresql.org (Postfix) with ESMTP id 03288D1B579 for ; Thu, 4 Dec 2003 01:41:03 -0400 (AST) Received: from [192.168.1.10] (ool-4352919e.dyn.optonline.net [67.82.145.158]) by mta11.srv.hcvlny.cv.net (iPlanet Messaging Server 5.2 HotFix 1.16 (built May 14 2003)) with ESMTP id <0HPC00JY4VOM7B@mta11.srv.hcvlny.cv.net> for pgsql-performance@postgresql.org; Thu, 04 Dec 2003 00:38:47 -0500 (EST) Date: Thu, 04 Dec 2003 00:38:46 -0500 From: "Matthew T. O'Connor" Subject: Re: autovacuum daemon stops doing work after about an hour In-reply-to: <16332.63508.196365.801418@yertle.int.kciLink.com> To: Vivek Khera Cc: pgsql-performance@postgresql.org Message-id: <1070516326.1455.11.camel@zedora.zeut.net> MIME-version: 1.0 X-Mailer: Ximian Evolution 1.4.5 (1.4.5-7) Content-type: text/plain Content-transfer-encoding: 7BIT References: <16332.63508.196365.801418@yertle.int.kciLink.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/66 X-Sequence-Number: 4926 On Tue, 2003-12-02 at 15:37, Vivek Khera wrote: > 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. Weird.... Alphabetically speaking, is vkmlm."public"."user_list" be the last table in the last schema in the last database? You are running with -d4, so you would get a message about going to sleep shortly after dealing with the last table, but you didn't get the sleep message, so I don't think the problem is that pg_autovacuum is sleeping for an inordinate amount time. > 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. when you kill it, do you get a core file? Could you do a backtrace and see where pg_autovacuum is hung up? > I'm running Postgres 7.4 release on FreeBSD 4.9-RELEASE. I don't run FreeBSD, so I haven't tested with FreeBSD. Recently Craig Boston reported and submitted a patch for a crash on FreeBSD, but that doesn't sound like your problem. Could be some other type of platform dependent problem.