PostgreSQL 10 GIN索引 锁优化

标签

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

时间： 2024-03-18 01:29:17

PostgreSQL 10 GIN索引 锁优化的相关文章

[MySQL5.7] Innodb的索引锁优化

背景: . 这是一个臭名昭彰的问题,Innodb的btree发生合并/分裂等可能修改B-tree的操作时,都需要对其加排他的索引锁,这时候是无法对该索引进行读写操作的,极大的影响了性能:关于index lock,可以看看大神Domas的这篇博文:"Innodb locking makes me sad"  以及Vadim的这篇博客  . 总而言之,MySQL5.7.2的这个功能点的改进是万众期待的!  . 以下是阅读Rev6232的笔记,大概理了下关于索引锁优化的几个点.有些只是自己的

PostgreSQL GIN索引实现原理

标签 PostgreSQL , GIN , 内核 , 实现原理 , PostgreSQL数据库内核分析 背景 本文参考并扩展自如下文档,修正了一些内容(大多数是由于版本不同造成的差异) <PostgreSQL数据库内核分析> ( 成书较早,大量内容基于8.4的代码编写 ) 以及 http://zisedeqing.blog.163.com/blog/static/95550871201621623458216/ ( 大量内容参考自 PostgreSQL数据库内核分析 ) 术语 本文适用的一些术

PostgreSQL 10.0 preview 性能增强 - GIN索引vacuum锁降低

标签 PostgreSQL , 10.0 , GIN vacuum , 锁范围降低 背景 如果你发现你的CPU没怎么用,但是压力就是上不去,很大可能是锁等待造成的(perf可以观察),锁在数据库优化中是一个比较永恒的话题. 以往在vacuum GIN索引clean posting tree时,需要锁整个posting tree,10.0改进了这块的锁,现在只锁一个subtree. 在较大的GIN KEY被更新后,清除posting tree时,锁冲突更小了. Reduce page lockin

PostgreSQL 10.0 preview 功能增强 - OLAP增强 向量聚集索引(列存储扩展)

标签 PostgreSQL , 10.0 , Vertical Clustered Index (columnar store extension) , 列存储 , 向量聚集索引 背景 未来数据库OLTP+OLAP逐渐模糊化,需求逐渐融合是一个大的趋势,如果你的数据库只支持OLTP的场景,未来可能会成为业务的绊脚石. 在这方面PostgreSQL每年发布的新版本,都给用户很大的惊喜,OLTP已经具备非常强大的竞争力(性能.功能.稳定性.成熟度.案例.跨行业应用等),而OLAP方面,新增的feat

PostgreSQL GIN multi-key search 优化

标签 PostgreSQL , gin , in , or , multi key , right link scan , skip scan 背景 PostgreSQL中,有一种GIN索引,被广泛应用于多值类型,例如数组,分词,同时也被应用于模糊查询等领域. gin索引,将列(比如数组,全文检索类型)中的值拿出来,再存储到树形结构中(类似B-TREE,键值+heap行号s),对于低频值,会作为posting list直接存在树的gin的叶子节点中,而对于高频值,行号s会存储在另外树结构(pos

PostgreSQL GIN索引limit慢的原因分析

PostgreSQL GIN索引的结构如下图 :假设这个表有2列,一列存储INT,另一列存储INT数组,最左边的表示记录的行号. 假设对INT数组建立GIN索引,那么GIN索引会记录每个数组element对应的行号,对于行号多的,会存成LIST,然后在索引中指向该list. 好了接下来分析一下limit慢的原因, 实际上和gin索引的扫描方法有关,目前gin 索引只支持bitmap index scan,也就是说,会将所有匹配的行号取出,排序,然后去heap表取记录.那么不管你limit多小,根

PostgreSQL 10.0 preview 性能增强 - 间接索引(secondary index)

标签 PostgreSQL , 10.0 , 间接索引 , 第二索引 背景 我们知道,PostgreSQL的MVCC是多版本来实现的,当更新数据时,产生新的版本. 那么如果新版本不在同一个数据块的时候,索引也要随之变化,当新版本在同一个堆表的块里面时,则发生HOT UPDATE,不需要变更没有发生值改变的索引. 但是HOT总不能覆盖100%的更新,所以还是有索引更新的可能存在. 为了解决这个问题,PostgreSQL 10.0引入了第二索引(间接索引)的概念,即在PK或者UK之上,构建其他索引.

PostgreSQL 10.0 preview 功能增强 - BRIN 索引更新smooth化

标签 PostgreSQL , 10.0 , BRIN , de-summarization , summarization , 平滑更新BRIN , 平滑失效BRIN 背景 我们将数据存入PostgreSQL时,如果创建的是堆表,那么数据是往数据文件的末尾不断追加存储的. 为了提高数据的检索速度,可以对响应的字段创建索引,在PostgreSQL中,已有8种索引类型,分别是B-Tree,hash, gin, gist, sp-gist, brin, bloom, rum.分别对应不同的应用场景.

PostgreSQL 10.0 preview 多核并行增强 - 索引扫描、子查询、VACUUM、fdw/csp钩子

标签 PostgreSQL , 10.0 , 并行计算 , 索引扫描 , 子查询 , VACUUM , 外部表并行 背景 PostgreSQL 9.6推出的多核并行计算特性,支持全表扫描,hash join,聚合操作. 10.0 在此基础上,增加了更多的支持. 1. Parallel bitmap heap scan 2. Parallel Index Scans 3. Parallel Merge Join 4. parallelize queries containing subplans