autovacuum running even when not set in config?

Hello,

Recently we noticed this autovacuum process running on one of our db
servers, which is running pg 8.2.3.

2273 ? Ds 0:00 postgres: autovacuum process fy2006outcomesdb4091

Autovacuum is off in this machine's config. I verified that by
connecting via psql and running 'show autovacuum'. Also,
pg_autovacuum on this machine has no rows.

Has anyone seen this sort of behavior? Is it normal for autovacuuming
to start up once in a while even when the configuration does not call
for autovacuuming?

Thanks,

Douglas Birdzell
Senior Software Developer
Advanced Outcomes Design

http://www.aod.cx
voice: 202-872-1033
fax: 202-833-9168



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: autovacuum running even when not set in config?

Douglas Birdzell wrote:
> Hello,
>
> Recently we noticed this autovacuum process running on one of our db
> servers, which is running pg 8.2.3.
>
> 2273 ? Ds 0:00 postgres: autovacuum process fy2006outcomesdb4091
>
> Autovacuum is off in this machine's config. I verified that by
> connecting via psql and running 'show autovacuum'. Also,
> pg_autovacuum on this machine has no rows.

It is normal if the database is in danger of Xid wraparound. (So now
you know have been neglecting to vacuum some tables).

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"The important things in the world are problems with society that we don't
understand at all. The machines will become more complicated but they won't
be more complicated than the societies that run them." (Freeman Dyson)

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: autovacuum running even when not set in config?

Well, the database is also showing a hint when less then 9 million
transactions are available - and this hint is telling us to vacuum
the whole database.

If, vacuum the whole database, would take days (quite unsure how
many days sometimes) is it possible to find out manually which
tables originally need a vacuum or is the xid wraparound problem
database wide so we MUST vacuum the whole database?

The automatic startup of autovacuum in this cases is really good,
but we would like to manage vacuum by ourself, especially WHEN it is
running, because we have different load times on different database
servers depending on time zones and the users using it.

> It is normal if the database is in danger of Xid wraparound. (So now
> you know have been neglecting to vacuum some tables).
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: autovacuum running even when not set in config?

Aldor wrote:
> Well, the database is also showing a hint when less then 9 million
> transactions are available - and this hint is telling us to vacuum
> the whole database.
>
> If, vacuum the whole database, would take days (quite unsure how
> many days sometimes) is it possible to find out manually which
> tables originally need a vacuum or is the xid wraparound problem
> database wide so we MUST vacuum the whole database?

On 8.2 this is per table. You can check which tables need vacuum with
select age(relfreezexid) from pg_class
(I'm not sure if the column is relfreezexid or relfrozenxid).

> The automatic startup of autovacuum in this cases is really good,
> but we would like to manage vacuum by ourself, especially WHEN it is
> running, because we have different load times on different database
> servers depending on time zones and the users using it.

Yeah, this is something we are planning for 8.4.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq