Autovacuum/Analyze Doesn't seem to be running properly

[Chris Kim <christopherkim22@xxxxxxxxx>]

Hello,

I have a cluster with these parameters (no changed settings for any of the database/tables):
 
 autovacuum                          | on                                                                                                                          
 autovacuum_analyze_scale_factor     | 0.05                                                                                                                        
 autovacuum_analyze_threshold        | 25                                                                                                                          
 autovacuum_freeze_max_age           | 200000000                                                                                                                  
 autovacuum_max_workers              | 6                                                                                                                          
 autovacuum_multixact_freeze_max_age | 400000000                                                                                                                  
 autovacuum_naptime                  | 15s                                                                                                                        
 autovacuum_vacuum_cost_delay        | 20ms                                                                                                                      
 autovacuum_vacuum_cost_limit        | -1                                                                                                                          
 autovacuum_vacuum_scale_factor      | 0.1  
 autovacuum_vacuum_threshold         | 25                                                                                                                          
 autovacuum_work_mem                 | -1

I have about 20 database with thousands of tuples (live and dead) but when I look at the pg_stat_user_tables within each database:

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum, last_analyze


FROM pg_stat_user_tables;




all n_live_tups are 0 and all dead tups are 0, with null data for last_vacuum, and last_analyze which seems very odd to me.

I can see there's live rows with select count and then I analyze a database, then I see live and dead tuples, and last_analyze shows the timestamp of when I just ran ANALYZE;

Am I missing something here or is this abnormal behaviour?

I'm also seeing my databases are above 90% towards hitting my autovacuum_max_freeze-age using this command.

SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname

  FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname.

  FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo

  ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;

I also have looked for vacuum via ps -ef | grep vacuum but didn't get any return.

And select * from pg_stat_progress_vacuum shows nothing as well.

I shouldn't have to create cron jobs to run analyze and vacuum manually for each database, but I don't know how to further investigate this issue.

Has anyone seen this before? Any advise would be greatly appreciated!

Thanks in advance for the help,

Chris