On Mon, 2020-03-02 at 19:57 -0700, Chris Kim wrote: > 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! Some ideas: - Did somebody run SELECT pg_stat_reset(); That would reset the statistics and explain that you see nothing in "pg_stat_user_tables". - After you ANALYZE the database, do you see something in "pg_stat_user_tables"? You should see something then. - Another explaination for the missing statistics could be a problem with the statistics collector process. Do you see and pertinent errors in the database log? If you restart the database, do you get errors from the statistics collector? Does a restart fix the problem? - Are the "track_activities" and "track_counts" parameters set to on? - Hitting 90% of autovacuum_freeze_max_age is no problem. It only means that you'll get some anti-wraparound autovacuums some time soon. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
