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)?
- CPU: how many CPU cores the master host has? (how big is it compared to
TODO
-
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.
Edited by Anna