Re: autovacuum daemon stops doing work after about an hour - Mailing list pgsql-performance

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.