Re: autovacuum daemon stops doing work after about an hour

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.