PostgreSQL10GIN索引鎖優化

德哥發表於2017-07-05

標籤

PostgreSQL , gin , 倒排索引 , 全文檢索 , 效能優化


背景

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)》


相關文章