Content-Type: text/plain; charset=UTF-8
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Autovacuum daemon internal handling
From: Alvaro Herrera <alvherre@commandprompt.com>
To: gnanam <gnanam@zoniac.com>
In-reply-to: <002401cb39da$bf04aec0$3d0e0c40$@com>
References: <001d01cb3913$0e5c8f60$2b15ae20$@com>
	<1281539491-sup-7017@alvh.no-ip.org>
	<002401cb39da$bf04aec0$3d0e0c40$@com>
Date: Thu, 12 Aug 2010 14:34:06 -0400
Message-Id: <1281637751-sup-2462@alvh.no-ip.org>
User-Agent: Sup/git
Content-Transfer-Encoding: 8bit

Excerpts from Gnanakumar's message of jue ago 12 00:56:34 -0400 2010:
> > in 8.2 "naptime" means "time to sleep after we finish a job".  So even
> > if the previous task takes an hour, it will still sleep a minute before
> > doing another round.  (Note that this setting has a different meaning in
> > later releases).
> 
> I couldn’t understand the difference in meaning of "autovacuum_naptime" between 8.2 and later releases from the documentation.  May be I'm not understanding/seeing the subtle difference in the documentation lines mentioned.  I referred these 3 doc links:
> http://www.postgresql.org/docs/8.2/interactive/runtime-config-autovacuum.html
> http://www.postgresql.org/docs/8.3/interactive/runtime-config-autovacuum.html
> http://www.postgresql.org/docs/8.4/interactive/runtime-config-autovacuum.html
> 
> 8.2 doc says "Specifies the delay between activity rounds for the autovacuum daemon...."
> 8.3 & 8.4 doc says "Specifies the minimum delay between autovacuum runs on any given database....."
> 
> Can you please make me clear on this?

The main difference is that 8.2 has only one process working at a time,
whereas in 8.3 and later there can be several.  When there's only one
process, the only way for it to process several databases is
sequentially; the naptime is how long to sleep between each item in the
list.

In 8.3, naptime is the time that lapses between two processes starting,
while respecting the limit of maximum processes that you configured in
max_workers.  So if the time is already up but all processes are busy
elsewhere, the next autovacuum will have to wait until one of those
finishes.

> > If autovacuum cannot keep up with all the vacuumable tables, you're in
> > trouble and should probably schedule vacuum externally.  (This also
> > changed in later releases).
> 
> Can you please point me to relevant documentation links on this change?

Since there are multiple processes, you can configure more so that all
tables can be vacuumed in time.

> >> 2. The columns "last_autovaccum" and 'last_autoanalyze" in
> >> pg_stat_user_tables shows the start time or end time of the operation?
> 
> > End time.
> Is there any way to find out the start time of "last_autovaccum" and/or 'last_autoanalyze" for a given table in 8.2.3?  So that I can isolate the tables that are taking too long time to complete vacuum and/or analyze and I can perform them externally if need.

No, I don't think there is in 8.2, unless you crank the debug level down
to DEBUG2 which means log a lot of stuff (probably too noisy to be
useful).

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support