PostgreSQL 11 核心優化 - 降低vacuum cleanup階段index scan機率 ( vacuum_cleanup_index_scale_factor , skip index vacuum cleanup stage)

背景

在執行vacuum時，有一個cleanup階段，以往，不管這個階段是否需要清理PAGE，隻要表上面有索引，就需要對這個表的索引全部掃描一遍。

今天，PG 11版本，增加了一個GUC參數vacuum_cleanup_index_scale_factor，以及btree索引參數vacuum_cleanup_index_scale_factor。在btree index meta page上存儲了目前表有多少條記錄（僅僅當vacuum時發現整個表沒有dead tuples（取自pg_stat_all_tables這種統計計數器）時更新meta page），當

1、((表上的 pg_stat_all_tables insert 計數器 - meta page)/meta page) 大于 vacuum_cleanup_index_scale_factor 時，vacuum cleanup階段才需要SCAN INDEX，更新INDEX stats資訊(包括meta page計數器資訊)。

或

2、有deleted pages that can be recycled during cleanup需要清理時，必定要scan index pages.

是以，對于大量INSERT，沒有UPDATE,DELETE操作的表的VACUUM，或者正常靜态表的VACUUM會快很多，因為不需要SCAN INDEX了。

背景技術

vacuum_cleanup_index_scale_factor (floating point)

Specifies the fraction of the total number of heap tuples counted in the previous statistics collection     
that can be inserted     
without incurring an index scan at the VACUUM cleanup stage.     
This setting currently applies to B-tree indexes only.    
    
If no tuples were deleted from the heap,     
B-tree indexes are still scanned at the VACUUM cleanup stage     
when at least one of the following conditions is met:     
1、the index statistics are stale,     
2、or the index contains deleted pages that can be recycled during cleanup.     
Index statistics are considered to be stale if     
the number of newly inserted tuples     
exceeds the vacuum_cleanup_index_scale_factor fraction of the total number of heap tuples detected by the previous statistics collection.     
    
The total number of heap tuples is stored in the index meta-page.     
Note that the meta-page does not include this data until VACUUM finds no dead tuples,     
so B-tree index scan at the cleanup stage can only be skipped     
if the second and subsequent VACUUM cycles detect no dead tuples.  (典型的insert only場景，或者vacuum幹掉所有dead tuple後)    
    
    
The value can range from 0 to 10000000000.     
When vacuum_cleanup_index_scale_factor is set to 0,     
index scans are never skipped during VACUUM cleanup.     
The default value is 0.1.

B-tree indexes additionally accept this parameter:

vacuum_cleanup_index_scale_factor

Per-index value for vacuum_cleanup_index_scale_factor.

相關代碼

src/backend/access/nbtree/nbtree.c

/*    
 * _bt_vacuum_needs_cleanup() -- Checks if index needs cleanup assuming that    
 *                      btbulkdelete() wasn't called.    
 */    
static bool    
_bt_vacuum_needs_cleanup(IndexVacuumInfo *info)    
{    
.... ....    
    
        {    
                StdRdOptions *relopts;    
                float8          cleanup_scale_factor;    
                float8          prev_num_heap_tuples;    
    
                /*    
                 * If table receives enough insertions and no cleanup was performed,    
                 * then index would appear have stale statistics.  If scale factor is    
                 * set, we avoid that by performing cleanup if the number of inserted    
                 * tuples exceeds vacuum_cleanup_index_scale_factor fraction of    
                 * original tuples count.    
                 */    
                relopts = (StdRdOptions *) info->index->rd_options;    
                cleanup_scale_factor = (relopts &&    
                                                                relopts->vacuum_cleanup_index_scale_factor >= 0)    
                        ? relopts->vacuum_cleanup_index_scale_factor    
                        : vacuum_cleanup_index_scale_factor;    
                prev_num_heap_tuples = metad->btm_last_cleanup_num_heap_tuples;    
    
                if (cleanup_scale_factor <= 0 ||    
                        prev_num_heap_tuples < 0 ||    
                        (info->num_heap_tuples - prev_num_heap_tuples) /    
                        prev_num_heap_tuples >= cleanup_scale_factor)     // 是否需要scan index，當判定index為stale狀态時，由計數器與vacuum_cleanup_index_scale_factor參數控制。    
                        result = true;    
        }

/*    
 * Post-VACUUM cleanup.    
 *    
 * Result: a palloc'd struct containing statistical info for VACUUM displays.    
 */    
IndexBulkDeleteResult *    
btvacuumcleanup(IndexVacuumInfo *info, IndexBulkDeleteResult *stats)    
{    
        /* No-op in ANALYZE ONLY mode */    
        if (info->analyze_only)    
                return stats;    
    
        /*    
         * If btbulkdelete was called, we need not do anything, just return the    
         * stats from the latest btbulkdelete call.  If it wasn't called, we might    
         * still need to do a pass over the index, to recycle any newly-recyclable    
         * pages or to obtain index statistics.  _bt_vacuum_needs_cleanup    
         * determines if either are needed.    
         *    
         * Since we aren't going to actually delete any leaf items, there's no    
         * need to go through all the vacuum-cycle-ID pushups.    
         */    
        if (stats == NULL)    
        {    
                TransactionId oldestBtpoXact;    
    
                /* Check if we need a cleanup */    
                if (!_bt_vacuum_needs_cleanup(info))  // 不需要scan index    
                        return NULL;    
    
                stats = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult));    
                btvacuumscan(info, stats, NULL, NULL, 0, &oldestBtpoXact);  // 需要SCAN index    
    
                /* Update cleanup-related information in the metapage */    
                _bt_update_meta_cleanup_info(info->index, oldestBtpoXact,    
                                                                         info->num_heap_tuples);    
        }    
    
        /*    
         * It's quite possible for us to be fooled by concurrent page splits into    
         * double-counting some index tuples, so disbelieve any total that exceeds    
         * the underlying heap's count ... if we know that accurately.  Otherwise    
         * this might just make matters worse.    
         */    
        if (!info->estimated_count)    
        {    
                if (stats->num_index_tuples > info->num_heap_tuples)    
                        stats->num_index_tuples = info->num_heap_tuples;    
        }    
    
        return stats;    
}

例子

tbl1，每次vacuum都要scan index

create table tbl1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int);    
create index idx_tbl1_1 on tbl1 (c1) with (vacuum_cleanup_index_scale_factor=0);    
create index idx_tbl1_2 on tbl1 (c2) with (vacuum_cleanup_index_scale_factor=0);    
create index idx_tbl1_3 on tbl1 (c3) with (vacuum_cleanup_index_scale_factor=0);    
create index idx_tbl1_4 on tbl1 (c4) with (vacuum_cleanup_index_scale_factor=0);    
create index idx_tbl1_5 on tbl1 (c5) with (vacuum_cleanup_index_scale_factor=0);    
create index idx_tbl1_6 on tbl1 (c6) with (vacuum_cleanup_index_scale_factor=0);

tbl2，當有deleted page需要recycle使用時，或者當((pg_stat_all_tables.inserted-metapage(上一次vacuum有多少條記錄))/上一次vacuum有多少條記錄) > 100000000 時，才需要scan index。

create table tbl2 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int);    
create index idx_tbl2_1 on tbl2 (c1) with (vacuum_cleanup_index_scale_factor=100000000);    
create index idx_tbl2_2 on tbl2 (c2) with (vacuum_cleanup_index_scale_factor=100000000);    
create index idx_tbl2_3 on tbl2 (c3) with (vacuum_cleanup_index_scale_factor=100000000);    
create index idx_tbl2_4 on tbl2 (c4) with (vacuum_cleanup_index_scale_factor=100000000);    
create index idx_tbl2_5 on tbl2 (c5) with (vacuum_cleanup_index_scale_factor=100000000);    
create index idx_tbl2_6 on tbl2 (c6) with (vacuum_cleanup_index_scale_factor=100000000);

分别寫入1000萬記錄

insert into tbl1 select id,id,id,id,id,id from generate_series(1,10000000) t(id);    
insert into tbl2 select id,id,id,id,id,id from generate_series(1,10000000) t(id);

觀察兩個表的二次VACUUM耗時

\timing    
    
vacuum verbose tbl1;    
vacuum verbose tbl1;    
    
vacuum verbose tbl2;    
vacuum verbose tbl2;

postgres=# vacuum verbose tbl1;    
INFO:  vacuuming "public.tbl1"    
INFO:  index "idx_tbl1_1" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_2" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_3" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_4" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_5" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_6" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  "tbl1": found 0 removable, 10000000 nonremovable row versions in 63695 out of 63695 pages    
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1137047265    
There were 0 unused item pointers.    
Skipped 0 pages due to buffer pins, 0 frozen pages.    
0 pages are entirely empty.    
CPU: user: 0.75 s, system: 0.00 s, elapsed: 0.76 s.    
VACUUM    
Time: 771.943 ms    
    
    
    
postgres=# vacuum verbose tbl1;    
INFO:  vacuuming "public.tbl1"    
INFO:  index "idx_tbl1_1" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_2" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_3" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_4" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_5" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_6" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  "tbl1": found 0 removable, 42 nonremovable row versions in 1 out of 63695 pages    
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1137047265    
There were 0 unused item pointers.    
Skipped 0 pages due to buffer pins, 0 frozen pages.    
0 pages are entirely empty.    
CPU: user: 0.13 s, system: 0.00 s, elapsed: 0.13 s.    
VACUUM    
Time: 141.759 ms    
    
    
    
postgres=# vacuum verbose tbl1;    
INFO:  vacuuming "public.tbl1"    
INFO:  index "idx_tbl1_1" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_2" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_3" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_4" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_5" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl1_6" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  "tbl1": found 0 removable, 42 nonremovable row versions in 1 out of 63695 pages    
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1137047265    
There were 0 unused item pointers.    
Skipped 0 pages due to buffer pins, 0 frozen pages.    
0 pages are entirely empty.    
CPU: user: 0.12 s, system: 0.00 s, elapsed: 0.13 s.    
VACUUM    
Time: 140.984 ms    
    
    
    
postgres=# vacuum verbose tbl2;    
INFO:  vacuuming "public.tbl2"    
INFO:  index "idx_tbl2_1" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl2_2" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl2_3" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl2_4" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl2_5" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  index "idx_tbl2_6" now contains 10000000 row versions in 27421 pages    
DETAIL:  0 index row versions were removed.    
0 index pages have been deleted, 0 are currently reusable.    
CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s.    
INFO:  "tbl2": found 0 removable, 10000000 nonremovable row versions in 63695 out of 63695 pages    
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1137047265    
There were 0 unused item pointers.    
Skipped 0 pages due to buffer pins, 0 frozen pages.    
0 pages are entirely empty.    
CPU: user: 0.84 s, system: 0.00 s, elapsed: 0.85 s.    
VACUUM    
Time: 860.749 ms    
    
    
    
postgres=# vacuum verbose tbl2;    
INFO:  vacuuming "public.tbl2"    
INFO:  "tbl2": found 0 removable, 42 nonremovable row versions in 1 out of 63695 pages    
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1137047265    
There were 0 unused item pointers.    
Skipped 0 pages due to buffer pins, 0 frozen pages.    
0 pages are entirely empty.    
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.    
VACUUM    
Time: 11.895 ms    
    
    
    
postgres=# vacuum verbose tbl2;    
INFO:  vacuuming "public.tbl2"    
INFO:  "tbl2": found 0 removable, 42 nonremovable row versions in 1 out of 63695 pages    
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1137047265    
There were 0 unused item pointers.    
Skipped 0 pages due to buffer pins, 0 frozen pages.    
0 pages are entirely empty.    
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.    
VACUUM    
Time: 11.944 ms

分析

tbl1，由于每次vacuum都需要scan index，是以更加耗時。

tbl2，由于設定了vacuum_cleanup_index_scale_factor=100000000，下一次vacuum時insert減去上一次的總記錄數再除以上一次總記錄數，小于100000000，是以不需要scan index。

耗時來看，相當明顯。

通過pageinspect，可以觀察到btree索引metapage上的table tuples總數。

postgres=# create extension pageinspect;  
CREATE EXTENSION  
  
postgres=# select * from bt_metap('idx_tbl1_1');  
 magic  | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples   
--------+---------+------+-------+----------+-----------+-------------+-------------------------  
 340322 |       3 |  412 |     2 |      412 |         2 |           0 |             9.99977e+06  
(1 row)  
  
Time: 0.345 ms  
  
postgres=# select * from bt_metap('idx_tbl2_1');  
 magic  | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples   
--------+---------+------+-------+----------+-----------+-------------+-------------------------  
 340322 |       3 |  412 |     2 |      412 |         2 |           0 |                   1e+07  
(1 row)  
  
Time: 0.429 ms

參考

https://www.postgresql.org/docs/11/sql-createindex.html https://www.postgresql.org/docs/11/runtime-config-client.html#GUC-VACUUM-CLEANUP-INDEX-SCALE-FACTOR 《PostgreSQL pg_stat_ pg_statio_ 統計資訊(scan,read,fetch,hit)源碼解讀》

PostgreSQL 11 核心優化 - 降低vacuum cleanup階段index scan機率 ( vacuum_cleanup_index_scale_factor , skip index vacuum cleanup stage)

背景

背景技術

相關代碼

例子

分析

參考

PostgreSQL 許願連結

9.9元購買3個月阿裡雲RDS PostgreSQL執行個體

PostgreSQL 解決方案集合

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述