Обсуждение: Question regarding autovacuum in 8.1

How is it possibile to check if autovacuum is running in 8.1.x?

"Show Autovacuum" gives me "on" and also i see evidence in logs
where,autovacuum writes "LOG:  autovacuum: processing database xxxx".

However i have no idea of what tables the autovacuum daemon is
processing because there aren't autovacuum info columns on
pg_stat_all_tables (as there are for 8.2.x).

Also I'm asking this because the size of the pg_clog is >200M and I am
worried about possible transaction  ID wraparound failures

Thank you,
Denis

Denis Gasparin wrote:
> How is it possibile to check if autovacuum is running in 8.1.x?
>
> "Show Autovacuum" gives me "on" and also i see evidence in logs
> where,autovacuum writes "LOG:  autovacuum: processing database xxxx".

Then it is running.

> However i have no idea of what tables the autovacuum daemon is
> processing because there aren't autovacuum info columns on
> pg_stat_all_tables (as there are for 8.2.x).

For that, you need to change log_min_messages to debug2.

Keep track of the PID of autovacuum from the "processing database"
message and see if you can spot an ERROR message from it.

--
Alvaro Herrera                          Developer, http://www.PostgreSQL.org/
"A wizard is never late, Frodo Baggins, nor is he early.
 He arrives precisely when he means to."  (Gandalf, en LoTR FoTR)

Alvaro Herrera ha scritto:

However i have no idea of what tables the autovacuum daemon is
processing because there aren't autovacuum info columns on
pg_stat_all_tables (as there are for 8.2.x).   

For that, you need to change log_min_messages to debug2.

Keep track of the PID of autovacuum from the "processing database"
message and see if you can spot an ERROR message from it.
 

Ok. Thank you.
Another question/idea: why don't put messages about what tables got vacuumed by the autovacuum daemon as normal log messages (instead of debug2)?
I think it could be useful because in this way you can also know what tables are used more often then other...
If i'm not wrong, the old autovacuum process in 7.4 and 8.0 did that...

Denis

Denis Gasparin wrote:

> Another question/idea: why don't put messages about what tables got
> vacuumed by the autovacuum daemon as normal log messages (instead of
> debug2)?

We did that for 8.3, actually.

> I think it could be useful because in this way you can also know what
> tables are used more often then other...

Well, if you want to know which tables are used more, check the pg_stat
views.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"No hay cielo posible sin hundir nuestras raíces
 en la profundidad de la tierra"                        (Malucha Pinto)