Hi all,
I'm not seeing any records in pg_stat_progress_vacuum even though there are always around three autovacuum operations
listedin pg_stat_activity and at least one of them is "(to prevent wraparound)". They are in state 'active' and
state_changeis within the last 3 hours. When logging vacuums I see one just completed stating the elapsed time was 9
hours.
It seems to be trying to catch up from a long running transaction. From the vacuum logs I can say it's progressing
althoughit's not working on the tables with the oldest xids.
I'm mainly wanting to understand why I'm not seeing processes in pg_stat_progress_vacuum. If I rapidly refresh I
occasionallysee an entry for a very small table. A manually started vacuum didn't show up either.
Pg version 11.4
Rebooted a couple days ago which may have cleared a hung transaction. Logs were not enabled at the time.
Disk IO is fluctuating to a degree it doesn't seem like any bottleneck. I'm not convinced the RAID is performing as
wellas it should but I don't think it's bad enough to be of concern.
I have 2 other 11.4 installations where I've never had an issue relying on pg_stat_progress_vacuum.
stats_temp_directory = '/var/run/postgresql' and directory contents are updating
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 6
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 4
autovacuum_vacuum_cost_delay | 20
autovacuum_vacuum_cost_limit | 1500
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50
autovacuum_work_mem | -1
work_mem | 10240
Thanks for any hints and recommendations,
Senor