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 A4873D1B49D for ; Thu, 4 Dec 2003 16:34:32 +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 70287-04 for ; Thu, 4 Dec 2003 12:34:01 -0400 (AST) Received: from yertle.kcilink.com (yertle.kcilink.com [216.194.193.105]) by svr1.postgresql.org (Postfix) with ESMTP id A30A4D1B4A1 for ; Thu, 4 Dec 2003 12:34:00 -0400 (AST) Received: by yertle.kcilink.com (Postfix, from userid 100) id 3B86E2178B; Thu, 4 Dec 2003 11:33:59 -0500 (EST) From: Vivek Khera MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <16335.25079.106697.7521@yertle.int.kciLink.com> Date: Thu, 4 Dec 2003 11:33:59 -0500 To: pgsql-performance@postgresql.org Subject: Re: autovacuum daemon stops doing work after about an hour In-Reply-To: <1070516326.1455.11.camel@zedora.zeut.net> References: <16332.63508.196365.801418@yertle.int.kciLink.com> <1070516326.1455.11.camel@zedora.zeut.net> 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/72 X-Sequence-Number: 4932 >>>>> "MTO" == Matthew T O'Connor writes: >> Then it just sits there. I started it at 11:35am, and it is now >> 3:30pm. MTO> Weird.... Alphabetically speaking, is vkmlm."public"."user_list" be the MTO> last table in the last schema in the last database? You are running conveniently, yes it is... MTO> with -d4, so you would get a message about going to sleep shortly after MTO> dealing with the last table, but you didn't get the sleep message, so I MTO> don't think the problem is that pg_autovacuum is sleeping for an MTO> inordinate amount time. The only sleep logged was [2003-12-03 04:47:13 PM] 1 All DBs checked in: 84996853 usec, will sleep for 469 secs. Here's all it did on yesterday afternoon's "hour of work": [2003-12-03 04:45:48 PM] Performing: ANALYZE "public"."url_track" [2003-12-03 04:46:27 PM] Performing: ANALYZE "public"."msg_recipients" [2003-12-03 04:46:55 PM] Performing: ANALYZE "public"."deliveries" [2003-12-03 04:46:55 PM] Performing: ANALYZE "public"."user_list" [2003-12-03 04:47:12 PM] Performing: ANALYZE "public"."sessions" [2003-12-03 04:55:02 PM] Performing: ANALYZE "public"."url_track" [2003-12-03 04:55:22 PM] Performing: VACUUM ANALYZE "public"."msg_recipients" [2003-12-03 05:40:11 PM] Performing: VACUUM ANALYZE "public"."user_list" then 18 minutes later, it reported: [2003-12-03 05:58:25 PM] select relfilenode,reltuples,relpages from pg_class where relfilenode=18588202 [2003-12-03 05:58:25 PM] table name: vkmlm."public"."user_list" [2003-12-03 05:58:25 PM] relfilenode: 18588202; relisshared: 0 [2003-12-03 05:58:25 PM] reltuples: 9; relpages: 427920 [2003-12-03 05:58:25 PM] curr_analyze_count: 2559236; cur_delete_count: 2475824 [2003-12-03 05:58:25 PM] ins_at_last_analyze: 2559236; del_at_last_vacuum: 2475824 [2003-12-03 05:58:25 PM] insert_threshold: 509; delete_threshold 1001 and stopped doing anything. MTO> when you kill it, do you get a core file? Could you do a backtrace and MTO> see where pg_autovacuum is hung up? nope. unfortunately my PG libs are without debugging, too. I'll rebuild pg_autovacuum with debugging and run it under gdb so I can see where it gets stuck. I'll report back when I find something. I just wanted to check first if anyone else ran into this.