PostgreSQL AutoVacuum 相关参数
1.###自动vacuum开关
autovacuum = on; 默认打开
2.###autovacuum运行频率
autovacuum_naptime = 60s; 默认1分钟
3.###autovacuum工作线程数
autovacuum_max_workers = 3；默认三个线程
4.###触发autovacuum执行的阈值
autovacuum_vacuum_scale_factor 默认值为0.2,
autovacuum_vacuum_scale_factor 默认值为0.2,
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操作
#表级别autovacuum阈值设置
#表级别autovacuum阈值设置
ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 2000);
#表级别autovacuum关闭
ALTER TABLE pgbench_accounts set (autovacuum_enabled = off);
#表级别autovacuum关闭
ALTER TABLE pgbench_accounts set (autovacuum_enabled = off);
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亿 逐步递增。
vacuum相关的sql查询语句
-- 查看auto vacuum 状态
select pid,state,query,age(clock_timestamp(), query_start) AS runtime from pg_stat_activity
where state = 'active' and query LIKE 'AUTOVACUUM%';
-- 查看正在执行的auto vacuum 信息
select * from pg_stat_progress_vacuum;
-- 表的死元组信息，以及auto analyze 和 auto analyze vacuum阈值
select
t.schemaname,
t.relname,
c.reltuples,
t.n_tup_ins as"inserts",
t.n_tup_upd as"updates",
t.n_tup_del as"deletes",
t.n_live_tup as"live_tuples",
t.n_dead_tup as"dead_tuples",
cast(current_setting('autovacuum_analyze_threshold') as int) + cast(current_setting('autovacuum_analyze_scale_factor') as decimal(18,3)) * c.reltuples as auto_analyze_threshold,
cast(current_setting('autovacuum_vacuum_threshold') as int)+ cast(current_setting('autovacuum_vacuum_scale_factor') as decimal(18,3))* c.reltuples as auto_vacuum_threshold
from pg_stat_user_tables t inner join pg_class c on t.relid = c."oid"
where 1=1
and t.schemaname ='public'
and t.relname ='t1';
--表的auto vacuum 历史记录
SELECT
schemaname,
relname,
last_autovacuum,
last_autoanalyze,
autovacuum_count,
autoanalyze_count
FROM
pg_stat_all_tables
WHERE
last_autovacuum IS NOT NULL
ORDER BY
last_autovacuum DESC;
-- 显示所有与冻结相关的配置
SELECT
name,
-- vacuum_freeze_min_age 事务ID达到这个年龄后可以被冻结
-- vacuum_freeze_table_age 当表的事务ID年龄超过此值时，VACUUM会执行全表扫描进行冻结
-- autovacuum_freeze_max_age 事务ID年龄达到此值时强制触发autovacuum
setting,
unit,
short_desc
FROM pg_settings
WHERE name LIKE '%freeze%' OR name LIKE '%vacuum%' OR name LIKE '%worker%'
ORDER BY name;
-- 当前事务ID
SELECT txid_current();
-- 当前最早未冻结的事务ID（年龄计算基准）
SELECT txid_current_snapshot();
-- 数据库年龄（当前事务ID与最早未冻结事务ID的差值）
SELECT datname, age(datfrozenxid) as freeze_age
FROM pg_database
ORDER BY freeze_age DESC;
-- 查看所有表的冻结年龄
SELECT
c.oid::regclass as table_name,
age(c.relfrozenxid) as freeze_age,
pg_size_pretty(pg_total_relation_size(c.oid)) as size
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
ORDER BY freeze_age DESC
LIMIT 200;
浙公网安备 33010602011771号