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

PostgreSQL 10 GIN索引锁优化

标签

背景

例子

PostgreSQL 9.4 版本压测

代码分析

PostgreSQL 9.4

PostgreSQL 10

调整pending list大小重新测试

PostgreSQL 9.4 版本压测

PostgreSQL 10的改进

PostgreSQL 10性能压测

GIN索引优化手段小结

PG 9.4 优化手段

PG 10 优化手段

其他代码层优化手段

数据库

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像

PostgreSQL 10 GIN索引 锁优化

标签

背景

例子

PostgreSQL 9.4 版本压测

代码分析

PostgreSQL 9.4

PostgreSQL 10

调整pending list大小重新测试

PostgreSQL 9.4 版本压测

PostgreSQL 10的改进

PostgreSQL 10性能压测

GIN索引 优化手段小结

PG 9.4 优化手段

PG 10 优化手段

其他代码层优化手段

数据库

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像

PostgreSQL 10 GIN索引锁优化

GIN索引优化手段小结