F001, F008 Autovacuum: settings and resource usage

WIP / DO NOT PROCEED

Useful information

During autovacuum tuning, we need to analyze:

• current autovacuum-related settings (this report Fxxx, issue ####)
• risks to have transaction wraparound problem (separate report Fxxx, see issue ####)
• risks to have a lot of dead tuples (separate report Fxxx, see issue ####)
• bloat, of heap and indexes, at least estimated (separate report Fxxx, see issue ####)
• resources available and current utilization:
  • CPU: how many CPU cores the master host has? (how big is it compared to autovacuum_max_workers)
  • RAM: how much memory is available, how much is dedicated to shared_buffers, how much is for regular work (work_mem + max_connections) and how much is allowed to be used by autovacuum (autovacuum_work_mem / maintenance_work_mem + autovacuum_max_workers)?
  • IO: what are maximum read and write bandwidth of disks, and max IOPS (as specified by vendor and/or learned from benchmarks)? What is the current read/write IO activity (throughput in B/s and IOPS) generated by all autovacuum workers? How is it compared to "the ceilings" (how many %, roughly)?

TODO

1. Resource consumption analysis
   • Display the current setting value log_autovacuum_min_duration
   • CPU: Add observations: how many cores are there and how is autovacuum_max_workers
   • MEMORY: add calculation: what part of the memory will be spent by all available workers in the worst case (if they work at the same time).
   • DISK: POSTPONED (don't do it right now) collection of information about the capabilities of the disk subsystem and the current IO-load produced by the auto-vacuum (by logs; by other means) << Here display:warning: Warning: collection of current impact on disks is not yet implemented. Please refer to Postgres logs and see current read and write IO bandwidth caused by autovacuum.

Acceptance criteria

As a DBA, I can see the current settings related to the autovacuum and vacuum, as well as evaluate the resource consumption (CPU / RAM / disk), which will help me to draw conclusions,if can I make the settings more aggressive and how much.