pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matthew T. O'Connor <matthew@zeut.net>
To: Vivek Khera <khera@kcilink.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum daemon stops doing work after about an	hour
Date: Thu, 04 Dec 2003 00:38:46 -0500
Message-ID: <1070516326.1455.11.camel@zedora.zeut.net> (raw)
In-Reply-To: <16332.63508.196365.801418@yertle.int.kciLink.com>
References: <16332.63508.196365.801418@yertle.int.kciLink.com>

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. 





view thread (16+ messages)  latest in thread

Message-ID: <1070516326.1455.11.camel@zedora.zeut.net>
Permalink:  ../1070516326.1455.11.camel@zedora.zeut.net/
Also on:    postgresql.org/message-id/1070516326.1455.11.camel@zedora.zeut.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: matthew@zeut.net, khera@kcilink.com
  Subject: Re: autovacuum daemon stops doing work after about an	hour
  In-Reply-To: <1070516326.1455.11.camel@zedora.zeut.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox