empty pg_stat_progress_vacuum

Started by senorover 3 years ago2 messagesgeneral
Jump to latest
#1senor
frio_cervesa@hotmail.com

Hi all,
I'm not seeing any records in pg_stat_progress_vacuum even though there are always around three autovacuum operations listed in pg_stat_activity and at least one of them is "(to prevent wraparound)". They are in state 'active' and state_change is 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 although it'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 occasionally see 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 well as 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

#2Michael Paquier
michael@paquier.xyz
In reply to: senor (#1)
Re: empty pg_stat_progress_vacuum

On Fri, Oct 21, 2022 at 10:21:23PM +0000, senor wrote:

I'm mainly wanting to understand why I'm not seeing processes in
pg_stat_progress_vacuum. If I rapidly refresh I occasionally see an
entry for a very small table. A manually started vacuum didn't show
up either.

It may be possible that the run is short enough that it did not get
captured, as pg_stat_progress_vacuum is a snapshot of the current
point in time.

Pg version 11.4

Hard to say, but I think that you should update your binaries, at
least. 11.4 has been release in June 2019, and the latest release
available is 11.17, meaning that you are missing more than three years
worth of bug fixes. Based on the roadmap in [1]https://www.postgresql.org/developer/roadmap/ -- Michael, 11.18 should be out
next week.

[1]: https://www.postgresql.org/developer/roadmap/ -- Michael
--
Michael