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
elapsed0.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,
1156remain       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.