本節簡單介紹了PostgreSQL的後臺程式:autovacuum,主要分析了rebuild_database_list函式的實現邏輯。

### 一、資料結構
**AutoVacuumShmem**
主要的autovacuum共享記憶體結構體,儲存在shared memory中,同時WorkerInfo也會儲存在其中.
```

/*-------------
 * The main autovacuum shmem struct.  On shared memory we store this main
 * struct and the array of WorkerInfo structs.  This struct keeps:
 * 主要的autovacuum共享記憶體結構體,儲存在shared memory中,同時WorkerInfo也會儲存在其中.
 * 該結構體包括:
 *
 * av_signal        set by other processes to indicate various conditions
 * 其他程式設定用於提示不同的條件
 * av_launcherpid   the PID of the autovacuum launcher
 * autovacuum launcher的PID
 * av_freeWorkers   the WorkerInfo freelist
 * WorkerInfo空閒連結串列
 * av_runningWorkers the WorkerInfo non-free queue
 * WorkerInfo非空閒佇列
 * av_startingWorker pointer to WorkerInfo currently being started (cleared by
 *                  the worker itself as soon as it's up and running)
 * av_startingWorker指向當前正在啟動的WorkerInfo
 * av_workItems     work item array
 * av_workItems 工作條目陣列
 *
 * This struct is protected by AutovacuumLock, except for av_signal and parts
 * of the worker list (see above).
 * 除了av_signal和worker list的一部分資訊，該資料結構透過AutovacuumLock保護
 *-------------
 */
typedef struct
{
    sig_atomic_t av_signal[AutoVacNumSignals];
    pid_t       av_launcherpid;
    dlist_head  av_freeWorkers;
    dlist_head  av_runningWorkers;
    WorkerInfo  av_startingWorker;
    AutoVacuumWorkItem av_workItems[NUM_WORKITEMS];
} AutoVacuumShmemStruct;

static AutoVacuumShmemStruct *AutoVacuumShmem;

```

**avw_dbase**
用於跟蹤worker中的資料庫的結構體
```

/* struct to keep track of databases in worker */
//用於跟蹤worker中的資料庫的結構體
typedef struct avw_dbase
{
    Oid         adw_datid;
    char       *adw_name;
    TransactionId adw_frozenxid;
    MultiXactId adw_minmulti;
    PgStat_StatDBEntry *adw_entry;
} avw_dbase;

```

### 二、原始碼解讀
rebuild_database_list用於構建出現變化後的DatabaseList，連結串列中的資料庫應出現在pgstats中，在autovacuum_naptime所設定的時間間隔範圍內均勻分佈。
比如autovacuum_naptime = 60s，有4個資料庫db1->db4，那麼每隔60s/4就會有啟動一個autovacuum worker對相應的DB進行處理。
可能的一個處理時間序列是：db1->XX（時）:XX（分）:18（秒）,db4->XX:XX:33,db4->XX:XX:48,db4->XX:XX:03
後續如需要對db1->db4進行vacuum，那麼db1->db4會在下一個18秒、33秒、48秒和03秒觸發autovacuum。
```
/*
 * Build an updated DatabaseList.  It must only contain databases that appear
 * in pgstats, and must be sorted by next_worker from highest to lowest,
 * distributed regularly across the next autovacuum_naptime interval.
 * 構建出現變化後的DatabaseList，連結串列中的資料庫應出現在pgstats中，透過next_worker從最高到最低排列，
 * 在autovacuum_naptime所設定的間隔範圍內均勻分佈。
 * 比如autovacuum_naptime = 60s，有4個資料庫db1->db4，那麼每隔60s/4就會有啟動一個autovacuum worker對相應的DB進行處理。
 * 可能的一個處理時間序列是：db1->XX:XX:18,db4->XX:XX:33,db4->XX:XX:48,db4->XX:XX:03
 *
 * Receives the Oid of the database that made this list be generated (we call
 * this the "new" database, because when the database was already present on
 * the list, we expect that this function is not called at all).  The
 * preexisting list, if any, will be used to preserve the order of the
 * databases in the autovacuum_naptime period.  The new database is put at the
 * end of the interval.  The actual values are not saved, which should not be
 * much of a problem.
 */
static void
rebuild_database_list(Oid newdb)
{
    List       *dblist;
    ListCell   *cell;
    MemoryContext newcxt;
    MemoryContext oldcxt;
    MemoryContext tmpcxt;
    HASHCTL     hctl;
    int         score;
    int         nelems;
    HTAB       *dbhash;
    dlist_iter  iter;

/* use fresh stats */
    autovac_refresh_stats();

newcxt = AllocSetContextCreate(AutovacMemCxt,
                                   "AV dblist",
                                   ALLOCSET_DEFAULT_SIZES);
    tmpcxt = AllocSetContextCreate(newcxt,
                                   "tmp AV dblist",
                                   ALLOCSET_DEFAULT_SIZES);
    oldcxt = MemoryContextSwitchTo(tmpcxt);

/*
     * Implementing this is not as simple as it sounds, because we need to put
     * the new database at the end of the list; next the databases that were
     * already on the list, and finally (at the tail of the list) all the
     * other databases that are not on the existing list.
     * 這裡的實現並沒有看上去的那麼簡單，因為需要把新資料庫放在連結串列的末尾;
     * 接下來是處理已經在連結串列上的資料庫，最後(在連結串列的末尾)是處理不在現有連結串列上的所有其他資料庫。
     *
     * To do this, we build an empty hash table of scored databases.  We will
     * start with the lowest score (zero) for the new database, then
     * increasing scores for the databases in the existing list, in order, and
     * lastly increasing scores for all databases gotten via
     * get_database_list() that are not already on the hash.
     * 為了實現這個目的，構建了一個空的雜湊表用於儲存資料庫（已加權重值）。
     * 從最低分值（0）開始，賦予新的資料庫，然後為現存在連結串列中的資料庫增加分值，
     * 繼續為不在連結串列中的資料庫增加分值。
     *
     * Then we will put all the hash elements into an array, sort the array by
     * score, and finally put the array elements into the new doubly linked
     * list.
     * 完成上述工作後，會把所有雜湊表中的元素放到陣列中，透過分值進行排序，最後把陣列元素放到新的雙向連結連結串列中。
     */
    hctl.keysize = sizeof(Oid);
    hctl.entrysize = sizeof(avl_dbase);
    hctl.hcxt = tmpcxt;
    dbhash = hash_create("db hash", 20, &hctl,  /* magic number here FIXME */
                         HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);

/* start by inserting the new database */
    score = 0;//分值從0開始
    if (OidIsValid(newdb))
    {
        avl_dbase  *db;
        PgStat_StatDBEntry *entry;

/* only consider this database if it has a pgstat entry */
        //只關注存在pgstat條目的資料庫
        entry = pgstat_fetch_stat_dbentry(newdb);
        if (entry != NULL)
        {
            /* we assume it isn't found because the hash was just created */
            db = hash_search(dbhash, &newdb, HASH_ENTER, NULL);

/* hash_search already filled in the key */
            db->adl_score = score++;
            /* next_worker is filled in later */
        }
    }

/* Now insert the databases from the existing list */
    //從現存連結串列中插入到資料庫中
    dlist_foreach(iter, &DatabaseList)
    {
        avl_dbase  *avdb = dlist_container(avl_dbase, adl_node, iter.cur);
        avl_dbase  *db;
        bool        found;
        PgStat_StatDBEntry *entry;

/*
         * skip databases with no stat entries -- in particular, this gets rid
         * of dropped databases
         * 跳過沒有統計資訊的資料庫
         */
        entry = pgstat_fetch_stat_dbentry(avdb->adl_datid);
        if (entry == NULL)
            continue;

db = hash_search(dbhash, &(avdb->adl_datid), HASH_ENTER, &found);

if (!found)
        {
            /* hash_search already filled in the key */
            db->adl_score = score++;
            /* next_worker is filled in later */
        }
    }

/* finally, insert all qualifying databases not previously inserted */
    //插入先前沒有處理過的資料庫
    dblist = get_database_list();
    foreach(cell, dblist)
    {
        avw_dbase  *avdb = lfirst(cell);
        avl_dbase  *db;
        bool        found;
        PgStat_StatDBEntry *entry;

/* only consider databases with a pgstat entry */
        //只考慮存在pgstat的資料庫
        entry = pgstat_fetch_stat_dbentry(avdb->adw_datid);
        if (entry == NULL)
            continue;

db = hash_search(dbhash, &(avdb->adw_datid), HASH_ENTER, &found);
        /* only update the score if the database was not already on the hash */
        if (!found)
        {
            /* hash_search already filled in the key */
            db->adl_score = score++;
            /* next_worker is filled in later */
        }
    }
    nelems = score;

/* from here on, the allocated memory belongs to the new list */
    MemoryContextSwitchTo(newcxt);
    dlist_init(&DatabaseList);

if (nelems > 0)
    {
        TimestampTz current_time;
        int         millis_increment;
        avl_dbase  *dbary;
        avl_dbase  *db;
        HASH_SEQ_STATUS seq;
        int         i;

/* put all the hash elements into an array */
        //放到陣列中
        dbary = palloc(nelems * sizeof(avl_dbase));

i = 0;
        hash_seq_init(&seq, dbhash);
        while ((db = hash_seq_search(&seq)) != NULL)
            memcpy(&(dbary[i++]), db, sizeof(avl_dbase));

/* sort the array */
        //排序
        qsort(dbary, nelems, sizeof(avl_dbase), db_comparator);

/*
         * Determine the time interval between databases in the schedule. If
         * we see that the configured naptime would take us to sleep times
         * lower than our min sleep time (which launcher_determine_sleep is
         * coded not to allow), silently use a larger naptime (but don't touch
         * the GUC variable).
         */
        //確定資料庫之間的排程間隔：autovacuum_naptime/資料庫個數
        millis_increment = 1000.0 * autovacuum_naptime / nelems;
        if (millis_increment <= MIN_AUTOVAC_SLEEPTIME)
            millis_increment = MIN_AUTOVAC_SLEEPTIME * 1.1;

current_time = GetCurrentTimestamp();

/*
         * move the elements from the array into the dllist, setting the
         * next_worker while walking the array
         * 把陣列中的元素移到dllist中，在遍歷陣列時設定next_worker
         */
        for (i = 0; i < nelems; i++)
        {
            avl_dbase  *db = &(dbary[i]);

current_time = TimestampTzPlusMilliseconds(current_time,
                                                       millis_increment);
            db->adl_next_worker = current_time;

/* later elements should go closer to the head of the list */
            dlist_push_head(&DatabaseList, &db->adl_node);
        }
    }

/* all done, clean up memory */
    if (DatabaseListCxt != NULL)
        MemoryContextDelete(DatabaseListCxt);
    MemoryContextDelete(tmpcxt);
    DatabaseListCxt = newcxt;
    MemoryContextSwitchTo(oldcxt);
}

```

### 三、跟蹤分析
啟動gdb,設定訊號處理,設定斷點
```
(gdb) b rebuild_database_list
Breakpoint 1 at 0x82eb2a: file autovacuum.c, line 931.
(gdb) handle SIGINT print nostop pass
SIGINT is used by the debugger.
Are you sure you want to change it? (y or n) y
Signal        Stop  Print   Pass to program Description
SIGINT        No    Yes Yes     Interrupt
(gdb) c
Continuing.
^C
Program received signal SIGINT, Interrupt.
```
進入斷點
```
Breakpoint 1, rebuild_database_list (newdb=0) at autovacuum.c:931
931     autovac_refresh_stats();
(gdb) n
933     newcxt = AllocSetContextCreate(AutovacMemCxt,
(gdb) 
936     tmpcxt = AllocSetContextCreate(newcxt,
(gdb) 
939     oldcxt = MemoryContextSwitchTo(tmpcxt);
(gdb) 
957     hctl.keysize = sizeof(Oid);
(gdb) 
958     hctl.entrysize = sizeof(avl_dbase);
(gdb) 
959     hctl.hcxt = tmpcxt;
```
檢視統計資訊檔案：pg_stat_tmp/global.stat
```
(gdb) p  *pgstat_stat_filename
$1 = 112 'p'
(gdb) p  pgstat_stat_filename
$2 = 0x203d7e0 "pg_stat_tmp/global.stat"
(gdb) n
960     dbhash = hash_create("db hash", 20, &hctl,  /* magic number here FIXME */
(gdb) 
###
[pg12@localhost pg_stat_tmp]$ pwd
/data/pgsql/pg121db/pg_stat_tmp
[pg12@localhost pg_stat_tmp]$ ll
total 4
-rw------- 1 pg12 pg12 237 Dec 11 16:40 global.stat
[pg12@localhost pg_stat_tmp]$

```
構建需處理的資料庫連結串列
```
964     score = 0;
(gdb) 
965     if (OidIsValid(newdb))
(gdb) p *hctl
Structure has no component named operator*.
(gdb) p hctl
$3 = {num_partitions = 140725872814104, ssize = 34131296, dsize = 32, max_dsize = 0, ffactor = 257, keysize = 4, entrysize = 40, hash = 0xc6afd3, match = 0x208cd60, keycopy = 0x0, alloc = 0x1, hcxt = 0x2090d80, 
  hctl = 0xfe3a00 <configurenamesstring>}
(gdb) n
984     dlist_foreach(iter, &DatabaseList)
(gdb) p *DatabaseList
Structure has no component named operator*.
(gdb) p DatabaseList
$4 = {head = {prev = 0xfd9880 <databaselist>, next = 0xfd9880 <databaselist>}}
(gdb) n
1010        dblist = get_database_list();
(gdb) 
1011        foreach(cell, dblist)
(gdb) p *dblist
$5 = {type = T_List, length = 7, head = 0x2090ef8, tail = 0x2091240}
(gdb) p *dblist->head
$6 = {data = {ptr_value = 0x2090e98, int_value = 34147992, oid_value = 34147992}, next = 0x2090fb0}
(gdb) p *(Node *)dblist->head->data.ptr_value
$7 = {type = 13591}
(gdb) p *dblist->head->data.ptr_value
Attempt to dereference a generic pointer.
(gdb) n
1013            avw_dbase  *avdb = lfirst(cell);
(gdb) 
```
如沒有統計資訊，則不予處理
```
1019            entry = pgstat_fetch_stat_dbentry(avdb->adw_datid);
(gdb) p *avdb
$8 = {adw_datid = 13591, adw_name = 0x2090ed0 "postgres", adw_frozenxid = 479, adw_minmulti = 1, adw_entry = 0x0}
(gdb) n
1020            if (entry == NULL)
(gdb) 
1011        foreach(cell, dblist)
(gdb) 
1013            avw_dbase  *avdb = lfirst(cell);
(gdb) 
1019            entry = pgstat_fetch_stat_dbentry(avdb->adw_datid);
(gdb) p *avdb
$9 = {adw_datid = 16384, adw_name = 0x2090f90 "testdb", adw_frozenxid = 2921, adw_minmulti = 1, adw_entry = 0x0}
(gdb) step
pgstat_fetch_stat_dbentry (dbid=16384) at pgstat.c:2438
2438        backend_read_statsfile();
(gdb) step
backend_read_statsfile () at pgstat.c:5644
5644        TimestampTz min_ts = 0;
(gdb) n
5645        TimestampTz ref_ts = 0;
(gdb) 
5650        if (pgStatDBHash)
(gdb) 
5651            return;
(gdb) 
5766    }
(gdb) 
pgstat_fetch_stat_dbentry (dbid=16384) at pgstat.c:2443
2443        return (PgStat_StatDBEntry *) hash_search(pgStatDBHash,
(gdb) 
2446    }
(gdb) 
rebuild_database_list (newdb=0) at autovacuum.c:1020
1020            if (entry == NULL)
(gdb) n
1011        foreach(cell, dblist)
(gdb) 
1013            avw_dbase  *avdb = lfirst(cell);
(gdb) 
1019            entry = pgstat_fetch_stat_dbentry(avdb->adw_datid);
(gdb) 
1020            if (entry == NULL)
(gdb) 
1011        foreach(cell, dblist)
(gdb) 
1013            avw_dbase  *avdb = lfirst(cell);
(gdb) 
1019            entry = pgstat_fetch_stat_dbentry(avdb->adw_datid);
(gdb) 
1020            if (entry == NULL)
(gdb) 
1011        foreach(cell, dblist)
(gdb) 
1013            avw_dbase  *avdb = lfirst(cell);
(gdb) 
1019            entry = pgstat_fetch_stat_dbentry(avdb->adw_datid);
(gdb) 
1020            if (entry == NULL)
(gdb) 
1011        foreach(cell, dblist)
(gdb) 
1013            avw_dbase  *avdb = lfirst(cell);
(gdb) 
1019            entry = pgstat_fetch_stat_dbentry(avdb->adw_datid);
(gdb) 
1020            if (entry == NULL)
(gdb) 
1011        foreach(cell, dblist)
(gdb) 
1013            avw_dbase  *avdb = lfirst(cell);
(gdb) 
1019            entry = pgstat_fetch_stat_dbentry(avdb->adw_datid);
(gdb) 
1020            if (entry == NULL)
(gdb) 
1011        foreach(cell, dblist)
(gdb) 
1032        nelems = score;
(gdb) 
1035        MemoryContextSwitchTo(newcxt);
(gdb) n
1036        dlist_init(&DatabaseList);
(gdb) 
```
所有資料庫都不需要處理，返回
```
1038        if (nelems > 0)
(gdb) p nelems
$10 = 0
(gdb) n
1089        if (DatabaseListCxt != NULL)
(gdb) 
1091        MemoryContextDelete(tmpcxt);
(gdb) 
1092        DatabaseListCxt = newcxt;
(gdb) 
1093        MemoryContextSwitchTo(oldcxt);
(gdb) 
1094    }
(gdb) 
AutoVacLauncherMain (argc=0, argv=0x0) at autovacuum.c:625
625     while (!got_SIGTERM)
(gdb)

```
DONE!

### 四、參考資料
[PG Source Code](https://doxygen.postgresql.org)</databaselist></databaselist></configurenamesstring>

PostgreSQL 原始碼解讀（237）- 後臺程式#15（rebuild_database_list）

相關文章