Autovacuum daemon internal handling

Hi,

Our production server is running PostgreSQL v8.2.3 on Red Hat Enterprise
Linux Server release 5 (Tikanga).

I need a clarification on how autovacuum daemon internally works/handles in
the following specific use case/situation:

1. Does autovacuum daemon works with one table at a time or does it work
with multiple tables at the same time?
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?

2. The columns "last_autovaccum" and 'last_autoanalyze" in
pg_stat_user_tables shows the start time or end time of the operation?

Regards,
Gnanam


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Autovacuum daemon internal handling

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 wor=
k
> with multiple tables at the same time?=20

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 defi=
ned
> 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.

--=20
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Autovacuum daemon internal handling

"Gnanakumar" wrote:

> Our production server is running PostgreSQL v8.2.3

There are a lot of bug fixes and security fixes you're missing by
not using a recent minor release:

http://www.postgresql.org/support/versioning

http://www.postgresql.org/docs/8.2/static/release.html

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Autovacuum daemon internal handling

> 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â€=99t understand the difference in meaning of "autovacuum_napt=
ime" between 8.2 and later releases from the documentation. May be I'm not=
understanding/seeing the subtle difference in the documentation lines ment=
ioned. I referred these 3 doc links:
http://www.postgresql.org/docs/8.2/interactive/runtime-confi g-autovacuum.ht=
ml
http://www.postgresql.org/docs/8.3/interactive/runtime-confi g-autovacuum.ht=
ml
http://www.postgresql.org/docs/8.4/interactive/runtime-confi g-autovacuum.ht=
ml

8.2 doc says "Specifies the delay between activity rounds for the autovacuu=
m 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?

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

>> 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 'la=
st_autoanalyze" for a given table in 8.2.3? So that I can isolate the tabl=
es that are taking too long time to complete vacuum and/or analyze and I ca=
n perform them externally if need.



--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Autovacuum daemon internal handling

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 eve=
n
> > if the previous task takes an hour, it will still sleep a minute befo=
re
> > doing another round. (Note that this setting has a different meaning=
in
> > later releases).
>=20
> I couldnâ€=99t 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 li=
nes mentioned. I referred these 3 doc links:
> http://www.postgresql.org/docs/8.2/interactive/runtime-confi g-autovacuu=
m.html
> http://www.postgresql.org/docs/8.3/interactive/runtime-confi g-autovacuu=
m.html
> http://www.postgresql.org/docs/8.4/interactive/runtime-confi g-autovacuu=
m.html
>=20
> 8.2 doc says "Specifies the delay between activity rounds for the autov=
acuum daemon...."
> 8.3 & 8.4 doc says "Specifies the minimum delay between autovacuum runs=
on any given database....."
>=20
> 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 i=
n
> > trouble and should probably schedule vacuum externally. (This also
> > changed in later releases).
>=20
> 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 operatio=
n?
>=20
> > 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 th=
e 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).

--=20
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Autovacuum daemon internal handling

Thanks for the update.

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

> Since there are multiple processes, you can configure more so that all
> tables can be vacuumed in 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 t=
ables 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).


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin