本节简单介绍了PostgreSQL手工执行vacuum的处理流程,主要分析了ExecVacuum->vacuum->vacuum_rel->heap_vacuum_rel->vacuum_set_xid_limits函数的实现逻辑,该函数计算最老的xmin和冻结截止点。

站在用户的角度思考问题，与客户深入沟通，找到都兰网站设计与都兰网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：网站制作、做网站、企业官网、英文网站、手机端网站、网站推广、域名注册、网络空间、企业邮箱。业务覆盖都兰地区。

一、数据结构

宏定义
Vacuum和Analyze命令选项

/* ----------------------
 *      Vacuum and Analyze Statements
 *      Vacuum和Analyze命令选项
 * 
 * Even though these are nominally two statements, it's convenient to use
 * just one node type for both.  Note that at least one of VACOPT_VACUUM
 * and VACOPT_ANALYZE must be set in options.
 * 虽然在这里有两种不同的语句,但只需要使用统一的Node类型即可.
 * 注意至少VACOPT_VACUUM/VACOPT_ANALYZE在选项中设置.
 * ----------------------
 */
typedef enum VacuumOption
{
    VACOPT_VACUUM = 1 << 0,     /* do VACUUM */
    VACOPT_ANALYZE = 1 << 1,    /* do ANALYZE */
    VACOPT_VERBOSE = 1 << 2,    /* print progress info */
    VACOPT_FREEZE = 1 << 3,     /* FREEZE option */
    VACOPT_FULL = 1 << 4,       /* FULL (non-concurrent) vacuum */
    VACOPT_SKIP_LOCKED = 1 << 5,    /* skip if cannot get lock */
    VACOPT_SKIPTOAST = 1 << 6,  /* don't process the TOAST table, if any */
    VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7   /* don't skip any pages */
} VacuumOption;

二、源码解读

vacuum_set_xid_limits() — 计算最老的xmin和冻结截止点
大体逻辑如下:
1.获取最旧的XMIN(*oldestXmin)
2.计算冻结上限XID(freezeLimit)
3.计算multiXactCutoff
4.计算全表扫描上限XID(xidFullScanLimit)
5.计算mxactFullScanLimit

/*
 * vacuum_set_xid_limits() -- compute oldest-Xmin and freeze cutoff points
 * 计算最老的xmin和冻结截止点
 *
 * The output parameters are:
 * - oldestXmin is the cutoff value used to distinguish whether tuples are
 *   DEAD or RECENTLY_DEAD (see HeapTupleSatisfiesVacuum).
 * - freezeLimit is the Xid below which all Xids are replaced by
 *   FrozenTransactionId during vacuum.
 * - xidFullScanLimit (computed from table_freeze_age parameter)
 *   represents a minimum Xid value; a table whose relfrozenxid is older than
 *   this will have a full-table vacuum applied to it, to freeze tuples across
 *   the whole table.  Vacuuming a table younger than this value can use a
 *   partial scan.
 * - multiXactCutoff is the value below which all MultiXactIds are removed from
 *   Xmax.
 * - mxactFullScanLimit is a value against which a table's relminmxid value is
 *   compared to produce a full-table vacuum, as with xidFullScanLimit.
 * 输出参数:
 * - oldestXmin:用来区分元组是DEAD还是RECENTLY_DEAD的截止值(参见HeapTupleSatisfiesVacuum)。
 * - freezeLimit:在vacuum状态下所有Xid都被FrozenTransactionId替换的Xid。
 * - xidFullScanLimit(通过参数table_freeze_age计算):
 *   表示最小Xid值;relfrozenxid比这个更老的表将对其应用一个full-table vacuum，以冻结整个表中的元组。
 *   对小于此值的表进行vacuuming可以使用部分扫描。
 * - multiXactCutoff:从Xmax中删除所有multixactid的值。
 * - mxactFullScanLimit:将表的relminmxid值与xidFullScanLimit进行比较以产生full-table vacuum的值。
 *
 * xidFullScanLimit and mxactFullScanLimit can be passed as NULL if caller is
 * not interested.
 * xidFullScanLimit和mxactFullScanLimit可以传NULL值
 */
void
vacuum_set_xid_limits(Relation rel,
                      int freeze_min_age,
                      int freeze_table_age,
                      int multixact_freeze_min_age,
                      int multixact_freeze_table_age,
                      TransactionId *oldestXmin,
                      TransactionId *freezeLimit,
                      TransactionId *xidFullScanLimit,
                      MultiXactId *multiXactCutoff,
                      MultiXactId *mxactFullScanLimit)
{
    int         freezemin;
    int         mxid_freezemin;
    int         effective_multixact_freeze_max_age;
    TransactionId limit;
    TransactionId safeLimit;
    MultiXactId mxactLimit;
    MultiXactId safeMxactLimit;
    /*
     * We can always ignore processes running lazy vacuum.  This is because we
     * use these values only for deciding which tuples we must keep in the
     * tables.  Since lazy vacuum doesn't write its XID anywhere, it's safe to
     * ignore it.  In theory it could be problematic to ignore lazy vacuums in
     * a full vacuum, but keep in mind that only one vacuum process can be
     * working on a particular table at any time, and that each vacuum is
     * always an independent transaction.
     * 通常可以忽略处理正在运行的lazy vacuum.
     * 这是因为我们使用这些值仅用于确定哪些元组我们必须保留,所以可以忽略lazy vacuum.
     * 由于lazy vacuum不会记录XID,因此可以很安全的忽略之.
     * 理论上来说,在full vacuum中忽略lazy vacuum是有问题的,
     *   但请记住,在任何时候只有一个vacuum进程可以在同一张表上进行操作,
     *   并且每个vacuum始终是独立的事务.
     */
    //获取最旧的XMIN
    *oldestXmin =
        TransactionIdLimitedForOldSnapshots(GetOldestXmin(rel, PROCARRAY_FLAGS_VACUUM), rel);
    Assert(TransactionIdIsNormal(*oldestXmin));
    /*
     * Determine the minimum freeze age to use: as specified by the caller, or
     * vacuum_freeze_min_age, but in any case not more than half
     * autovacuum_freeze_max_age, so that autovacuums to prevent XID
     * wraparound won't occur too frequently.
     * 确定要使用的最小 freeze age:使用调用方所指定的值，或vacuum_freeze_min_age，
     *  但在任何情况下不超过autovacuum_freeze_max_age的一半，
     *  这样可以防止XID wraparound的autovacuums不会频繁发生。
     */
    freezemin = freeze_min_age;
    if (freezemin < 0)
        freezemin = vacuum_freeze_min_age;
    freezemin = Min(freezemin, autovacuum_freeze_max_age / 2);
    Assert(freezemin >= 0);
    /*
     * Compute the cutoff XID, being careful not to generate a "permanent" XID
     * 计算截止XID，注意不要生成“永久”XID
     */
    limit = *oldestXmin - freezemin;
    if (!TransactionIdIsNormal(limit))
        limit = FirstNormalTransactionId;
    /*
     * If oldestXmin is very far back (in practice, more than
     * autovacuum_freeze_max_age / 2 XIDs old), complain and force a minimum
     * freeze age of zero.
     * 如果oldestXmin过于久远(实践上,比autovacuum_freeze_max_age / 2 XIDs还要旧),
     *   警告并强制最小freeze age为0.
     */
    //安全上限
    safeLimit = ReadNewTransactionId() - autovacuum_freeze_max_age;
    if (!TransactionIdIsNormal(safeLimit))
        safeLimit = FirstNormalTransactionId;
    //上限比安全上限小
    if (TransactionIdPrecedes(limit, safeLimit))
    {
        ereport(WARNING,
                (errmsg("oldest xmin is far in the past"),
                 errhint("Close open transactions soon to avoid wraparound problems.\n"
                         "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
        limit = *oldestXmin;
    }
    //
    *freezeLimit = limit;
    /*
     * Compute the multixact age for which freezing is urgent.  This is
     * normally autovacuum_multixact_freeze_max_age, but may be less if we are
     * short of multixact member space.
     * 计算急需冻结的multixact age。
     * 这通常是autovacuum_multixact_freeze_max_age，
     *   但是如果欠缺multixact成员空间，那这个值可能会更小。
     */
    effective_multixact_freeze_max_age = MultiXactMemberFreezeThreshold();
    /*
     * Determine the minimum multixact freeze age to use: as specified by
     * caller, or vacuum_multixact_freeze_min_age, but in any case not more
     * than half effective_multixact_freeze_max_age, so that autovacuums to
     * prevent MultiXact wraparound won't occur too frequently.
     * 确定将要使用的最小multixact freeze age:
     *   由调用者指定或者vacuum_multixact_freeze_min_age,
     *   但无论如何不会比effective_multixact_freeze_max_age / 2大,
     *   由此保证autovacuums不会让MultiXact wraparound出现得太频繁.
     */
    mxid_freezemin = multixact_freeze_min_age;
    if (mxid_freezemin < 0)
        mxid_freezemin = vacuum_multixact_freeze_min_age;
    mxid_freezemin = Min(mxid_freezemin,
                         effective_multixact_freeze_max_age / 2);
    Assert(mxid_freezemin >= 0);
    /* compute the cutoff multi, being careful to generate a valid value */
    //计算阶段的multi,注意需要生成一个有效值
    mxactLimit = GetOldestMultiXactId() - mxid_freezemin;
    if (mxactLimit < FirstMultiXactId)
        mxactLimit = FirstMultiXactId;
    safeMxactLimit =
        ReadNextMultiXactId() - effective_multixact_freeze_max_age;
    if (safeMxactLimit < FirstMultiXactId)
        safeMxactLimit = FirstMultiXactId;
    if (MultiXactIdPrecedes(mxactLimit, safeMxactLimit))
    {
        ereport(WARNING,
                (errmsg("oldest multixact is far in the past"),
                 errhint("Close open transactions with multixacts soon to avoid wraparound problems.")));
        mxactLimit = safeMxactLimit;
    }
    *multiXactCutoff = mxactLimit;
    if (xidFullScanLimit != NULL)
    {
        int         freezetable;
        Assert(mxactFullScanLimit != NULL);
        /*
         * Determine the table freeze age to use: as specified by the caller,
         * or vacuum_freeze_table_age, but in any case not more than
         * autovacuum_freeze_max_age * 0.95, so that if you have e.g nightly
         * VACUUM schedule, the nightly VACUUM gets a chance to freeze tuples
         * before anti-wraparound autovacuum is launched.
         * 确定要使用的表冻结年龄:如调用者所指定的，或vacuum_freeze_table_age，
         *   但在任何情况下不超过autovacuum_freeze_max_age * 0.95，
         *   因此如果您有比如夜间VACUUM计划，
         *   夜间VACUUM在anti-wraparound autovacuum 启动之前有机会冻结元组。
         */
        freezetable = freeze_table_age;
        if (freezetable < 0)
            freezetable = vacuum_freeze_table_age;
        freezetable = Min(freezetable, autovacuum_freeze_max_age * 0.95);
        Assert(freezetable >= 0);
        /*
         * Compute XID limit causing a full-table vacuum, being careful not to
         * generate a "permanent" XID.
         * 计算引起full-table vacuum的XID,注意不要产生一个永久XID
         */
        limit = ReadNewTransactionId() - freezetable;
        if (!TransactionIdIsNormal(limit))
            limit = FirstNormalTransactionId;
        *xidFullScanLimit = limit;
        /*
         * Similar to the above, determine the table freeze age to use for
         * multixacts: as specified by the caller, or
         * vacuum_multixact_freeze_table_age, but in any case not more than
         * autovacuum_multixact_freeze_table_age * 0.95, so that if you have
         * e.g. nightly VACUUM schedule, the nightly VACUUM gets a chance to
         * freeze multixacts before anti-wraparound autovacuum is launched.
         * 与上述类似,为multixacts确定数据表freeze age:
         *   调用者指定或者vacuum_multixact_freeze_table_age,
         *   但不能超过autovacuum_multixact_freeze_table_age * 0.95.
         */
        freezetable = multixact_freeze_table_age;
        if (freezetable < 0)
            freezetable = vacuum_multixact_freeze_table_age;
        freezetable = Min(freezetable,
                          effective_multixact_freeze_max_age * 0.95);
        Assert(freezetable >= 0);
        /*
         * Compute MultiXact limit causing a full-table vacuum, being careful
         * to generate a valid MultiXact value.
         * 同上
         */
        mxactLimit = ReadNextMultiXactId() - freezetable;
        if (mxactLimit < FirstMultiXactId)
            mxactLimit = FirstMultiXactId;
        *mxactFullScanLimit = mxactLimit;
    }
    else
    {
        Assert(mxactFullScanLimit == NULL);
    }
}

三、跟踪分析

测试脚本

11:12:53 (xdb@[local]:5432)testdb=# vacuum t1;

启动gdb,设置断点

(gdb) b vacuum_set_xid_limits
Breakpoint 1 at 0x6ba463: file vacuum.c, line 622.
(gdb) c
Continuing.
Breakpoint 1, vacuum_set_xid_limits (rel=0x7fdb230b39a0, freeze_min_age=-1, freeze_table_age=-1, 
    multixact_freeze_min_age=-1, multixact_freeze_table_age=-1, oldestXmin=0xf88148 , 
    freezeLimit=0xf8814c , xidFullScanLimit=0x7fffc5163b10, multiXactCutoff=0xf88150 , 
    mxactFullScanLimit=0x7fffc5163b0c) at vacuum.c:622
622         TransactionIdLimitedForOldSnapshots(GetOldestXmin(rel, PROCARRAY_FLAGS_VACUUM), rel);
(gdb)

输入参数
freeze_min_age等为默认值

(gdb) p *rel
$1 = {rd_node = {spcNode = 1663, dbNode = 16402, relNode = 50820}, rd_smgr = 0x0, rd_refcnt = 1, rd_backend = -1, 
  rd_islocaltemp = false, rd_isnailed = false, rd_isvalid = true, rd_indexvalid = 0 '\000', rd_statvalid = false, 
  rd_createSubid = 0, rd_newRelfilenodeSubid = 0, rd_rel = 0x7fdb230b3bb8, rd_att = 0x7fdb230b3cd0, rd_id = 50820, 
  rd_lockInfo = {lockRelId = {relId = 50820, dbId = 16402}}, rd_rules = 0x0, rd_rulescxt = 0x0, trigdesc = 0x0, 
  rd_rsdesc = 0x0, rd_fkeylist = 0x0, rd_fkeyvalid = false, rd_partkeycxt = 0x0, rd_partkey = 0x0, rd_pdcxt = 0x0, 
  rd_partdesc = 0x0, rd_partcheck = 0x0, rd_indexlist = 0x0, rd_oidindex = 0, rd_pkindex = 0, rd_replidindex = 0, 
  rd_statlist = 0x0, rd_indexattr = 0x0, rd_projindexattr = 0x0, rd_keyattr = 0x0, rd_pkattr = 0x0, rd_idattr = 0x0, 
  rd_projidx = 0x0, rd_pubactions = 0x0, rd_options = 0x0, rd_index = 0x0, rd_indextuple = 0x0, rd_amhandler = 0, 
  rd_indexcxt = 0x0, rd_amroutine = 0x0, rd_opfamily = 0x0, rd_opcintype = 0x0, rd_support = 0x0, rd_supportinfo = 0x0, 
  rd_indoption = 0x0, rd_indexprs = 0x0, rd_indpred = 0x0, rd_exclops = 0x0, rd_exclprocs = 0x0, rd_exclstrats = 0x0, 
  rd_amcache = 0x0, rd_indcollation = 0x0, rd_fdwroutine = 0x0, rd_toastoid = 0, pgstat_info = 0x28dc030}
(gdb)

获取最旧的XMIN(*oldestXmin)

(gdb) n
621     *oldestXmin =
(gdb) 
624     Assert(TransactionIdIsNormal(*oldestXmin));
(gdb) p *oldestXmin
$2 = 315793
(gdb)

计算冻结上限XID(freezeLimit)

(gdb) n
632     freezemin = freeze_min_age;
(gdb) 
633     if (freezemin < 0)
(gdb) p freezemin
$3 = -1
(gdb) n
634         freezemin = vacuum_freeze_min_age;
(gdb) 
635     freezemin = Min(freezemin, autovacuum_freeze_max_age / 2);
(gdb) p vacuum_freeze_min_age --> 默认值为五千万/50,000,000
$4 = 50000000
(gdb) p autovacuum_freeze_max_age --> 默认值为两亿/200,000,000
$5 = 200000000
(gdb) n
636     Assert(freezemin >= 0);
(gdb) p freezemin
$6 = 50000000
(gdb) n

A.limit = *oldestXmin - freezemin
B.获取新的事务号,判断oldestXmin是否年代久远(长期未提交的事务)
C.判断limit是否在saftlimit之前,比较方法是:
int32(limit - safeLimit) < 0? 即 int32(4245283089 - 4095283090) < 0?该断言为F,不成立.

641     limit = *oldestXmin - freezemin; --> 上限 = *oldestXmin - freezemin
(gdb) 
642     if (!TransactionIdIsNormal(limit))
(gdb) p limit
$7 = 4245283089
(gdb) n
650     safeLimit = ReadNewTransactionId() - autovacuum_freeze_max_age;
(gdb) p ReadNewTransactionId() --> 新的事务号,判断oldestXmin是否年代久远(长期未提交的事务)
$8 = 315794
(gdb) n
651     if (!TransactionIdIsNormal(safeLimit))
(gdb) p safeLimit
$9 = 4095283090
(gdb) n
654     if (TransactionIdPrecedes(limit, safeLimit)) 
(gdb) 
663     *freezeLimit = limit;
(gdb) 
670     effective_multixact_freeze_max_age = MultiXactMemberFreezeThreshold();
(gdb)

计算multiXactCutoff

(gdb) 
678     mxid_freezemin = multixact_freeze_min_age;
(gdb) 
679     if (mxid_freezemin < 0)
(gdb) 
680         mxid_freezemin = vacuum_multixact_freeze_min_age;
(gdb) 
681     mxid_freezemin = Min(mxid_freezemin,
(gdb) 
683     Assert(mxid_freezemin >= 0);
(gdb) 
686     mxactLimit = GetOldestMultiXactId() - mxid_freezemin;
(gdb) 
687     if (mxactLimit < FirstMultiXactId)
(gdb) 
691         ReadNextMultiXactId() - effective_multixact_freeze_max_age;
(gdb) 
690     safeMxactLimit =
(gdb) 
692     if (safeMxactLimit < FirstMultiXactId)
(gdb) 
695     if (MultiXactIdPrecedes(mxactLimit, safeMxactLimit))
(gdb) 
703     *multiXactCutoff = mxactLimit;
(gdb) 
705     if (xidFullScanLimit != NULL)
(gdb)

计算全表扫描上限XID(xidFullScanLimit)

(gdb) 
709         Assert(mxactFullScanLimit != NULL);
(gdb) 
718         freezetable = freeze_table_age;
(gdb) 
719         if (freezetable < 0)
(gdb) p freezetable
$10 = -1
(gdb) n
720             freezetable = vacuum_freeze_table_age;
(gdb) 
721         freezetable = Min(freezetable, autovacuum_freeze_max_age * 0.95);
(gdb) p vacuum_freeze_table_age
$11 = 150000000
(gdb) p autovacuum_freeze_max_age * 0.95
$12 = 189999999.99999997
(gdb) n
722         Assert(freezetable >= 0);
(gdb) 
728         limit = ReadNewTransactionId() - freezetable;
(gdb) 
729         if (!TransactionIdIsNormal(limit))
(gdb) p limit
$13 = 4145283090
(gdb) p freezetable
$14 = 150000000
(gdb) n
732         *xidFullScanLimit = limit;
(gdb) 
742         freezetable = multixact_freeze_table_age;
(gdb)

计算mxactFullScanLimit

(gdb) n
743         if (freezetable < 0)
(gdb) 
744             freezetable = vacuum_multixact_freeze_table_age;
(gdb) 
745         freezetable = Min(freezetable,
(gdb) 
747         Assert(freezetable >= 0);
(gdb) 
753         mxactLimit = ReadNextMultiXactId() - freezetable;
(gdb) 
754         if (mxactLimit < FirstMultiXactId)
(gdb) 
757         *mxactFullScanLimit = mxactLimit;
(gdb) 
763 }
(gdb) p *mxactFullScanLimit
$15 = 4144967297
(gdb)

完成调用

(gdb) n
lazy_vacuum_rel (onerel=0x7fdb230b39a0, options=1, params=0x7fffc5163e60, bstrategy=0x292b708) at vacuumlazy.c:245
245     aggressive = TransactionIdPrecedesOrEquals(onerel->rd_rel->relfrozenxid,
(gdb)

DONE!

四、参考资料

PG Source Code