PostgreSQL 垃圾版本引入的索引扫描性能下降诊断

3 minute read

背景

首先介绍几个背景知识，由于这些背景知识的存在，所以在某些情况下索引扫描的性能可能会出现一些问题或抖动。

导致性能下降，CPU开销增加，应用程序访问产生更多的连接等连锁反应。

1、当记录被删除，更新时，PostgreSQL目前的存储引擎会在原HEAP PAGE中保留老的记录版本，如果是UPDATE会写入一条新版本。

2、表上索引也会产生一个新的版本。

3、另一方面，PostgreSQL索引中没有包含版本信息，无法辨识该索引对应记录的可见性，PostgreSQL又引入了一个VM文件（标记每个HEAP PAGE是否所有记录对所有事务可见），从而实现INDEX ONLY SCAN。

4、普通的index scan，需要访问INDEX ITEM对应HEAP TUPLE的TUPLE HEADER来判断记录的可见性。

5、当垃圾回收时，回收heap tuple，回收index item。

6、索引页里面的所有index item都被回收后，索引页才会被回收。（回收该索引块，同时该页左右链接的两个数据块建立直接链接）

那么问题来了，如果索引中大量的index item对应的heap tuple都是不可见（或者已删除，但是没有回收）的版本，那么索引扫描的性能就会下降。同时引入更多的CPU消耗（判断版本的可见性）。

模拟问题

1、创建测试表

关闭垃圾回收（更容易模拟问题）

create table t123(id int, info text);  
create index idx_t123_id on t123(id);  
  
alter table t123 set (autovacuum_enabled =off);  

2、插入数据

insert into t123 select generate_series(1,1000000), 'test';

3、查询，没有问题，扫描了4个数据块。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t123 where id=1;  
                                                           QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_t123_id on public.t123  (cost=0.42..3520.93 rows=5000 width=36) (actual time=0.030..0.031 rows=1 loops=1)  
   Output: id, info  
   Index Cond: (t123.id = 1)  
   Buffers: shared hit=4  
 Planning time: 0.076 ms  
 Execution time: 0.053 ms  
(6 rows)  

4、插入不可见数据

postgres=# begin;  
BEGIN  
postgres=# insert into t123 select 1 , 'test123' from generate_series(1,1000000);  
INSERT 0 1000000  

5、查询，有问题，扫描了17203个数据块。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t123 where id=1;  
                                                          QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_t123_id on public.t123  (cost=0.43..2.45 rows=1 width=10) (actual time=127.646..127.647 rows=1 loops=1)  
   Output: id, info  
   Index Cond: (t123.id = 1)  
   Buffers: shared hit=17203  
 Planning time: 0.601 ms  
 Execution time: 127.723 ms  
(6 rows)  

6、可见，并删除

postgres=# end;  
COMMIT  
postgres=# select ctid from t123 where id=1 limit 1;  
    ctid       
-------------  
 (10810,150)  
(1 row)  
  
postgres=# delete from t123 where id=1 and ctid <>'(10810,150)';  
DELETE 1000000  

7、查询，有问题，扫描了20648个数据块。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t123 where id=1;  
                                                         QUERY PLAN                                                           
----------------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_t123_id on public.t123  (cost=0.43..2.45 rows=1 width=10) (actual time=0.032..146.112 rows=1 loops=1)  
   Output: id, info  
   Index Cond: (t123.id = 1)  
   Buffers: shared hit=20648  
 Planning time: 0.106 ms  
 Execution time: 146.142 ms  
(6 rows)  

8、垃圾回收，回收掉垃圾heap tuple, index item。

postgres=# vacuum verbose analyze t123;  
INFO:  vacuuming "public.t123"  
INFO:  scanned index "idx_t123_id" to remove 1000000 row versions  
DETAIL:  CPU: user: 0.20 s, system: 0.01 s, elapsed: 0.21 s  
INFO:  "t123": removed 1000000 row versions in 5407 pages  
DETAIL:  CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s  
INFO:  index "idx_t123_id" now contains 1000000 row versions in 6206 pages  
DETAIL:  1000000 index row versions were removed.  
3442 index pages have been deleted, 0 are currently reusable.  
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  
INFO:  "t123": found 149 removable, 1000000 nonremovable row versions in 10811 out of 10811 pages  
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 372671760  
There were 0 unused item pointers.  
Skipped 0 pages due to buffer pins, 0 frozen pages.  
0 pages are entirely empty.  
CPU: user: 0.33 s, system: 0.02 s, elapsed: 0.36 s.  
INFO:  vacuuming "pg_toast.pg_toast_1596783"  
INFO:  index "pg_toast_1596783_index" now contains 0 row versions in 1 pages  
DETAIL:  0 index row versions were removed.  
0 index pages have been deleted, 0 are currently reusable.  
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  
INFO:  "pg_toast_1596783": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages  
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 372671760  
There were 0 unused item pointers.  
Skipped 0 pages due to buffer pins, 0 frozen pages.  
0 pages are entirely empty.  
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  
INFO:  analyzing "public.t123"  
INFO:  "t123": scanned 10811 of 10811 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows  
VACUUM  

9、查询，有问题，扫描了6个数据块。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t123 where id=1;  
                                                       QUERY PLAN                                                          
-------------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_t123_id on public.t123  (cost=0.42..2.44 rows=1 width=9) (actual time=0.014..0.019 rows=1 loops=1)  
   Output: id, info  
   Index Cond: (t123.id = 1)  
   Buffers: shared hit=6  
 Planning time: 0.150 ms  
 Execution time: 0.040 ms  
(6 rows)  

通过pageinspect可以观察到heap page, index page的结构。请参考

《Use pageinspect EXTENSION view PostgreSQL Page’s raw infomation》

《PostgreSQL 黑科技 - 空间聚集存储, 内窥GIN, GiST, SP-GiST索引》

解决办法

1、数据库优化，自动垃圾回收，避免长事务（事务开启后，该事务后产生的垃圾无法被回收，直到事务结束（可以通过内核优化解决））

《影响或控制PostgreSQL垃圾回收的参数或因素》

《PostgreSQL 垃圾回收原理以及如何预防膨胀 - How to prevent object bloat in PostgreSQL》

《PostgreSQL垃圾回收代码分析 - why postgresql cann’t reclaim tuple is HEAPTUPLE_RECENTLY_DEAD》

2、开启快照过旧。

#old_snapshot_threshold = -1            # 1min-60d; -1 disables; 0 is immediate  
                                        # (change requires restart)  

《PostgreSQL 9.6 快照过旧 - 源码浅析》

3、内核优化1

仅保留其他会话需要读到的版本，例如同一条记录，如果被多次更新，那么这么多垃圾版本，在回收时，只保留当前其他活跃会话所需要的版本。

目前的做法，不管其他会话要不要读到，只判断TUPLE版本是不是当前最早打开事务之后产生的，如果是就不回收。

4、内核优化2

ZHEAP存储引擎，使用UNDO来保留旧版本，并且事务结束立即回收（只要其他会话不需要的话）。

《[未完待续] PostgreSQL 扩展存储引擎介绍 - zheap - 1》