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: Gnanakumar <gnanam@zoniac.com>
In-reply-to: <001d01cb3913$0e5c8f60$2b15ae20$@com>
References: <001d01cb3913$0e5c8f60$2b15ae20$@com>
Date: Wed, 11 Aug 2010 11:17:28 -0400
Message-Id: <1281539491-sup-7017@alvh.no-ip.org>
User-Agent: Sup/git
Content-Transfer-Encoding: 8bit

Excerpts from Gnanakumar's message of mié ago 11 01:07:08 -0400 2010:

Hi,

> 1. Does autovacuum daemon works with one table at a time or does it work
> with multiple tables at the same time? 

Only one.

> Reason to ask this question is, let's say I've "autovacuum_naptime" set to 1
> minute, and there are 500 tables in one database.   Assuming there are 10
> tables that has to be vacuumed and analyzed based on the threshold defined
> in one of the autovacuum daemon execution time, and if takes more than 1
> minute to complete vacuum and analyze operation on even a single table (out
> of 10 tables), how does autovacuum daemon works/handles in this case?

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).

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).

> 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.

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