本節簡單介紹了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 <OldestXmin>, 
    freezeLimit=0xf8814c <FreezeLimit>, xidFullScanLimit=0x7fffc5163b10, multiXactCutoff=0xf88150 <MultiXactCutoff>, 
    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