ReaderMX.
PostgreSQL学徒

修改源代码，优化 Autovacuum 触发条件

PImongo约 19 分钟

前言

此文章为读者投稿 (欢迎各位读者投稿👏🏻)，Author：邱神医，数据库里最懂医学，医学里最懂数据库，被数据库耽搁的神医。

PostgreSQL 的 MVCC 实现方式就像信用卡，删除、更新和回滚留下的"债务"需要去偿还，偿还的动作便是由 vacuum / autovacuum 来做的，如果不偿还就会导致破产。

autovacuum 的触发机制有几个参数控制

• autovacuum_vacuum_threshold 和 toast.autovacuum_vacuum_threshold • autovacuum_vacuum_scale_factor 和 toast.autovacuum_vacuum_scale_factor

今天这篇文章由邱神医投稿，演示了如何修改源代码以调整触发 autovacuum 的算法。

当前计算方法是基于线性增长的阈值对大表并不友好。设想一个包含数亿行的大表，其清理阈值将随着表行数增加而增大，导致自动清理触发的时间过长。这不仅影响了存储空间的利用率，还使查询优化器无法得到及时更新的统计信息进行优化，进而影响数据库的查询性能。

正文

PostgreSQL 的 MVCC 机制 (这段偷懒，引用 cc 的文章）

1. 插入是将元组插入到页面的空闲空间中； 2. 删除则是直接将元组标记为旧版本，即使这个旧版本对所有事务都不可见了，这个元组占用的空间也不会归还给文件系统 3. UPDATE相当于DELETE + INSERT，等于是占用了两条元组的位置，类似DELETE，旧版本的元组依然占用着物理空间。

表经过增删改操作之后，页面上的旧版本元组势必是占有一定比重的。这就导致了物理文件大小明显高于实际的数据量。

为此，PostgreSQL 引入了 vacuum 的机制，去清理那些不再需要的死元组，在 8.1 版本之后引入了 autovacuum，到目前为止，现有的代码触发 autovacuum 计算公式：

1. vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples; 2. anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;

通过画出函数图像，可以得知是一次函数图像，当前计算方法是基于线性增长的阈值对大表并不友好。设想一个包含数亿行的大表，其清理阈值将随着表行数增加而增大，导致自动清理触发的时间过长。这不仅影响了存储空间的利用率，还使查询优化器无法得到及时更新的统计信息进行优化，进而影响数据库的查询性能。

在我们中学阶段，就学习了几个函数，算术平方根，对数函数，幂函数，我们以幂函数来看，在使用幂次公式的情况下，阈值在表行数较小时与线性模型接近，但随着表行数的进一步增加，幂次增长公式的阈值增长速度减缓。对大表来说，这意味着清理和分析操作会更频繁地触发。通过计算公式可以推算实际数据库中的大表更新，我们发现使用幂次公式后，数据库的大表能够更及时地进行清理操作，避免了表膨胀现象。同时，统计信息的更新也更加及时，优化器在查询时能够基于最新的统计信息做出更好的查询计划，从而提升整体查询性能。

源码实现

我们查看 autovacuum.c ，总共 3041 行，现有的计算方法是一次函数，我们直接去修改计算公式，PG 社区老顽固肯定不会同意，你这么一改，回不到原来的行为，而且这么一改，只有一种算法，不符合成年人的选择，上面计算公式我全都要，那么我们就需要引入一个参数，可以选择其中一个计算方法： 
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
首先我们要在 guc_tables.c 新增一个参数，新增参数最好是枚举类型，防止瞎填导致出现问题，通过 guc.c 源码，我们可以知道 guc 参数有这些，我们在 guc_tables.c 源码文件中添加我们新的参数 
/* * Detect whether an "extra" struct is referenced anywhere in a GUC item */static boolextra_field_used(struct config_generic *gconf, void *extra){    GucStack   *stack;    if (extra == gconf->extra)        return true;    switch (gconf->vartype)    {        case PGC_BOOL:            if (extra == ((struct config_bool *) gconf)->reset_extra)                return true;            break;        case PGC_INT:            if (extra == ((struct config_int *) gconf)->reset_extra)                return true;            break;        case PGC_REAL:            if (extra == ((struct config_real *) gconf)->reset_extra)                return true;            break;        case PGC_STRING:            if (extra == ((struct config_string *) gconf)->reset_extra)                return true;            break;        case PGC_ENUM:            if (extra == ((struct config_enum *) gconf)->reset_extra)                return true;            break;    }    for (stack = gconf->stack; stack; stack = stack->prev)    {        if (extra == stack->prior.extra ||            extra == stack->masked.extra)            return true;    }    return false;}/* * Support for assigning to an "extra" field of a GUC item.  Free the prior * value if it's not referenced anywhere else in the item (including stacked * states). */

在 struct config_enum ConfigureNamesEnum[] = 里面新增一个参数叫 autovacuum_algorithm 

    /* add guc for autovacuum algorithm*/    {        {"autovacuum_algorithm", PGC_SIGHUP, AUTOVACUUM,            gettext_noop("vacuum trigger algorithm."),            NULL        },        &autovacuum_algorithm,        AUTOVACUUM_ALGORITHM_LINEAR, autovacuum_algorithm_options,        NULL, NULL, NULL    },        /* End-of-list marker */    {        {NULL, 0, 0, NULL, NULL}, NULL, 0, NULL, NULL, NULL, NULL    }
同时我们要在 guc_tables.c 添加一个枚举类型： 
static const struct config_enum_entry wal_compression_options[] = {    {"pglz", WAL_COMPRESSION_PGLZ, false},#ifdef USE_LZ4    {"lz4", WAL_COMPRESSION_LZ4, false},#endif#ifdef USE_ZSTD    {"zstd", WAL_COMPRESSION_ZSTD, false},#endif    {"on", WAL_COMPRESSION_PGLZ, false},    {"off", WAL_COMPRESSION_NONE, false},    {"true", WAL_COMPRESSION_PGLZ, true},    {"false", WAL_COMPRESSION_NONE, true},    {"yes", WAL_COMPRESSION_PGLZ, true},    {"no", WAL_COMPRESSION_NONE, true},    {"1", WAL_COMPRESSION_PGLZ, true},    {"0", WAL_COMPRESSION_NONE, true},    {NULL, 0, false}};/* vacuum_algorithm guc */static const struct config_enum_entry autovacuum_algorithm_options[] = {    {"log2", AUTOVACUUM_ALGORITHM_LOG2, false},    {"sqrt", AUTOVACUUM_ALGORITHM_SQRT, false},    {"linear", AUTOVACUUM_ALGORITHM_LINEAR, false},    {"pow", AUTOVACUUM_ALGORITHM_POW, false},    {NULL, 0, false}};
在参数层面我们已经成功添加了， autovacuum.h 去定义新增参数 
/*  algorithms for Vacuum */typedef enum Autovacuum_algorithm{    AUTOVACUUM_ALGORITHM_LINEAR,    AUTOVACUUM_ALGORITHM_LOG2,    AUTOVACUUM_ALGORITHM_SQRT,    AUTOVACUUM_ALGORITHM_POW} Autovacuum_algorithm;extern PGDLLIMPORT int autovacuum_algorithm;/* Status inquiry functions */extern bool AutoVacuumingActive(void);
在 autovacuum.c 定义一个全局变量，改原有计算公式。vacinsthresh 因为是优化插入数据触发收集统计信息，保留原来算法更合适 
int         Log_autovacuum_min_duration = 600000;/* guc parameter of autovacuum_algorithm */int         autovacuum_algorithm = AUTOVACUUM_ALGORITHM_LINEAR;/* the minimum allowed time between two awakenings of the launcher */#define MIN_AUTOVAC_SLEEPTIME 100.0 /* milliseconds */#define MAX_AUTOVAC_SLEEPTIME 300   /* seconds *//* If the table hasn't yet been vacuumed, take reltuples as zero */        if (reltuples < 0)            reltuples = 0;        switch (autovacuum_algorithm)        {            case AUTOVACUUM_ALGORITHM_LOG2:                vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples), vac_base_thresh + vac_scale_factor * log2(reltuples) * 100000.0 );                anlthresh = (float4) fmin(anl_base_thresh + (anl_scale_factor * reltuples), anl_base_thresh + anl_scale_factor * log2(reltuples) * 100000.0 );                elog(DEBUG2, "Using log2 algorithm for vacuum,vacthresh values is %f,anlthresh values is %f",vacthresh,anlthresh);                break;            case AUTOVACUUM_ALGORITHM_SQRT:                vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples), vac_base_thresh + vac_scale_factor * sqrt(reltuples) * 1000.0 );                anlthresh = (float4) fmin(anl_base_thresh + (anl_scale_factor * reltuples), vac_base_thresh + anl_scale_factor * sqrt(reltuples) * 1000.0 );                elog(DEBUG2, "Using sqrt algorithm for vacuum,vacthresh values is %f,anlthresh values is %f",vacthresh,anlthresh);                break;            case AUTOVACUUM_ALGORITHM_LINEAR:                vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;                anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;                elog(DEBUG2, "Using linear algorithm for vacuum,vacthresh values is %f,anlthresh values is %f",vacthresh,anlthresh);                break;            case AUTOVACUUM_ALGORITHM_POW:                vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples), vac_base_thresh + vac_scale_factor * pow(reltuples,0.7) * 100.0 );                anlthresh = (float4) fmin(anl_base_thresh + (anl_scale_factor * reltuples), vac_base_thresh + anl_scale_factor * pow(reltuples,0.7) * 100.0 );                elog(DEBUG2, "Using pow algorithm for vacuum,vacthresh values is %f,anlthresh values is %f",vacthresh,anlthresh);                break;        default:            elog(ERROR, "Unknown vacuum_algorithm value.");        }        vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;        /*         * Note that we don't need to take special consideration for stat         * reset, because if that happens, the last vacuum and analyze counts         * will be reset too.         */

最后我们要去修改一下配置文件模板，添加我们新增参数 

#autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for                    # autovacuum, -1 means use                    # vacuum_cost_limit#autovacuum_algorithm = linear # autovacuum_algorithm for (log2,sqrt,linear,pow) default linear

编译代码，初始化库之后，我们尝试设置为算术平方根。 

postgres=# show autovacuum_algorithm ; autovacuum_algorithm---------------------- linear(1 row)postgres=# alter system set autovacuum_algorithm TO sqrt;ALTER SYSTEMpostgres=# select pg_reload_conf(); pg_reload_conf---------------- t(1 row)postgres=# show autovacuum_algorithm ; autovacuum_algorithm---------------------- sqrt(1 row)

仅仅有这个参数，那么我们怎么知道我们改的逻辑正确的呢 — 在代码里面加了 debug2 日志 

postgres=# show log_min_messages ; log_min_messages------------------ warning(1 row)postgres=# alter system set log_min_messages TO debug2 ;ALTER SYSTEMpostgres=# select pg_reload_conf(); pg_reload_conf---------------- t(1 row)postgres=# show log_min_messages ; log_min_messages------------------ debug2(1 row)

从日志来看，我们新增的算法是生效的 

2024-11-26 18:18:04.233 +08,,,205680,,6745a05c.32370,92,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.233 +08,,,205680,,6745a05c.32370,93,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.233 +08,,,205680,,6745a05c.32370,94,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,95,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,96,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,97,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,98,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,99,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,100,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,101,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,102,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,103,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,104,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,105,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,106,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,107,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,108,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,02024-11-26 18:18:04.234 +08,,,205680,,6745a05c.32370,109,,2024-11-26 18:18:04 +08,1005/44261,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 50.000000,anlthresh values is 50.000000",,,,,,,,,"","autovacuum worker",,0

验证

让我们新建一个表并插入一千万行记录，验证一下： 

sysbench=# CREATE TABLE t11(id bigserial NOT NULL primary key,cid text);CREATE TABLEsysbench=# insert into t11 SELECT generate_series(1,10000000) as id,md5(random()::text);INSERT 0 10000000# INSERT 同样会触发统计信息更新sysbench=# select * from pg_stat_user_tables where relname='t11';-[ RECORD 1 ]-------+------------------------------relid               | 16428schemaname          | publicrelname             | t11seq_scan            | 1last_seq_scan       | 2024-11-27 09:44:44.605001+08seq_tup_read        | 0idx_scan            | 0last_idx_scan       |idx_tup_fetch       | 0n_tup_ins           | 10000000n_tup_upd           | 0n_tup_del           | 0n_tup_hot_upd       | 0n_tup_newpage_upd   | 0n_live_tup          | 10000006n_dead_tup          | 0n_mod_since_analyze | 0n_ins_since_vacuum  | 0last_vacuum         |last_autovacuum     | 2024-11-27 09:49:14.562558+08last_analyze        |last_autoanalyze    | 2024-11-27 09:49:14.795829+08vacuum_count        | 0autovacuum_count    | 1analyze_count       | 0autoanalyze_count   | 1

通过计算，如果选用算术平方根算法，需要更新这么多行数据就会触发 vacuum 

sysbench=# select 50+sqrt(10000000)*1000*0.2;-[ RECORD 1 ]---------------?column? | 632505.5320336759sysbench=# update t11 set cid=md5(random()::text) where id<=632500;UPDATE 632500sysbench=# select * from pg_stat_user_tables where relname='t11';-[ RECORD 1 ]-------+------------------------------relid               | 16428schemaname          | publicrelname             | t11seq_scan            | 1last_seq_scan       | 2024-11-27 09:44:44.605001+08seq_tup_read        | 0idx_scan            | 1last_idx_scan       | 2024-11-27 09:52:49.728153+08idx_tup_fetch       | 632500n_tup_ins           | 10000000n_tup_upd           | 632500n_tup_del           | 0n_tup_hot_upd       | 0n_tup_newpage_upd   | 632500n_live_tup          | 10000006n_dead_tup          | 632500n_mod_since_analyze | 632500n_ins_since_vacuum  | 0last_vacuum         |last_autovacuum     | 2024-11-27 09:49:14.562558+08last_analyze        |last_autoanalyze    | 2024-11-27 09:49:14.795829+08vacuum_count        | 0autovacuum_count    | 1analyze_count       | 0autoanalyze_count   | 1# 再更新50行，达到触发阈值sysbench=# update t11 set cid=md5(random()::text) where id>=632500 and id<=632500+20;UPDATE 21sysbench=# select * from pg_stat_user_tables where relname='t11';-[ RECORD 1 ]-------+------------------------------relid               | 16428schemaname          | publicrelname             | t11seq_scan            | 2last_seq_scan       | 2024-11-27 09:53:27.640239+08seq_tup_read        | 1286710idx_scan            | 2last_idx_scan       | 2024-11-27 09:53:59.004808+08idx_tup_fetch       | 632521n_tup_ins           | 10000000n_tup_upd           | 1919212n_tup_del           | 0n_tup_hot_upd       | 42n_tup_newpage_upd   | 1919170n_live_tup          | 10005976n_dead_tup          | 1913304n_mod_since_analyze | 21n_ins_since_vacuum  | 0last_vacuum         |last_autovacuum     | 2024-11-27 09:49:14.562558+08last_analyze        |last_autoanalyze    | 2024-11-27 09:53:14.043256+08vacuum_count        | 0autovacuum_count    | 1analyze_count       | 0autoanalyze_count   | 2sysbench=# select * from pg_stat_user_tables where relname='t11';        ---触发了autovacuum-[ RECORD 1 ]-------+------------------------------relid               | 16428schemaname          | publicrelname             | t11seq_scan            | 2last_seq_scan       | 2024-11-27 09:53:27.640239+08seq_tup_read        | 1286710idx_scan            | 2last_idx_scan       | 2024-11-27 09:53:59.004808+08idx_tup_fetch       | 632521n_tup_ins           | 10000000n_tup_upd           | 1919212n_tup_del           | 0n_tup_hot_upd       | 42n_tup_newpage_upd   | 1919170n_live_tup          | 9523781n_dead_tup          | 0n_mod_since_analyze | 21n_ins_since_vacuum  | 0last_vacuum         |last_autovacuum     | 2024-11-27 09:54:14.942846+08last_analyze        |last_autoanalyze    | 2024-11-27 09:53:14.043256+08vacuum_count        | 0autovacuum_count    | 2analyze_count       | 0autoanalyze_count   | 2

如果原有算法，触发 vacuum 和收集统计信息需要更新这么多行数据 

sysbench=# select 50+0.2*10000000;-[ RECORD 1 ]-------?column? | 2000050.0sysbench=# select 50+0.1*10000000;-[ RECORD 1 ]-------?column? | 1000050.0

在日志文件里面，也可以得到验证，现在就看社区大佬什么意见了。 

2024-11-27 09:54:13.751 +08,,,318543,,67467bc5.4dc4f,8,,2024-11-27 09:54:13 +08,1001/60,0,DEBUG,00000,"the relname is t11 has 10005976.000000 reltuples ",,,,,,,,,"","autovacuum worker",,02024-11-27 09:54:13.751 +08,,,318543,,67467bc5.4dc4f,9,,2024-11-27 09:54:13 +08,1001/60,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 632694.500000,anlthresh values is 316372.250000, the t11 has 10005976.000000 reltuples",,,,,,,,,"","autovacuum worker",,02024-11-27 09:54:13.752 +08,,,318543,,67467bc5.4dc4f,108,,2024-11-27 09:54:13 +08,1001/60,0,DEBUG,00000,"the relname is t11 has 10005976.000000 reltuples ",,,,,,,,,"","autovacuum worker",,02024-11-27 09:54:13.752 +08,,,318543,,67467bc5.4dc4f,109,,2024-11-27 09:54:13 +08,1001/60,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 632694.500000,anlthresh values is 316372.250000, the t11 has 10005976.000000 reltuples",,,,,,,,,"","autovacuum worker",,02024-11-27 09:54:14.892 +08,,,318543,,67467bc5.4dc4f,112,,2024-11-27 09:54:13 +08,1001/61,0,DEBUG,00000,"scanned index ""t11_pkey"" to remove 1919170 row versions",,,,,"while vacuuming index ""t11_pkey"" of relation ""public.t11""",,,,"","autovacuum worker",,02024-11-27 09:54:14.942 +08,,,318543,,67467bc5.4dc4f,113,,2024-11-27 09:54:13 +08,1001/61,0,DEBUG,00000,"table ""t11"": removed 1919170 dead item identifiers in 17938 pages",,,,,"while vacuuming relation ""public.t11""",,,,"","autovacuum worker",,02024-11-27 09:54:14.942 +08,,,318543,,67467bc5.4dc4f,114,,2024-11-27 09:54:13 +08,1001/61,0,DEBUG,00000,"index ""t11_pkey"" now contains 10000000 row versions in 32683 pages","1919170 index row versions were removed.0 index pages are currently deleted, of which 0 are currently reusable.",,,,"while cleaning up index ""t11_pkey"" of relation ""public.t11""",,,,"","autovacuum worker",,02024-11-27 09:55:13.770 +08,,,318605,,67467c01.4dc8d,8,,2024-11-27 09:55:13 +08,1002/67,0,DEBUG,00000,"the relname is t11 has 9523781.000000 reltuples ",,,,,,,,,"","autovacuum worker",,02024-11-27 09:55:13.770 +08,,,318605,,67467c01.4dc8d,9,,2024-11-27 09:55:13 +08,1002/67,0,DEBUG,00000,"Using sqrt algorithm for vacuum, vacthresh values is 617262.500000,anlthresh values is 308656.250000, the t11 has 9523781.000000 reltuples",,,,,,,,,"","autovacuum worker",,0

至于日志里面多出来的表名字，是我为了debug 加的，实现也很简单，具体代码如下 

if (PointerIsValid(tabentry) && AutoVacuumingActive()) {                              reltuples = classForm->reltuples;                vactuples = tabentry->dead_tuples;                instuples = tabentry->ins_since_vacuum;                anltuples = tabentry->mod_since_analyze;                char relname_str[NAMEDATALEN];                strncpy(relname_str,  classForm->relname.data, NAMEDATALEN);                relname_str[NAMEDATALEN - 1] = '\0';                elog(DEBUG2, "the relname is %s has %f reltuples ", relname_str, reltuples);                /* If the table hasn't yet been vacuumed, take reltuples as zero */                if (reltuples < 0)                        reltuples = 0;                switch (autovacuum_algorithm)                {                        case AUTOVACUUM_ALGORITHM_LOG2:                                vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples), vac_base_thresh + vac_scale_factor * log2(reltuples) * 10000.0 );                                anlthresh = (float4) fmin(anl_base_thresh + (anl_scale_factor * reltuples), anl_base_thresh + anl_scale_factor * log2(reltuples) * 10000.0 );                                elog(DEBUG2, "Using log2 algorithm for vacuum, vacthresh values is %f,anlthresh values is %f, the %s has %f reltuples " ,vacthresh, anlthresh, relname_str , reltuples );                                break;                        case AUTOVACUUM_ALGORITHM_SQRT:                                vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples), vac_base_thresh + vac_scale_factor * sqrt(reltuples) * 1000.0 );                                anlthresh = (float4) fmin(anl_base_thresh + (anl_scale_factor * reltuples), anl_base_thresh + anl_scale_factor * sqrt(reltuples) * 1000.0 );                                elog(DEBUG2, "Using sqrt algorithm for vacuum, vacthresh values is %f,anlthresh values is %f, the %s has %f reltuples" , vacthresh, anlthresh, relname_str, reltuples );                                break;                        case AUTOVACUUM_ALGORITHM_LINEAR:                                vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;                                anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;                                elog(DEBUG2, "Using linear algorithm for vacuum, vacthresh values is %f,anlthresh values is %f, the %s has %f reltuples", vacthresh, anlthresh, relname_str , reltuples );                                break;                        case AUTOVACUUM_ALGORITHM_POW:                                vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples), vac_base_thresh + vac_scale_factor * pow(reltuples, 0.7) * 100.0 );                                anlthresh = (float4) fmin(anl_base_thresh + (anl_scale_factor * reltuples), anl_base_thresh + anl_scale_factor * pow(reltuples, 0.7) * 100.0 );                                elog(DEBUG2, "Using pow algorithm for vacuum, vacthresh values is %f,anlthresh values is %f, the %s has %f reltuples", vacthresh, anlthresh, relname_str, reltuples);                                break;                        default:                                elog(ERROR, "Unknown vacuum_algorithm value.");                                break;                }                vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;

老熊有话说

autovacuum 的调优一直是一门大学问，说白了，autovacuum 只是根据相关配置参数自动运行 VACUUM/ANALYZE 的后台进程，因此，它也可能会因为各种各样的原因失败、阻塞、忙等，需对症下药。

• Slow or Stuck . VACUUM runs for a very long time on a table, maybe forever，比如较慢的磁盘，锁阻塞、配置参数过于保守等等 • Spinning . Same VACUUM is getting retried over and over again, either because either because it’s failing with an error, or because it’s succeeding but nothing useful is happening，比如2PC、复制槽和长事务等等，也可能因为报错反复重启，就好比 logical replication worker • Skipped . Autovacuum doesn’t think that VACUUM is required, but actually it is，没有达到触发阈值，或者没有足够的工作进程等等。 • Starvation . Autovacuum is too busy and so some tables are not getting checked. 希望邱神医这篇文章能够给各位读者带来些许思路。

KEEP THE THREAD GOING

更多来源文章