文章整理 - Autovacuum Tuning Basics

https://www.2ndquadrant.com/en/blog/autovacuum-tuning-basics/

最主要的手段还是修改 scale_factor 以及根据硬件配置修改 cost_limit 。单独修改 autovacuum_max_workers 只会回收的更慢。

Cleanup of dead tuples is not the only task of autovacuum. It’s also responsible for updating data distribution statistics, used by the optimizer when planning queries. You may collect those manually by running ANALYZE, but it suffers similar issues as VACUUM – you’re likely to run it either too often or not often enough.

In other words, you should have some basic monitoring in place, collecting metrics from the database. For cleanup, you need to be looking at least at these values:

• pg_stat_all_tables.n_dead_tup – number of dead tuples in each table (both user tables and system catalogs)
• (n_dead_tup / n_live_tup) – ratio of dead/live tuples in each table
• (pg_class.relpages / pg_class.reltuples) – space “per row”

Tuning Goals

• cleanup dead tuples – Keep the amount of disk space reasonably low, not to waste unreasonable amount of disk space, prevent index bloat and keep queries fast.
• minimize cleanup impact – Don’t perform cleanup too often, as it would waste resources (CPU, I/O and RAM) and might significantly hurt performance.

Thresholds and Scale Factors

the cleanup is triggered whenever the number of dead tuples (which you can see as pg_stat_all_tables.n_dead_tup) exceeds
threshold + pg_class.reltuples * scale_factor
the table will be considered as in need of cleanup. The formula basically says that up to 20% of a table may be dead tuples before it gets cleaned up (the threshold of 50 rows is there to prevent very frequent cleanups of tiny tables).
The default scale factor works fine for small and medium-sized tables, but not so much for very large tables – on 10GB table this is roughly 2GB of dead tuples, while on 1TB table it’s ~200GB.

Throttling

What we generally do is increasing the cost_limit parameter, e.g. to 1000 (or 2000), which increases the throughput by 5x (or 10x). You may of course tweak the other parameters (cost per page operation, sleep delay), but we do that only very rarely – changing the cost limit works well enough.

Number of Workers

The trouble is users assume the number of workers is proportional to the amount of cleanup that can happen. If you bump the number of autovacuum workers up to 6, it’ll surely do twice as much work compared to the default 3 workers, right?
Well, no. The cost limit, described a few paragraphs ago, is global, shared by all autovacuum workers. Each worker process only gets 1/autovacuum_max_workers of the total cost limit, so increasing the number of workers will only make them go slower.

Summary

So that’s how you tune autovacuum. If I had to sum it into a few basic rules, it’d be these five:

• Don’t disable autovacuum, unless your really know what you’re doing. Seriously.
• On busy databases (doing a lot of UPDATEs and DELETEs), particularly large ones, you should probably decrease the scale factor, so that cleanup happens more frequently.
• On reasonable hardware (good storage, multiple cores), you should probably increase the throttling parameters, so that the cleanup can keep up.
• Increasing autovacuum_max_workers alone will not really help in most cases. You’ll get more processes that go slower.
• You can set the parameters per table using ALTER TABLE, but think twice if you really need that. It makes the system more complex and more difficult to inspect.