Re: Need help debugging why autovacuum seems "stuck" -- until I use superuser to vacuum freeze pg_database

I enabled debug level and can see that the launcher does in fact spawn a
worker, but, it appears, scans and disappears in milliseconds.

It was stuck again this morning and it was spitting this out ad nauseum for
"my_database_name":

2016-05-11 16:03:42 UTC::@:[13567]:DEBUG: pg_database: vac: 0 (threshold
141), anl: 57 (threshold 166)
2016-05-11 16:03:42 UTC::@:[13567]:DEBUG: autovac_balance_cost(pid=13567
db=143187, rel=1262, dobalance=yes cost_limit=1000, cost_limit_base=1000,
cost_delay=10)
2016-05-11 16:03:42 UTC::@:[13567]:DEBUG: vacuuming
"pg_catalog.pg_database"
2016-05-11 16:03:42 UTC::@:[13567]:DEBUG: index "pg_database_datname_index"
now contains 1325 row versions in 11 pages
2016-05-11 16:03:42 UTC::@:[13567]:DETAIL: 0 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
2016-05-11 16:03:42 UTC::@:[13567]:DEBUG: index "pg_database_oid_index" now
contains 1325 row versions in 6 pages
2016-05-11 16:03:42 UTC::@:[13567]:DETAIL: 0 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
2016-05-11 16:03:42 UTC::@:[13567]:DEBUG: "pg_database": found 0 removable,
0 nonremovable row versions in 0 out of 43 pages
2016-05-11 16:03:42 UTC::@:[13567]:DETAIL: 0 dead row versions cannot be
removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
2016-05-11 16:03:42 UTC::@:[13567]:LOG: automatic vacuum of table
"my_database_name.pg_catalog.pg_database": index scans: 0
pages: 0 removed, 43 remain
tuples: 0 removed, 1156 remain
buffer usage: 45 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

The problem is though, when I look at age(datfrozenxid) for
"my_database_name" it is the oldest at 654,189,546 and should trigger the
autovacuum_freeze_max_age of 200,000,000 but it doesn't. The relation with
this age is "pg_database". If I issue a manual "vacuum freeze pg_database"
in the database, autovacuum will run on the next oldest database but get
stuck again with same symptoms (i.e. pg_database is the relation with the
oldest age). If I don't issue the manual "vacuum freeze" for all databases
and left things alone, it would get to wrap-around state.

--
View this message in context: http://postgresql.nabble.com/Need-help-debugging-why-autovacuum-seems-stuck-until-I-use-superuser-to-vacuum-freeze-pg-database-tp5901425p5903104.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.