PostgreSQL VACUUM 之深入浅出 (五)
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 的日常工作,学习实用数据库技术干货!
公众号优质文章推荐
[PG Upgrade Series] Extract Epoch Trap
[PG Upgrade Series] Toast Dump Error
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· 单线程的Redis速度为什么快?