AUTOVACUUM to prevent wraparound
autovacuum_freeze_max_age 是 AUTOVACUUM 最不常用的参数，也基本不需要优化，但却是 AUTOVACUUM 最重要的一个参数，因为它与 wraparound 有关，即使 AUTOVACUUM 关闭，达到这个阈值，也会强制触发 AUTOVACUUM ，可见它有多重要。
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
首先打开 AUTOVACUUM。为了方便测试，将 autovacuum_freeze_max_age 设置为其最小值。
autovacuum_freeze_max_age = 100000
正常情况是达不到 autovacuum_freeze_max_age 阈值的，因为还没有达到这个阈值，就触发了 AUTOVACUUM。为了能测试到这个阈值，人工创建一条 idle in transaction，以阻塞正常的 AUTOVACUUM。
alvindb=# BEGIN;
BEGIN
alvindb=# SELECT txid_current();
txid_current
--------------
15200695
(1 row)
alvindb=# UPDATE alvin.tb_test_vacuum SET test_num = test_num WHERE test_num = 1;
UPDATE 1
查到 idle in transaction 如下，可以看到其 backend_xid 为 15200695：
postgres=# SELECT * FROM pg_stat_activity WHERE state = "idle in transaction";
-[ RECORD 1 ]----+------------------------------
datid | 37509
datname | alvindb
pid | 28497
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2021-11-13 23:18:33.480814+08
xact_start | 2021-11-13 23:19:45.288275+08
query_start | 2021-11-13 23:19:58.133248+08
state_change | 2021-11-13 23:19:58.133412+08
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid | 15200695
backend_xmin |
query | SELECT txid_current();
backend_type | client backend
不断更新表，使其达到阈值。
alvindb=# SELECT c.relname, c.relfrozenxid,age(c.relfrozenxid) FROM pg_class c WHERE relname = "pgbench_accounts";
relname | relfrozenxid | age
------------------+--------------+--------
pgbench_accounts | 15250696 | 100000
(1 row)
此时修改最后一条数据，使表的 age 超过阈值。
alvindb=# SELECT c.relname, age(c.relfrozenxid) FROM pg_class c WHERE relname = "pgbench_accounts";
relname | age
------------------+--------
pgbench_accounts | 100001
(1 row)
可以看到，此时已触发 aggressive vacuum to prevent wraparound。
其 query 为 VACUUM public.pgbench_accounts (to prevent wraparound)，并且 backend_xmin 为 15200695。
-[ RECORD 1 ]----+-------------------------------------------------------------------
datid | 37509
datname | alvindb
pid | 30288
usesysid |
usename |
application_name |
client_addr |
client_hostname |
client_port |
backend_start | 2021-11-13 23:30:39.767723+08
xact_start | 2021-11-13 23:30:39.788839+08
query_start | 2021-11-13 23:30:39.788839+08
state_change | 2021-11-13 23:30:39.788839+08
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 15200695
query | autovacuum: VACUUM public.pgbench_accounts (to prevent wraparound)
backend_type | autovacuum worker
从 PostgreSQL 日志中可以看到触发的 automatic aggressive vacuum。
[ 2021-11-13 23:30:40323 CST 30288 618fda1f.7650 3 6/72694 0]LOG: automatic aggressive vacuum of table "alvindb.public.pgbench_accounts": index scans: 1
pages: 0 removed, 404 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 3 removed, 100000 remain, 0 are dead but not yet removable, oldest xmin: 15200695
buffer usage: 438 hits, 480 misses, 412 dirtied
avg read rate: 28.065 MB/s, avg write rate: 24.089 MB/s
system usage: CPU: user: 0.02 s, system: 0.03 s, elapsed: 0.53 s
[ 2021-11-13 23:30:40.334 CST 30288 618fda1f.7650 4 6/72696 0]WARNING: oldest xmin is far in the past
所以，如果一旦发现 aggressive vacuum (to prevent wraparound)，需要格外重视，检查是否出现类似 idle in transaction 等异常情况。
尤其是 PostgreSQL 9.6 之前，还没有参数 idle_in_transaction_session_timeout(since PostgreSQL 9.6) 的版本，需要手动 terminate idle in transaction。或 PostgreSQL 9.6 之后，但未设置该参数。
总结
本文由浅入深，一步一步演示了如何精准触发 AUTO VACUUM，并就各个参数如何调优进行了详细的测试。性能提高同时，也注意是否出现主从延迟、CPU、load、IO、网络流量及 archive 等问题。
公众号
关注 DBA Daily 公众号，第一时间收到文章的更新。
通过一线 DBA 的日常工作，学习实用数据库技术干货！
公众号优质文章推荐
PostgreSQL VACUUM 之深入浅出
华山论剑之 PostgreSQL sequence
[PG Upgrade Series] Extract Epoch Trap
[PG Upgrade Series] Toast Dump Error
GitLab supports only PostgreSQL now
MySQL or PostgreSQL?
PostgreSQL hstore Insight
ReIndex 失败原因调查
PG 数据导入 Hive 乱码问题调查
PostGIS 扩展创建失败原因调查
原文地址：https://www.cnblogs.com/dbadaily/archive/2022/03/01/vacuum5.html
