Re: AutoVacuum Behaviour Question

Bruce McAlister wrote:
> Hi All,
>
> I have enabled autovacuum in our PostgreSQL cluster of databases. What I
> have noticed is that the autovacuum process keeps selecting the same
> database to perform autovacuums on and does not select any of the others
> within the cluster. Is this normal behaviour or do I need to do
> something more elaborate with my settings?

There are two reasons autovacuum would keep picking up the same
database:

1. the other databases do not have pgstat entries.

2. this database is in danger of Xid wraparound and the vacuum run
fails to complete for some reason.

> Our main concern is the "blueface-service" database. The sipaccounts
> table has some high traffic, mainly updates.

Are there non-null values in the pg_stat views for tables in
blueface-service database? If there are, then you can discard (1) as
the problem. If all values are nulls for all tables, then you have the
stats collector disabled for that database, or something (maybe by ALTER
DATABASE ... SET). In this case, reenable it and issue a manual VACUUM
so that pgstat is populated. (I think the easiest way to check is
SELECT datname, datconfig FROM pg_database).

Regarding (2) you would need to check whether the autovacuum run dies
with an ERROR. I'd advise setting a log_line_prefix that included the
PID (%p) so that you can check whether the process goes away cleanly or
it dies early. It is not impossible that a corrupt index or table is
causing autovacuum to die, but it should certainly show up in the logs.

--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"El que vive para el futuro es un iluso, y el que vive para el pasado,
un imbécil" (Luis Adler, "Los tripulantes de la noche")