背景

PostgreSQL HEAP引擎,同一张表的记录的有效版本、垃圾版本存储在同一个数据文件中。ZHEAP引擎将垃圾版本从数据文件剥离,存储在UNDO文件中。

由于HEAP引擎的垃圾版本会存储在数据文件中,所以可能会导致表的膨胀,PG有垃圾回收的功能可以回收这些垃圾版本,使得膨胀尽可能可控(包括索引中的垃圾版本)。但是配置或使用不当时,还是可能导致表和索引的膨胀。

那么到底如何避免,到底应该如何设置参数?哪些操作可能导致膨胀?膨胀了怎么处理?

多版本机制

1、更新记录会产生新版本,旧版本保留在原地不动,仅在记录头信息中标记删除记录的事务号(xmax)。

2、删除记录,旧版本存储在原地不动。仅在记录头信息中标记删除记录的事务号(xmax)。

例如

  1. postgres=# create table t1(id int, info text);
  2. CREATE TABLE
  3. postgres=# insert into t1 values (1,'test');
  4. INSERT 0 1

会话1

  1. postgres=# begin;
  2. BEGIN
  3. 开启事务
  4. postgres=# select txid_current();
  5. txid_current
  6. --------------
  7. 749507
  8. (1 row)
  9. 查询当前记录行号,版本号
  10. postgres=# select ctid,xmin,xmax,* from t1;
  11. ctid | xmin | xmax | id | info
  12. -------+--------+------+----+------
  13. (0,1) | 749506 | 0 | 1 | test
  14. (1 row)

会话2

  1. 更新记录
  2. postgres=# update t1 set info='test1';
  3. UPDATE 1

会话1

查询记录版本,已生成第2个版本。

  1. postgres=# select ctid,xmin,xmax,* from t1;
  2. ctid | xmin | xmax | id | info
  3. -------+--------+------+----+-------
  4. (0,2) | 749509 | 0 | 1 | test1
  5. (1 row)

会话2

  1. 更新记录
  2. postgres=# update t1 set info='test2';
  3. UPDATE 1

会话1

查询记录版本,已生成第3个版本。

  1. postgres=# select ctid,xmin,xmax,* from t1;
  2. ctid | xmin | xmax | id | info
  3. -------+--------+------+----+-------
  4. (0,3) | 749510 | 0 | 1 | test2
  5. (1 row)

会话2

  1. 更新记录
  2. postgres=# update t1 set info='test3';
  3. UPDATE 1

会话1

查询记录版本,已生成第4个版本。

  1. postgres=# select ctid,xmin,xmax,* from t1;
  2. ctid | xmin | xmax | id | info
  3. -------+--------+------+----+-------
  4. (0,4) | 749511 | 0 | 1 | test3
  5. (1 row)

最老存在的事务快照之后产生的垃圾版本,不会被回收。所以可以看到一条记录在更新后一直在新增新的版本。

HOT技术

HOT 技术实际上是解决更新时索引放大(产生新的索引item)的问题,只要一笔更新中没有包含任何索引字段的更新,同时TUPLE新版本在同一个HEAP PAGE内时,索引不需要新增item。

《PostgreSQL Heap Only Tuple - HOT (降低UPDATE引入的索引写IO放大)》

垃圾回收机制

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

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

数据库只能回收实例中现存最老的事务快照之前产生的dead tuple。

最老的事务快照可能来自:

1、最老的未结束事务

2、最老的未结束SQL

3、最老的未结束的2pc(两阶段提交事务)

4、接收慢的wal sender slot

5、有standby,并且standby开启了feedback,同时standby的事务快照信息会反馈给主库,那么这个事务快照也会被主库的垃圾回收关照。

6、开启了强制延迟回收vacuum_defer_cleanup_age

7、开启了snapshot too old。

可以回收哪些版本?取决于几个因素

1、不管是什么地方残留的事务快照信息,以最老的快照为准。凡是这个快照之后产生的垃圾版本,都无法被回收。

2、达到snapshot too old后的版本,可以被回收,不管约束1。

3、设置了延迟回收vacuum_defer_cleanup_age时,只有年龄大于设定延迟年龄的垃圾版本才会被回收。

所以,膨胀的一种原因可能来自以上使用不当或设置不当。

垃圾回收相关参数

分为全局、表级设置。

《PostgreSQL 自动vacuum(垃圾回收)与手动vacuum的参数详解》

1、HIT:垃圾回收时阶段性工作量统计。

2、COST:工作量阶段性阈值,超出后DELAY

3、delay:暂停垃圾回收的WORKER工作,防止垃圾回收时耗费大量资源影响业务。smooth化的一种手段,但是会拉长整个VACUUM的周期,也是膨胀原因之一。

4、naptime:每个数据库的垃圾统计信息扫描间隔。

5、worker:最多允许开启多少个AUTOVACUUM WORKER进程同时进行垃圾回收。如果worker很少,也可能导致回收不及时,膨胀。

6、autovacuum memory:垃圾回收时,记录垃圾版本的行号,用于对应索引指向这些垃圾TUPLE的ITEM的回收。如果内存很小,可能导致回收过程中,索引要被多次扫描。

《PostgreSQL 垃圾回收参数优化之 - maintenance_work_mem , autovacuum_work_mem》

7、freeze相关参数(导致IO增大的原因),什么时候触发冻结(扫描全表),冻结哪些记录(修改TUPLE头信息)。

自动垃圾回收相关参数

同上

无法回收垃圾引发的额外问题

由于各种原因导致垃圾无法回收,会导致表的垃圾比例可能一直高于阈值水位,在开启了autovacuum的情况下,那么autovacuum会不停唤起,去执行垃圾回收,扫描表(产生大量读IO),CPU也会升高,但是无法回收(所以几乎不产生写IO)。

HEAP PAGE复用机制,HEAP PAGE删除机制

HEAP PAGE内的dead tuple清除后,会整理PAGE,空间立即就可以被复用。

HEAP PAGE什么时候可以从磁盘删除?

在数据文件末尾,并且整个PAGE里面全部都是DEAD TUPLE时,这种PAGE可以从磁盘删除,缩小数据文件的大小。(而中间的数据块无法删除,因为TUPLE的寻址地址会随之变化,索引无法根据ctid寻址(blockid, offset)访问到原来的TUPLE。)

《PostgreSQL 12 preview - heap table末端垃圾page是否在vacuum时被回收可控 - vacuum_truncate》

《PostgreSQL 单表并行bulkload的extend file lock 冲突问题解决 - 数据块预分配》

《parallel blocking|waiting by slow BLOCK extend relation , ExclusiveLock on extension of relation》

《PostgreSQL bulk COPY load Bottleneck by extend lock waiting》

INDEX PAGE复用机制,INDEX PAGE删除机制

索引页的复用与HEAP PAGE不一样,因为索引的内容是有序结构,只有复合顺序的ITEM才能插入对应的PAGE。不像HEAP TUPLE,只要有空间就可以插入。

index page无论在任何位置,都不能从磁盘删除,索引变大以后,不可能变小,除非vacuum full。因此索引膨胀后,通常需要重建索引来缩小索引大小。

  1. postgres=# create table t2(id int);
  2. CREATE TABLE
  3. postgres=# create index idx_t2 on t2(id);
  4. CREATE INDEX
  5. postgres=# insert into t2 select generate_series(1,1000000);
  6. INSERT 0 1000000
  7. postgres=# \dt+ t2
  8. List of relations
  9. Schema | Name | Type | Owner | Size | Description
  10. --------+------+-------+----------+-------+-------------
  11. public | t2 | table | postgres | 35 MB |
  12. (1 row)
  13. postgres=# \di+ idx_t2
  14. List of relations
  15. Schema | Name | Type | Owner | Table | Size | Description
  16. --------+--------+-------+----------+-------+-------+-------------
  17. public | idx_t2 | index | postgres | t2 | 21 MB |
  18. (1 row)
  19. postgres=# delete from t2 where id<>1;
  20. DELETE 999999
  21. postgres=# vacuum t2;
  22. VACUUM
  23. postgres=# \dt+ t2
  24. List of relations
  25. Schema | Name | Type | Owner | Size | Description
  26. --------+------+-------+----------+-------+-------------
  27. public | t2 | table | postgres | 40 kB |
  28. (1 row)
  29. postgres=# \di+ idx_t2
  30. List of relations
  31. Schema | Name | Type | Owner | Table | Size | Description
  32. --------+--------+-------+----------+-------+-------+-------------
  33. public | idx_t2 | index | postgres | t2 | 21 MB |
  34. (1 row)

HEAP PAGE删除相关参数

HEAP PAGE从磁盘删除,还取决于参数。例如删除大量数据后,还会写入大量数据,可以不删除数据库,避免重复EXTEND PAGE。(每次extend page都会改变文件大小,会修改文件系统的INODE元信息。有些系统这类操作会存在性能问题)

《PostgreSQL 12 preview - heap table末端垃圾page是否在vacuum时被回收可控 - vacuum_truncate》

为什么表会膨胀

例如:

1、长事务,长SQL,长2PC,长wal sender SLOT,standby feedback(并且STANDBY存在长的SQL等),无法回收OLDEST SNAPSHOT后产生的垃圾。

2、关闭自动回收。导致不自动回收垃圾。

3、vacuum worker delay,cost limit配置有问题,导致垃圾回收慢。

4、最近大量删除了表的前段PAGE记录并且还没有完成垃圾回收。

5、开启了延迟回收参数。

6、autovacuum worker个数太少,而产生垃圾的表很多,并且产生很快。

为什么索引会膨胀

同上。

如何处理表膨胀

1、避免长事务,长SQL,长2PC,长wal sender SLOT,standby feedback(并且STANDBY存在长的SQL等)。

2、开启snapshot too old

3、开启自动垃圾回收

4、设置合理的vacuum worker delay,cost limit配置。

5、关闭延迟回收参数。

6、设置合理的autovacuum worker memory

7、设置合理的autovacuum worker个数

如果已经膨胀:

1、pg_repack

2、vacuum full(锁全表,需要注意)

如何处理索引膨胀

重新创建索引,注意带CONCURRENTLY选项,否则会堵塞DML。

存储引擎技术

PG 12开始支持了AM扩展,可以有列存、UNDO存储等加入。

《PostgreSQL 12 AM 之 - blackhole 黑洞存储引擎》

《PostgreSQL 基于access method api的列存zedstore》

《PostgreSQL 12 preview - 意义重大改进:增加一层access manager API - 支持 TABLE、INDEX AM(access method) - 为storage pluggable 开路》

《PostgreSQL 12 preview - psql增强, \dA 列出当前系统中的access method (table am, index am)》

《PostgreSQL undo多版本存储引擎 zheap测试》

参考

《PostgreSQL 自动vacuum(垃圾回收)与手动vacuum的参数详解》

《PostgreSQL 12 preview - vacuum 新增开关: index_cleanup , 是否同时回收索引》

《PostgreSQL 12 preview - heap table末端垃圾page是否在vacuum时被回收可控 - vacuum_truncate》

《PostgreSQL 12 preview - Add progress reporting for CLUSTER and VACUUM FULL》

《PostgreSQL 12 preview - 调大默认autovacuum sleep间隔, 加速vacuum》

《PostgreSQL 12 preview - vacuum analyze skip_locked》

《PostgreSQL 12 preview - Delete empty pages during GiST VACUUM - 降低GiST索引膨胀概率》

《PostgreSQL 12 preview - 支持列出 vacuum full, cluster 过程详情 pg_stat_progress_cluster》

《PostgreSQL 10 CLogControlLock 等待事件分析与优化 - hint bit, freeze, autovacuum, 风暴》

《PostgreSQL 垃圾回收参数优化之 - maintenance_work_mem , autovacuum_work_mem》

《PostgreSQL 11 内核优化 - 降低vacuum cleanup阶段index scan概率 ( vacuum_cleanup_index_scale_factor , skip index vacuum cleanup stage)》

《Greenplum 垃圾回收、收集统计信息调度 - vacuum analyze 所有表 - 注意锁问题》

《PostgreSQL vacuum 的页内tuple move收缩空间》

《PostgreSQL 并行vacuum patch - 暨为什么需要并行vacuum或分区表》

《Greenplum vacuum ao表和heap表的区别》

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

《PostgreSQL 10.0 preview 功能增强 - SQL执行剩余时间 - 垃圾回收过程可视pg_stat_progress_vacuum》

《PostgreSQL物理”备库”的哪些操作或配置,可能影响”主库”的性能、垃圾回收、IO波动》

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

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

《PostgreSQL 9.6 vacuum freeze大幅性能提升 代码浅析》

《PostgreSQL 老湿机图解平安科技遇到的垃圾回收”坑”》

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

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

《PostgreSQL nagios monitor script (archive, vacuum, age, conn, rollback, standby, lock, xact, seq, index…)》

《PostgreSQL Systemtap example : autovacuum_naptime & databases in cluster》