PostgreSQL pageinspect 诊断与优化GIN (倒排) 索引合并延迟导致的查询性能下降问题

标签

PostgreSQL , brin索引 , gin索引 , 合并延迟 , gin_pending_list_limit , 查询性能下降

背景

GIN索引为PostgreSQL数据库多值类型的倒排索引，一条记录可能涉及到多个GIN索引中的KEY，所以如果写入时实时合并索引，会导致IO急剧增加，写入RT必然增加。为了提高写入吞吐，PG允许用户开启GIN索引的延迟合并技术，开启后，数据会先写入pending list，并不是直接写入索引页，当pending list达到一定大小，或者autovacuum 对应表时，会触发pending list合并到索引的动作。

查询时，如果有未合并到索引中的PENDING LIST，那么会查询pending list，同时查询索引也的信息。

如果写入量很多，pending list非常巨大，合并(autovacuum worker做的)速度跟不上时，会导致通过GIN索引查询时查询性能下降。

知道问题的根源，就知道如何解决，以及如何排查。

背景原理

https://www.postgresql.org/docs/11/static/sql-createindex.html

GIN indexes accept different parameters:

1、fastupdate

This setting controls usage of the fast update technique described in Section 66.4.1. It is a Boolean parameter: ON enables fast update, OFF disables it. (Alternative spellings of ON and OFF are allowed as described in Section 19.1.) The default is ON.

Note

Turning fastupdate off via ALTER INDEX prevents future insertions from going into the list of pending index entries, but does not in itself flush previous entries. You might want to VACUUM the table or call gin_clean_pending_list function afterward to ensure the pending list is emptied.

2、gin_pending_list_limit

Custom gin_pending_list_limit parameter. This value is specified in kilobytes.

当前设置

postgres=# show gin_pending_list_limit ;  
 gin_pending_list_limit   
------------------------  
 4MB  
(1 row)  

BRIN indexes accept different parameters:

1、pages_per_range

Defines the number of table blocks that make up one block range for each entry of a BRIN index (see Section 67.1 for more details). The default is 128.

2、autosummarize

Defines whether a summarization run is invoked for the previous page range whenever an insertion is detected on the next one.

通过pageinspect可观察索引的pending list等内容。

https://www.postgresql.org/docs/11/static/pageinspect.html

postgres=# create extension pageinspect ;  
CREATE EXTENSION  

例子

1、建表

postgres=# create table t(id int, arr int[]);  
CREATE TABLE  

2、创建倒排索引

postgres=# create index idx_t_1 on t using gin (arr);  
CREATE INDEX  

3、创建生成随机数组的函数

postgres=# create or replace function gen_rand_arr() returns int[] as $$  
  select array(select (100*random())::int from generate_series(1,64));  
$$ language sql strict;  
CREATE FUNCTION  

4、写入测试数据

postgres=# insert into t select generate_series(1,100000), gen_rand_arr();  
INSERT 0 100000  
postgres=# insert into t select generate_series(1,1000000), gen_rand_arr();  
INSERT 0 1000000  

5、通过pageinspect插件，观察当前GIN索引的pendinglist大小，可以看到pending page有356个，涉及2484条记录。

如果很多条记录在pending list中，查询性能会下降明显。

postgres=# SELECT * FROM gin_metapage_info(get_raw_page('idx_t_1', 0));  
 pending_head | pending_tail | tail_free_size | n_pending_pages | n_pending_tuples | n_total_pages | n_entry_pages | n_data_pages | n_entries | version   
--------------+--------------+----------------+-----------------+------------------+---------------+---------------+--------------+-----------+---------  
            2 |          369 |           3640 |             356 |             2848 |             2 |             1 |            0 |         0 |       2  
(1 row)  

6、查询测试1，（pending list大于0）

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t where arr @> array[1,2,3];  
                                                         QUERY PLAN                                                           
----------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.t  (cost=82.38..262.28 rows=11373 width=284) (actual time=82.444..141.559 rows=114906 loops=1)  
   Output: id, arr  
   Recheck Cond: (t.arr @> '{1,2,3}'::integer[])  
   Heap Blocks: exact=41304  
   Buffers: shared hit=42043  
   ->  Bitmap Index Scan on idx_t_1  (cost=0.00..79.92 rows=11373 width=0) (actual time=75.902..75.902 rows=114906 loops=1)  
         Index Cond: (t.arr @> '{1,2,3}'::integer[])  
         Buffers: shared hit=739    
 Planning Time: 0.092 ms  
 Execution Time: 152.260 ms  
(10 rows)  

7、vacuum table，强制合并pending list

set vacuum_cost_delay=0;  
  
postgres=# vacuum t;  
VACUUM  

8、观察pendign list合并后，n_pending_tuples等于0.

postgres=# SELECT * FROM gin_metapage_info(get_raw_page('idx_t_1', 0));  
 pending_head | pending_tail | tail_free_size | n_pending_pages | n_pending_tuples | n_total_pages | n_entry_pages | n_data_pages | n_entries | version   
--------------+--------------+----------------+-----------------+------------------+---------------+---------------+--------------+-----------+---------  
   4294967295 |   4294967295 |              0 |               0 |                0 |          9978 |            41 |         9421 |       101 |       2  
(1 row)  

9、查询测试2，（pending list = 0）

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t where arr @> array[1,2,3];  
                                                          QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.t  (cost=792.36..1699.10 rows=117244 width=284) (actual time=79.861..139.603 rows=114906 loops=1)  
   Output: id, arr  
   Recheck Cond: (t.arr @> '{1,2,3}'::integer[])  
   Heap Blocks: exact=41304  
   Buffers: shared hit=41687  
   ->  Bitmap Index Scan on idx_t_1  (cost=0.00..766.95 rows=117244 width=0) (actual time=73.360..73.360 rows=114906 loops=1)  
         Index Cond: (t.arr @> '{1,2,3}'::integer[])  
         Buffers: shared hit=383   -- 大幅减少   
 Planning Time: 0.135 ms  
 Execution Time: 150.656 ms  
(10 rows)  

与此类似，brin也有类似的情况。

处理方法类似。

小结

数据库为了降低索引引入的写RT升高，采用了延迟合并的方法。如果数据库长期写压力巨大，可能导致未合并的LIST很大，导致查询性能受到影响。

使用pageinspect插件可以观察未合并的pending list有多大。

使用vacuum可以强制合并pending list，提高查询性能。

参考

https://www.postgresql.org/docs/11/static/pageinspect.html

https://www.postgresql.org/docs/11/static/sql-createindex.html