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 -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_memmaintenance_work_mem是手动vacuum的参数 默认值为 64MB
    6.2##autovacuum工作
        vacuum_cost_limit
        默认200秒
    6.3##autovacuum工作时长超出后休眠时间
        vacuum_cost_delay
        默认为0也即不休眠
    6.4##autovacuum工作量评估标准
        以下是三种不同 page 的 cost默认值分别为 11020基本不用调整
        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_memvacuum_cost_delay 和 vacuum_cost_limit
    AUTOVACUUM 对应的参数是 autovacuum_work_memautovacuum_vacuum_cost_delayautovacuum_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万这个参数用于控制多事务IDmultixact达到多少年龄时触发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亿 逐步递增。

来源: https://www.modb.pro/db/1724329716294426624