Re: autovacuum daemon stops doing work after about an hour

>>>>> "MTO" == Matthew T O'Connor <matthew(at)zeut(dot)net> 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.