PostgreSQL10GIN索引鎖優化

德哥發表於2017-07-05

SQL索引優化

背景

PostgreSQL gin索引介面常被用於多值列的檢索，例如全文檢索型別、陣列型別。

有興趣瞭解更多索引介面的原理和使用場景，可以參考下文。

《PostgreSQL 9種索引的原理和應用場景》

今天要說道一下PostgreSQL GIN索引的程式碼優化。

在說GIN程式碼優化前，我們先來看一個場景，以及在老版本下的效能表現。

例子

建立一張測試表，三個欄位，其中一個全文檢索欄位，另一個PK，還有一個時間。

全文檢索欄位使用隨機字串生成，建立索引。

create table test(id int, info tsvector, crt_time timestamp);  
  
-- 生成隨機字串  
CREATE OR REPLACE FUNCTION public.gen_rand_str(integer)            
 RETURNS text            
 LANGUAGE sql            
 STRICT            
AS $function$            
  select string_agg(a[random()*6+1],``) from generate_series(1,$1), (select array[`a`,`b`,`c`,`d`,`e`,`f`,` `]) t(a);            
$function$;   
  
-- 插入100萬條資料  
insert into test select generate_series(1,1000000), to_tsvector(gen_rand_str(512)), now();  
  
-- 建立索引  
create index idx_test_info on test using gin (info);  
create index idx_test_id on test (id);

測試SQL

更新crt_time時間欄位，但是不更新全文檢索欄位。

set id random(1,1000000)  
update test set crt_time=now() where id=:id;  
  
或  
  
setrandom id 1 1000000  
update test set crt_time=now() where id=:id;

注意，雖然我們沒有更新全文檢索欄位，但是依舊會導致GIN索引的變更，因為token->ctid，由於PG多版本的原因這裡的ctid會變化，如果CTID變成了其他PAGE的行，那麼索引也需要變化。

即使是更新後的記錄在同一個PAGE（HOT更新），VACUUMM時將老的記錄刪掉也需要變更索引ENTRY。

總之這個為了突出業務上可能忽視的問題。以為不更新索引欄位，索引就不需要變化。

PS：PG 10或將來會支援二級索引，就不會存在以上問題。那麼使用者只需要考慮索引欄位VALUE被更新的情況。

PostgreSQL 9.4 版本壓測

1、4併發

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 4 -j 4 -T 1000  
progress: 1.0 s, 8622.0 tps, lat 0.091 ms stddev 0.041  
progress: 2.0 s, 9038.2 tps, lat 0.549 ms stddev 22.067  
progress: 3.0 s, 9910.5 tps, lat 0.466 ms stddev 18.571  
progress: 4.0 s, 11642.6 tps, lat 0.389 ms stddev 16.555  
progress: 5.0 s, 12109.2 tps, lat 0.386 ms stddev 16.879  
progress: 6.0 s, 9292.4 tps, lat 0.468 ms stddev 18.731  
progress: 7.0 s, 4511.1 tps, lat 0.077 ms stddev 0.023  
progress: 8.0 s, 15309.5 tps, lat 0.320 ms stddev 15.127  
progress: 9.0 s, 18481.9 tps, lat 0.274 ms stddev 13.459  
progress: 10.0 s, 22044.6 tps, lat 0.242 ms stddev 12.381  
progress: 11.0 s, 5432.4 tps, lat 0.789 ms stddev 26.151  
progress: 12.0 s, 22851.0 tps, lat 0.070 ms stddev 0.019  
progress: 13.0 s, 35955.7 tps, lat 0.175 ms stddev 10.177

2、16併發

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 16 -j 16 -T 1000  
progress: 1.0 s, 65915.6 tps, lat 0.104 ms stddev 0.075  
progress: 2.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 3.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 4.0 s, 20134.1 tps, lat 2.256 ms stddev 76.169  
progress: 5.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 6.0 s, 10403.8 tps, lat 3.658 ms stddev 90.374  
progress: 7.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 8.0 s, 9328.5 tps, lat 3.659 ms stddev 85.652  
progress: 9.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 10.0 s, 8348.0 tps, lat 3.787 ms stddev 84.213  
progress: 11.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 12.0 s, 7258.0 tps, lat 1.394 ms stddev 49.557  
progress: 13.0 s, 21.0 tps, lat 1231.018 ms stddev 1173.690  
progress: 14.0 s, 7237.3 tps, lat 1.228 ms stddev 48.168  
progress: 15.0 s, 13.0 tps, lat 1191.294 ms stddev 1108.031  
progress: 16.0 s, 9.0 tps, lat 1482.792 ms stddev 1657.674  
progress: 17.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 18.0 s, 6163.0 tps, lat 4.255 ms stddev 126.424  
progress: 19.0 s, 17.0 tps, lat 1785.435 ms stddev 1721.592

3、64併發

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 1000  
progress: 1.0 s, 2083.1 tps, lat 1.243 ms stddev 1.126  
progress: 2.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 3.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 4.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 5.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 6.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 7.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 8.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 9.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 10.0 s, 2030.1 tps, lat 300.032 ms stddev 1647.060  
progress: 11.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 12.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 13.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 14.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 15.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 16.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 17.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 18.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 19.0 s, 2064.0 tps, lat 289.639 ms stddev 1586.564  
progress: 20.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 21.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 22.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 23.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 24.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 25.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 26.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 27.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 28.0 s, 0.0 tps, lat -nan ms stddev -nan

我們發現，併發越高，效能抖動非常嚴重，但是資料庫中並未發現waiting。

postgres=# select pid,query,waiting from pg_stat_activity ;  
  pid  |                      query                       | waiting   
-------+--------------------------------------------------+---------  
 39830 | update test set crt_time=now() where id=$1;      | f  
 39836 | update test set crt_time=now() where id=$1;      | f  
 39841 | update test set crt_time=now() where id=$1;      | f  
 39845 | update test set crt_time=now() where id=$1;      | f  
 39852 | update test set crt_time=now() where id=$1;      | f  
 39858 | update test set crt_time=now() where id=$1;      | f  
 39862 | update test set crt_time=now() where id=$1;      | f  
 39869 | update test set crt_time=now() where id=$1;      | f  
 39874 | update test set crt_time=now() where id=$1;      | f

跟蹤程式pstack，如下，出現了lock和sleep。

pstack 39926  

#0  0x00007f3836a21393 in __select_nocancel () from /lib64/libc.so.6  
#1  0x0000000000818d3a in pg_usleep ()  
#2  0x00000000006c2c66 in s_lock ()  
#3  0x00000000006a30ff in ReleaseBuffer ()  
#4  0x0000000000472320 in ginInsertValue ()  
#5  0x000000000046ad5a in ginEntryInsert ()  
#6  0x0000000000478552 in ginHeapTupleFastInsert ()   -- 插入pending list  
#7  0x000000000046b30a in gininsert ()  
#8  0x00000000007e13b7 in FunctionCall6Coll ()  
#9  0x000000000049fc5f in index_insert ()  
#10 0x00000000005c5975 in ExecInsertIndexTuples ()  
#11 0x00000000005d4db7 in ExecModifyTable ()  
#12 0x00000000005bb278 in ExecProcNode ()  
#13 0x00000000005b91fd in standard_ExecutorRun ()  
#14 0x00000000006d5816 in ProcessQuery ()  
#15 0x00000000006d5aef in PortalRunMulti ()  
#16 0x00000000006d5fda in PortalRun ()  
#17 0x00000000006d24d9 in exec_execute_message ()  
#18 0x00000000006d430c in PostgresMain ()  
#19 0x000000000066bcaf in PostmasterMain ()  
#20 0x00000000005f469c in main ()

PG GIN索引有一個fastupdate的選項，實際上是因為一條記錄涉及多個TOKEN，為了防止索引頻繁更新，PG設計的一種快速DML方法。就是先將資料寫入pending list，然後由vacuum, analyze或當list滿時觸發將pengding list合併到gin tree的動作。

程式碼分析

首先看一下pending list區域的大小由什麼控制。

PostgreSQL 9.4

postgresql 9.4的pending list大小由work_mem引數控制。

https://www.postgresql.org/docs/9.4/static/gin-implementation.html#GIN-FAST-UPDATE

src/backend/access/gin/ginfast.c

/*  
 * Write the index tuples contained in *collector into the index`s  
 * pending list.  
 *  
 * Function guarantees that all these tuples will be inserted consecutively,  
 * preserving order  
 */  
void  
ginHeapTupleFastInsert(GinState *ginstate, GinTupleCollector *collector)  
{  
  
......  
  
        /*  
         * Force pending list cleanup when it becomes too long. And,  
         * ginInsertCleanup could take significant amount of time, so we prefer to  
         * call it when it can do all the work in a single collection cycle. In  
         * non-vacuum mode, it shouldn`t require maintenance_work_mem, so fire it  
         * while pending list is still small enough to fit into work_mem.  
         *  
         * ginInsertCleanup() should not be called inside our CRIT_SECTION.  
         */  
        if (metadata->nPendingPages * GIN_PAGE_FREESIZE > work_mem * 1024L)  
                needCleanup = true;  
  
        UnlockReleaseBuffer(metabuffer);  
  
        END_CRIT_SECTION();  
  
        if (needCleanup)  
                ginInsertCleanup(ginstate, false, NULL);  
}

PostgreSQL 10

PostgreSQL 10的gin pending list大小由表級引數，或者全域性引數gin_pending_list_limit控制。

https://www.postgresql.org/docs/10/static/gin-implementation.html

src/include/access/gin_private.h

#define GinGetPendingListCleanupSize(relation)   
        ((relation)->rd_options &&   
         ((GinOptions *) (relation)->rd_options)->pendingListCleanupSize != -1 ?   
         ((GinOptions *) (relation)->rd_options)->pendingListCleanupSize :   
         gin_pending_list_limit)  
  
  
  
/*  
 * Write the index tuples contained in *collector into the index`s  
 * pending list.  
 *  
 * Function guarantees that all these tuples will be inserted consecutively,  
 * preserving order  
 */  
void  
ginHeapTupleFastInsert(GinState *ginstate, GinTupleCollector *collector)  
{  
  
  
  
  
        /*  
         * Force pending list cleanup when it becomes too long. And,  
         * ginInsertCleanup could take significant amount of time, so we prefer to  
         * call it when it can do all the work in a single collection cycle. In  
         * non-vacuum mode, it shouldn`t require maintenance_work_mem, so fire it  
         * while pending list is still small enough to fit into  
         * gin_pending_list_limit.  
         *  
         * ginInsertCleanup() should not be called inside our CRIT_SECTION.  
         */  
        cleanupSize = GinGetPendingListCleanupSize(index);  
        if (metadata->nPendingPages * GIN_PAGE_FREESIZE > cleanupSize * 1024L)  
                needCleanup = true;  
  
        UnlockReleaseBuffer(metabuffer);  
  
        END_CRIT_SECTION();  
  
        if (needCleanup)  
                ginInsertCleanup(ginstate, false, true, NULL);  
}

效能抖動和pending list大小有沒有關係呢？

調整pending list大小重新測試

預設work_mem, gin_pending_list_limit都是4MB。

PostgreSQL 9.4 版本壓測

1、work_mem = 64kB

4併發

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 4 -j 4 -T 1000  
progress: 1.0 s, 32554.8 tps, lat 0.121 ms stddev 1.110  
progress: 2.0 s, 36012.4 tps, lat 0.110 ms stddev 0.930  
progress: 3.0 s, 39955.8 tps, lat 0.098 ms stddev 0.785  
progress: 4.0 s, 44042.8 tps, lat 0.090 ms stddev 0.642  
progress: 5.0 s, 47402.4 tps, lat 0.083 ms stddev 0.545  
progress: 6.0 s, 46474.7 tps, lat 0.085 ms stddev 0.491  
progress: 7.0 s, 50977.9 tps, lat 0.077 ms stddev 0.387  
progress: 8.0 s, 51330.5 tps, lat 0.077 ms stddev 0.403

64併發

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 1000  
progress: 1.0 s, 13021.7 tps, lat 4.089 ms stddev 36.660  
progress: 2.0 s, 6642.0 tps, lat 9.485 ms stddev 54.981  
progress: 3.0 s, 6994.0 tps, lat 9.174 ms stddev 60.217  
progress: 4.0 s, 4345.6 tps, lat 14.211 ms stddev 68.500  
progress: 5.0 s, 7360.6 tps, lat 7.853 ms stddev 51.010  
progress: 6.0 s, 6372.2 tps, lat 11.388 ms stddev 66.828  
progress: 7.0 s, 6324.7 tps, lat 8.510 ms stddev 50.780  
progress: 8.0 s, 5943.3 tps, lat 12.828 ms stddev 72.672  
progress: 9.0 s, 6861.8 tps, lat 8.754 ms stddev 57.454  
progress: 10.0 s, 6083.8 tps, lat 10.360 ms stddev 60.478  
progress: 11.0 s, 7248.5 tps, lat 8.640 ms stddev 52.020  
progress: 12.0 s, 7171.5 tps, lat 9.383 ms stddev 60.656  
progress: 13.0 s, 5656.4 tps, lat 10.994 ms stddev 60.451  
progress: 14.0 s, 6396.0 tps, lat 9.981 ms stddev 62.383  
progress: 15.0 s, 4699.3 tps, lat 13.693 ms stddev 71.377  
progress: 16.0 s, 7418.4 tps, lat 8.886 ms stddev 54.437  
....  
  
progress: 83.0 s, 5156.8 tps, lat 12.807 ms stddev 63.668  
progress: 84.0 s, 4043.1 tps, lat 15.275 ms stddev 73.505  
progress: 85.0 s, 5127.0 tps, lat 13.008 ms stddev 66.901  
progress: 86.0 s, 5359.9 tps, lat 11.516 ms stddev 63.316  
progress: 87.0 s, 748.5 tps, lat 15.886 ms stddev 69.462  
progress: 88.0 s, 5490.9 tps, lat 21.286 ms stddev 137.586  
progress: 89.0 s, 3695.7 tps, lat 17.238 ms stddev 76.678  
progress: 90.0 s, 4141.1 tps, lat 15.490 ms stddev 74.441  
progress: 91.0 s, 5796.0 tps, lat 11.150 ms stddev 59.668

2、work_mem = 128kB

4併發

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 4 -j 4 -T 1000  
progress: 1.0 s, 46604.8 tps, lat 0.084 ms stddev 0.748  
progress: 2.0 s, 48606.3 tps, lat 0.081 ms stddev 0.613  
progress: 3.0 s, 51673.2 tps, lat 0.076 ms stddev 0.436  
progress: 4.0 s, 49671.8 tps, lat 0.079 ms stddev 0.595  
progress: 5.0 s, 51807.9 tps, lat 0.076 ms stddev 0.427  
progress: 6.0 s, 51751.9 tps, lat 0.076 ms stddev 0.405  
progress: 7.0 s, 52160.9 tps, lat 0.075 ms stddev 0.361  
progress: 8.0 s, 55404.6 tps, lat 0.071 ms stddev 0.393  
progress: 9.0 s, 54101.5 tps, lat 0.072 ms stddev 0.416

64併發

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 1000  
progress: 1.0 s, 24304.3 tps, lat 1.902 ms stddev 31.997  
progress: 2.0 s, 6585.9 tps, lat 9.479 ms stddev 72.847  
progress: 3.0 s, 7045.9 tps, lat 9.267 ms stddev 69.525  
progress: 4.0 s, 7310.2 tps, lat 8.398 ms stddev 64.139  
progress: 5.0 s, 3764.9 tps, lat 16.462 ms stddev 97.427  
progress: 6.0 s, 4425.2 tps, lat 15.909 ms stddev 97.157  
progress: 7.0 s, 3876.2 tps, lat 15.035 ms stddev 100.171  
progress: 8.0 s, 4772.2 tps, lat 13.357 ms stddev 88.078  
progress: 9.0 s, 6198.3 tps, lat 11.602 ms stddev 90.655  
progress: 10.0 s, 7339.2 tps, lat 8.658 ms stddev 66.986

3、work_mem = 32MB

4併發

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 4 -j 4 -T 1000  
progress: 1.0 s, 57437.4 tps, lat 0.068 ms stddev 0.024  
progress: 2.0 s, 53123.4 tps, lat 0.074 ms stddev 0.031  
progress: 3.0 s, 45341.6 tps, lat 0.079 ms stddev 0.023  
progress: 4.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 5.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 6.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 7.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 8.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 9.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 10.0 s, 0.0 tps, lat -nan ms stddev -nan

64併發

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 1000  
progress: 1.0 s, 159573.9 tps, lat 0.395 ms stddev 0.438  
progress: 2.0 s, 163035.2 tps, lat 0.391 ms stddev 0.441  
progress: 3.0 s, 162534.7 tps, lat 0.392 ms stddev 0.476  
progress: 4.0 s, 162375.3 tps, lat 0.393 ms stddev 0.527  
...  
progress: 19.0 s, 167594.3 tps, lat 0.380 ms stddev 0.431  
progress: 20.0 s, 164800.4 tps, lat 0.387 ms stddev 0.447  
progress: 21.0 s, 62932.3 tps, lat 0.378 ms stddev 0.439  
progress: 22.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 23.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 24.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 25.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 26.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 27.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 28.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 29.0 s, 0.0 tps, lat -nan ms stddev -nan  
progress: 30.0 s, 0.0 tps, lat -nan ms stddev -nan  
  
很長很長時間tps=0

觀察到一個現象：

1、pending list(work_mem)越大，效能抖動越嚴重，tps=0越持久。

2、work_mem越小，效能抖動越少，但是峰值效能會有一定的下降。

3、併發越低，效能越穩定。

4、work_mem較小時，即使併發較高，tps=0的機率也非常小。

PostgreSQL 9.4的優化建議：

1、work_mem設定為64kB，降低更新併發（例如使用連線池控制併發）。

2、將建立了gin索引的欄位剝離到獨立的表，通過PK將兩者進行關聯。

例如

create table test(id int primary, crt_time timestamp,...其他欄位);  
  
create table test_info (id int primary key, info tsvector);

PostgreSQL 10的改進

PostgreSQL 10提交了一個PATCH，解決了gin vacuum時需要對整個posting tree的所有頁面長時間持鎖的問題。

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=218f51584d5a9fcdf702bcc7f54b5b65e255c187

Reduce page locking in GIN vacuum  
  
GIN vacuum during cleaning posting tree can lock this whole tree for a long  
time with by holding LockBufferForCleanup() on root. Patch changes it with  
two ways: first, cleanup lock will be taken only if there is an empty page  
(which should be deleted) and, second, it tries to lock only subtree, not the  
whole posting tree.  
  
Author: Andrey Borodin with minor editorization by me  
Reviewed-by: Jeff Davis, me  
https://commitfest.postgresql.org/13/896/

PostgreSQL 10效能壓測

1、pending_list_limit = 64kB

postgres=# alter index idx_test_info set (gin_pending_list_limit = 64);  
ALTER INDEX

4併發

progress: 23.0 s, 78817.0 tps, lat 0.051 ms stddev 0.004  
progress: 24.0 s, 78643.1 tps, lat 0.051 ms stddev 0.076  
progress: 25.0 s, 78718.6 tps, lat 0.051 ms stddev 0.084  
progress: 26.0 s, 79931.3 tps, lat 0.050 ms stddev 0.083  
progress: 27.0 s, 79523.5 tps, lat 0.050 ms stddev 0.092  
progress: 28.0 s, 79449.2 tps, lat 0.050 ms stddev 0.084  
progress: 29.0 s, 80023.3 tps, lat 0.050 ms stddev 0.090  
progress: 30.0 s, 79718.2 tps, lat 0.050 ms stddev 0.103  
progress: 31.0 s, 80446.6 tps, lat 0.050 ms stddev 0.003  
progress: 32.0 s, 79853.8 tps, lat 0.050 ms stddev 0.101  
progress: 33.0 s, 79592.8 tps, lat 0.050 ms stddev 0.097  
progress: 34.0 s, 78224.2 tps, lat 0.051 ms stddev 0.239

64併發

progress: 52.0 s, 228751.3 tps, lat 0.280 ms stddev 0.298  
progress: 53.0 s, 194422.8 tps, lat 0.329 ms stddev 2.412  
progress: 54.0 s, 227821.5 tps, lat 0.281 ms stddev 0.293  
progress: 55.0 s, 216616.2 tps, lat 0.295 ms stddev 1.073  
progress: 56.0 s, 228380.8 tps, lat 0.280 ms stddev 0.368  
progress: 57.0 s, 230253.9 tps, lat 0.278 ms stddev 0.348  
progress: 58.0 s, 229343.5 tps, lat 0.279 ms stddev 0.339  
progress: 59.0 s, 202373.8 tps, lat 0.316 ms stddev 2.022  
progress: 60.0 s, 227434.2 tps, lat 0.281 ms stddev 0.331  
progress: 61.0 s, 225450.5 tps, lat 0.284 ms stddev 0.349  
progress: 62.0 s, 208194.6 tps, lat 0.301 ms stddev 1.130  
progress: 63.0 s, 216338.4 tps, lat 0.302 ms stddev 1.071  
progress: 64.0 s, 204889.6 tps, lat 0.312 ms stddev 1.565  
progress: 65.0 s, 224583.9 tps, lat 0.285 ms stddev 0.382  
progress: 66.0 s, 209168.7 tps, lat 0.306 ms stddev 1.124  
progress: 67.0 s, 224295.0 tps, lat 0.285 ms stddev 0.385

2、pending_list_limit = 128kB

postgres=# alter index idx_test_info set (gin_pending_list_limit = 128);  
ALTER INDEX

4併發

progress: 54.0 s, 79711.9 tps, lat 0.050 ms stddev 0.149  
progress: 55.0 s, 76737.8 tps, lat 0.051 ms stddev 0.248  
progress: 56.0 s, 60214.1 tps, lat 0.066 ms stddev 0.782  
progress: 57.0 s, 52029.9 tps, lat 0.078 ms stddev 1.053  
progress: 58.0 s, 60822.2 tps, lat 0.066 ms stddev 0.782  
progress: 59.0 s, 80107.9 tps, lat 0.050 ms stddev 0.004  
progress: 60.0 s, 79895.1 tps, lat 0.050 ms stddev 0.140  
progress: 61.0 s, 80622.3 tps, lat 0.050 ms stddev 0.004

64併發

progress: 127.0 s, 223377.9 tps, lat 0.287 ms stddev 0.243  
progress: 128.0 s, 224016.9 tps, lat 0.286 ms stddev 0.301  
progress: 129.0 s, 224614.2 tps, lat 0.285 ms stddev 0.240  
progress: 130.0 s, 226185.8 tps, lat 0.283 ms stddev 0.336  
progress: 131.0 s, 222921.9 tps, lat 0.287 ms stddev 0.395  
progress: 132.0 s, 215942.6 tps, lat 0.285 ms stddev 0.241  
progress: 133.0 s, 207529.5 tps, lat 0.320 ms stddev 2.013  
progress: 134.0 s, 224605.9 tps, lat 0.284 ms stddev 0.240  
progress: 135.0 s, 224704.7 tps, lat 0.286 ms stddev 0.341  
progress: 136.0 s, 224848.3 tps, lat 0.285 ms stddev 0.363  
progress: 137.0 s, 226411.0 tps, lat 0.283 ms stddev 0.339  
progress: 138.0 s, 225254.1 tps, lat 0.284 ms stddev 0.237  
progress: 139.0 s, 224640.3 tps, lat 0.285 ms stddev 0.327

3、pending_list_limit = 4MB

postgres=# alter index idx_test_info set (gin_pending_list_limit = 4096);  
ALTER INDEX

4併發

progress: 67.0 s, 81374.3 tps, lat 0.049 ms stddev 0.003  
progress: 68.0 s, 81713.3 tps, lat 0.049 ms stddev 0.003  
progress: 69.0 s, 80631.6 tps, lat 0.050 ms stddev 0.004  
progress: 70.0 s, 80821.0 tps, lat 0.049 ms stddev 0.004  
progress: 71.0 s, 81783.2 tps, lat 0.049 ms stddev 0.003  
progress: 72.0 s, 71155.7 tps, lat 0.056 ms stddev 0.931  
progress: 73.0 s, 81714.0 tps, lat 0.049 ms stddev 0.003  
progress: 74.0 s, 81456.0 tps, lat 0.049 ms stddev 0.004  
progress: 75.0 s, 81591.8 tps, lat 0.049 ms stddev 0.003  
progress: 76.0 s, 80284.4 tps, lat 0.050 ms stddev 0.004  
progress: 77.0 s, 80637.0 tps, lat 0.050 ms stddev 0.003  
progress: 78.0 s, 81178.1 tps, lat 0.049 ms stddev 0.003  
progress: 79.0 s, 80447.3 tps, lat 0.050 ms stddev 0.003  
progress: 80.0 s, 80951.4 tps, lat 0.049 ms stddev 0.003  
progress: 81.0 s, 81138.6 tps, lat 0.049 ms stddev 0.003  
progress: 82.0 s, 80637.2 tps, lat 0.050 ms stddev 0.003  
progress: 83.0 s, 80643.6 tps, lat 0.050 ms stddev 0.004  
progress: 84.0 s, 80786.5 tps, lat 0.050 ms stddev 0.004  
progress: 85.0 s, 79975.0 tps, lat 0.050 ms stddev 0.004  
progress: 86.0 s, 80947.5 tps, lat 0.049 ms stddev 0.003  
progress: 87.0 s, 80761.2 tps, lat 0.050 ms stddev 0.004

64併發

progress: 8.0 s, 228075.5 tps, lat 0.281 ms stddev 0.227  
progress: 9.0 s, 196448.4 tps, lat 0.326 ms stddev 2.398  
progress: 10.0 s, 226087.3 tps, lat 0.283 ms stddev 0.228  
progress: 11.0 s, 226998.2 tps, lat 0.282 ms stddev 0.230  
progress: 12.0 s, 226270.9 tps, lat 0.283 ms stddev 0.230  
progress: 13.0 s, 226731.1 tps, lat 0.282 ms stddev 0.230  
progress: 14.0 s, 226088.0 tps, lat 0.283 ms stddev 0.231  
progress: 15.0 s, 227248.2 tps, lat 0.282 ms stddev 0.227  
progress: 16.0 s, 227266.0 tps, lat 0.282 ms stddev 0.226  
progress: 17.0 s, 227264.0 tps, lat 0.282 ms stddev 0.228  
progress: 18.0 s, 216534.3 tps, lat 0.296 ms stddev 0.720  
progress: 19.0 s, 227696.1 tps, lat 0.281 ms stddev 0.225  
progress: 20.0 s, 226178.8 tps, lat 0.283 ms stddev 0.231  
progress: 21.0 s, 227289.1 tps, lat 0.282 ms stddev 0.229  
progress: 22.0 s, 225112.8 tps, lat 0.284 ms stddev 0.232  
progress: 23.0 s, 228076.6 tps, lat 0.281 ms stddev 0.225  
progress: 24.0 s, 194332.3 tps, lat 0.253 ms stddev 0.215  
progress: 25.0 s, 81322.6 tps, lat 0.956 ms stddev 26.334  
progress: 26.0 s, 228265.8 tps, lat 0.285 ms stddev 2.451  
progress: 27.0 s, 224867.7 tps, lat 0.285 ms stddev 0.235  
progress: 28.0 s, 225499.8 tps, lat 0.284 ms stddev 0.233

4、pending_list_limit = 128MB

postgres=# alter index idx_test_info set (gin_pending_list_limit = 131072);  
ALTER INDEX

4併發

progress: 13.0 s, 78402.4 tps, lat 0.051 ms stddev 0.004  
progress: 14.0 s, 78956.9 tps, lat 0.051 ms stddev 0.004  
progress: 15.0 s, 79046.6 tps, lat 0.051 ms stddev 0.004  
progress: 16.0 s, 78720.3 tps, lat 0.051 ms stddev 0.004  
progress: 17.0 s, 79181.2 tps, lat 0.051 ms stddev 0.004  
progress: 18.0 s, 79725.3 tps, lat 0.050 ms stddev 0.004  
progress: 19.0 s, 79403.7 tps, lat 0.050 ms stddev 0.004

64併發

progress: 73.0 s, 227570.5 tps, lat 0.281 ms stddev 0.244  
progress: 74.0 s, 225064.2 tps, lat 0.284 ms stddev 0.236  
progress: 75.0 s, 224500.2 tps, lat 0.285 ms stddev 0.245  
progress: 76.0 s, 223362.8 tps, lat 0.287 ms stddev 0.253  
progress: 77.0 s, 225023.5 tps, lat 0.284 ms stddev 0.234  
progress: 78.0 s, 197069.7 tps, lat 0.326 ms stddev 2.198  
progress: 79.0 s, 224710.8 tps, lat 0.285 ms stddev 0.239

PostgreSQL 10 效能非常的平穩，即使是高併發，高pending list的情況下，沒有出現tps=0的情況。

同時在PG 10下，pstack沒有觀測到idx_test_info索引被更新的情況，這也是一個大的改進，可以找一下git.postgresql.org對應哪個patch。

GIN索引優化手段小結

對於需要頻繁更新的表，如果這個表的某些欄位建立了GIN索引，為了減少GIN索引的更新開銷，優化如下。

PG 9.4 優化手段

1、設定表的fillfactor（如=50），儘量使用讓資料庫使用HOT更新。減少行遷移，從而減少索引entry的更新。

postgres=# alter table test set (fillfactor =50);
ALTER TABLE

2、設定較小work_mem，例如設定為64kB。

3、使用連線池，控制併發。

4、將tsvector欄位拆分出來，使用PK進行關聯。完全杜絕沒必要的更新。

其中1,2是最好實施的，不影響業務，效果立竿見影(但是在巨大壓力、巨大併發下依舊偶爾會有一兩秒的tps=0)。

PG 10 優化手段

1、設定表的fillfactor（如=50），儘量使用讓資料庫使用HOT更新。減少行遷移，從而減少索引entry的更新。

PG 10在巨大壓力、巨大併發(同時伴隨checkpoint, vacuum的虐待)下，TPS表現都非常平穩，抖動不超過5%。

其他程式碼層優化手段

1、使用二級索引

《PostgreSQL 10.0 preview 效能增強 – 間接索引(secondary index)》

理解索引：索引優化
2018-06-04
索引優化
MSSQL優化之索引優化
2009-04-15
SQL優化索引
Elasitcsearch索引優化
2018-11-15
索引優化
MySQL索引優化
2021-01-05
MySql索引優化
mysql優化索引
2020-12-23
MySql優化索引
SQL優化-索引
2013-11-15
SQL優化索引
MySQL 效能優化之索引優化
2015-04-01
MySql優化索引
JVM鎖優化
2019-04-12
JVM優化
鎖優化--1
2018-04-01
優化
MySQL調優之索引優化
2021-11-09
MySql索引優化
Mysql索引優化（一）
2020-12-02
MySql索引優化
MySQL 5.7 索引優化
2016-07-11
MySql索引優化
Oracle 索引的優化
2015-12-30
Oracle索引優化
Mysql索引優化之索引的分類
2019-07-04
MySql索引優化
MySQL 字串索引優化方案
2020-07-01
MySql字串索引優化
MongoDB索引優化詳解
2019-02-23
MongoDB索引優化
MySQL 索引原理以及優化
2019-06-03
MySql索引優化
mysql索引原理及優化
2021-06-20
MySql索引優化
MySQL——索引優化實戰
2018-08-08
MySql索引優化
MySQL 筆記 - 索引優化
2018-08-12
MySql筆記索引優化
效能優化：索引下推
2020-11-18
優化索引
MySQL優化之索引解析
2022-03-15
MySql優化索引
使用索引優化StopKey
2014-06-16
索引優化TopK
索引優化和維護
2014-10-22
索引優化
倒排索引優化 - 跳錶
2014-09-23
索引優化
SUM優化（複合索引）
2014-08-25
優化索引
聊聊索引和SQL優化
2011-02-03
索引SQL優化
索引優化index skip scan
2010-12-08
索引優化Index
SQL優化--函式索引
2012-01-19
SQL優化函式索引
前端效能優化方案索引
2015-10-07
前端優化索引
MySQL-效能優化-索引和查詢優化
2018-06-03
MySql優化索引
使用Elasticsearch的動態索引和索引優化
2019-03-28
Elasticsearch索引優化
鎖機制優化MySQL
2012-09-20
優化MySql
MySQL函式索引及優化
2020-07-04
MySql函式索引優化
MySQL 索引使用策略及優化
2019-08-07
MySql索引優化
MongoDB索引與優化詳解
2019-04-27
MongoDB索引優化
MySQL索引與查詢優化
2019-04-13
MySql索引優化
SQL優化之利用索引排序
2019-07-10
SQL優化索引排序

PostgreSQL10GIN索引鎖優化

標籤

背景

例子

PostgreSQL 9.4 版本壓測

程式碼分析

PostgreSQL 9.4

PostgreSQL 10

調整pending list大小重新測試

PostgreSQL 9.4 版本壓測

PostgreSQL 10的改進

PostgreSQL 10效能壓測

GIN索引優化手段小結

PG 9.4 優化手段

PG 10 優化手段

其他程式碼層優化手段

相關文章

PostgreSQL10GIN索引鎖優化

標籤

背景

例子

PostgreSQL 9.4 版本壓測

程式碼分析

PostgreSQL 9.4

PostgreSQL 10

調整pending list大小重新測試

PostgreSQL 9.4 版本壓測

PostgreSQL 10的改進

PostgreSQL 10效能壓測

GIN索引 優化手段小結

PG 9.4 優化手段

PG 10 優化手段

其他程式碼層優化手段

相關文章

GIN索引優化手段小結