I need to setup Autovacuum monitoring for RDS instances. Need to make sure that Autovacuuming is running fine on table specific Autovacuuming. What would be there perfect way to setup monitoring. What all parameters should be the part of monitoring.
Thanks.
This is a good query to find out if autovacuum is overdue:
SELECT t.oid::regclass AS tablename,
s.n_dead_tup AS dead_tuples,
t.reltuples AS live_tuples,
GREATEST(s.n_dead_tup::float8 - 50.0, 0) / t.reltuples AS dead_ratio
FROM pg_stat_all_tables AS s
JOIN pg_namespace AS n
ON s.schemaname = n.nspname
JOIN pg_class AS t
ON t.relname = s.relname
AND t.relnamespace = n.oid
WHERE t.reltuples <> 0
ORDER BY dead_ratio DESC;
If the last number is significantly higher than 0.2 (and you didn't change the autovacuum parameters), something is fishy.
It is not important to measure how often autovacuum runs, because it depends on the utilization of the table how often autvacuum is necessary.
There are 2 parameters that determine when the autovacuum process is triggered
autovacuum_vacuum_thresholdautovacuum_vacuum_scale_factorThe vacuum threshold is defined as:
vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples
where the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale factor is autovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples.
The autovacuum_vacuum_threshold (integer) specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples.
The autovacuum_analyze_scale_factor specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE. The default is 0.1 (10% of table size).
Let us imagine a scenario where you have not set the above two parameters and they are using the default values. Now, for example, if you have a table with ten million records, consisting of ten thousand dead tuples. This will still not trigger the autovacuum as according to the above formula, the vacuum threshold will be equal to (50+10% of ten million) which is approximately, 1 million dead tuples.
Hence, to make autovacuum more aggressive, you can set the two above parameters on table level accordingly so that it can reduce the threshold and hence trigger autovacuum faster.
Please refer to the below documentation link for more information on Autovacuum Tuning basics:
