PostgreSQL AutoVacuum 相关参数
1.###自动vacuum开关
autovacuum = on; 默认打开
2.###autovacuum运行频率
autovacuum_naptime = 60s; 默认1分钟
3.###autovacuum工作线程数
autovacuum_max_workers = 3;默认三个线程
4.###触发autovacuum执行的阈值
auto vacuum执行的阈值(dead行大于这个值): autovacuum_vacuum_scale_factor × 表上记录数 + autovacuum_vacuum_threshold
auto analyze执行的阈值(dead行大于这个值): autovacuum_analyze_scale_factor × 表上记录数 + autovacuum_analyze_threshold
4.1##如何查看表上的死元组以及vacuum信息
SELECT u.schemaname,u.relname,c.reltuples,u.n_live_tup,u.n_mod_since_analyze,u.n_dead_tup,u.last_autoanalyze,u.last_autovacuum
FROM
pg_stat_user_tables u, pg_class c, pg_namespace n
WHERE n.oid = c.relnamespace
AND c.relname = u.relname
AND n.nspname = u.schemaname
AND u.schemaname = 'public'
AND u.relname = 't01'
5.###表级别auto_vacuum阈值定义
ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 2000);
6.###vacuum内存参数
##vacuum内存
# - Memory -
#maintenance_work_mem = 64MB # min 1MB
#autovacuum_work_mem = -1
# min 1MB, or -1 to use maintenance_work_mem
# - Cost-Based Vacuum Delay -
##当VACUUM 工作超出一定量之后,会休眠vacuum_cost_delay这么久的时间。
#vacuum_cost_delay = 0 # 0-100 milliseconds (0 disables)
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
6.1##autovacuum_work_mem
autovacuum_work_mem,默认值为 -1,即同 maintenance_work_mem。maintenance_work_mem是手动vacuum的参数, 默认值为 64MB。
6.2##autovacuum工作“量”
vacuum_cost_limit
默认200秒
6.3##autovacuum工作时长超出后休眠时间
vacuum_cost_delay
默认为0,也即不休眠
6.4##autovacuum工作量评估标准
以下是三种不同 page 的 cost,默认值分别为 1、10、20,基本不用调整。
vacuum_cost_page_hit - The estimated cost for vacuuming a buffer found in the shared buffer cache.
vacuum_cost_page_miss - The estimated cost for vacuuming a buffer that has to be read from disk.
vacuum_cost_page_dirty - The estimated cost charged when vacuum modifies a block that was previously clean.
手动VACUUM 对应的参数是 maintenance_work_mem,vacuum_cost_delay 和 vacuum_cost_limit。
AUTOVACUUM 对应的参数是 autovacuum_work_mem,autovacuum_vacuum_cost_delay,autovacuum_vacuum_cost_limit。
1,适当增大 autovacuum_work_mem 和 autovacuum_vacuum_cost_limit、减少 autovacuum_vacuum_cost_delay 可提高 AUTOVACUUM 性能。
2,手动VACUUM设置Session级别参数
SET vacuum_cost_delay = 10;
VACUUM ANALYZE pgbench_accounts;
7.###vacuum freeze表相关的参数
7.1##vacuum_freeze_min_age
默认值5000万,如果存在某个元组的事务年龄超过vacuum_freeze_min_age参数值时,就可以在vacuum时把该元组事务号冻结,也即将该元组事务id置为2
7.2##vacuum_freeze_table_age
默认值1.5亿,强制执行急切冻结阈值,
7.3##autovacuum_freeze_max_age = 200000000
默认值2亿,强制冻结新老事物id差距不能超过20亿,这个差值的大小就是autovacuum_freeze_max_age的值
7.4##vacuum_multixact_freeze_min_age
默认值5000万,这个参数用于控制多事务ID(multixact)达到多少年龄时触发VACUUM操作。
7.5##vacuum_multixact_freeze_table_age
默认值是1.5亿,
如果表的pg_class.relminmxid域超过了这个设置指定的年龄,VACUUM会执行一次激进的扫描。
激进的扫描与常规VACUUM的区别在于它会访问每一个可能包含未冻结 XID 或者 MXID 的页面,而不是只扫描那些可能包含死亡元组的页面。
7.6##autovacuum_multixact_freeze_max_age = 400000000
默认值是4亿,
指定在一个VACUUM操作被强制执行来防止表中多事务ID回卷之前,一个表的pg_class.relminmxid域能保持的最大年龄(多事务的)。注意即便自动清理被禁用,系统也将发起自动清理进程来阻止回卷。
参考https://www.modb.pro/db/1724329716294426624这里相关的参数以及事务冻结模式
冻结模式 | 触发参数 | 默认数值 | 表级别参数设置 |
---|---|---|---|
懒惰模式(Lazy mode) | vacuum_freeze_min_age | 50000000 | autovacuum_freeze_min_age and toast.autovacuum_freeze_min_age |
饥渴(侵略)模式(Eager mode) | vacuum_freeze_table_age | 150000000 | autovacuum_freeze_table_age and toast.autovacuum_freeze_table_age |
强制模式 (Forced mode) | autovacuum_freeze_max_age | 200000000 | autovacuum_freeze_max_age and toast.autovacuum_freeze_max_age |
安全保护模式(Failsafe mode) | vacuum_failsafe_age(PG14开始支持) | 1600000000 | NA |
我们可以看到 触发的参数从 5千万->1.5亿->2亿->16亿 逐步递增。