作者
digoal
日期
2021-12-24
标签
PostgreSQL , 热门问题
- 问题说明(现象、环境)
- 分析原因
- 结论和解决办法
20、为什么分区表的分区过多会导致性能下降?
https://www.bilibili.com/video/BV1zL411V7HX/
1、表太大的通用危害
背景知识点: autovacuum 垃圾回收、freeze 冻结xid号 操作都是有粒度的: 一个表、一个索引只能有1个进程对其进行回收和冻结工作(同一个表的多个索引可以并行). 但是多个不同的表(包括不同的分区)可以有多个工作进程并行执行.
- (未来也许单个表(单个分区)也能实现多进行并行垃圾回收, 但是当前的版本并不支持.)
危害1: 垃圾回收慢, 可能跟不上产生垃圾的速度. 导致表、索引膨胀.
危害2: 表大, 索引自然也大, 垃圾扫描时垃圾版本可能超出内存(暂存垃圾行号的内存), 使得索引可能需要被多次扫描.
- 《PostgreSQL 垃圾回收参数优化之 - maintenance_work_mem , autovacuum_work_mem》
危害3: 单个表freeze的操作变久, 在极端情况可能导致xid耗尽, 数据库需要停库进入单用户模式执行freeze降低年龄后才能正常使用.
危害4: freeze的操作密度变大(表就这么大, 完成1个表就会产生大量的wal,IO. 如果是小表还能通过配置参数来进行freeze错峰.), 可能导致wal打爆, 消耗大量wal存储, 消耗大量数据文件、wal文件的IO影响性能, 同时可能导致standby延迟.
危害5: 由于单表(单个最底层分区)的逻辑备份恢复无法并行, 大表的逻辑备份耗时变长、逻辑恢复耗时变长. 除了时长是个问题, 另一个问题是时长可能会导致膨胀.
- 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第13期 - 为什么长时间等待业务处理的情况不建议封装在事务中?》
危害6: 由于单表(单个最底层分区)只能对应到某一个表空间, 表空间又会对应到某个目录, 某个目录对应到某个文件系统. 所以单表很大的时候可能大于某个文件系统, 而将表拆小后能放到不同的表空间, 不同的文件系统.
危害7: 逻辑复制时, 初始数据全量同步的效率问题. (单个表(单个最底层分区)无法并行, 所以单表太大可能导致全量拷贝的时间很长, 除了膨胀问题, 还有一个问题: 复制异常中断后又得再次全量同步, 隐患比较大)
危害8: 特大的表, 可能超出PostgreSQL的寻址边界: CTID(blockid, tupleid), BlockID为4字节无符号整型, (block_size=8K 时最大寻址空间 32TB).
2、经常要清理数据, 保持苗条身材的场景. 没有分区的危害:
例如最常见的, 按时间清理历史数据.
没有分区的危害:
危害1: 没有分区, 那么就必须delete来清理数据, delete 产生大量wal日志, 导致从库延迟, 同时还需要垃圾回收产生二次数据文件和WAL IO, 而且delete清理大量数据是个事务, 长事务还会引发膨胀隐患.
- 建议分区: 使用时间分区, 可以直接drop或truncate分区. 几乎不产生wal, 也不需要垃圾回收, 非常快, 没有后遗症.
3、多个块设备的场景, 没有分区, 单个表高频率访问时, 无法达到充分利用块设备的效果.
如果你的数据库环境有很多块盘, 通常1个文件系统最多对应1个块设备, 所以一个非分区表无法充分利用块设备的并行吞吐能力.
对数据库透明的其他解决方案: 可以用卷管理来实现多个块设备组合成条带, 用逻辑卷照样能利用多个块设备的并行吞吐, 而且对数据库透明.
《PostgreSQL 11 1万亿 tpcb 性能测试 on 阿里云ECS + ESSD + zfs/lvm2条带 + block_size=32K》
截止到目前的PG版本(14), 分区过多也会有一些问题:
问题1: 分区过多, 对于一些老版本的PG, 优化器在生成path后才会裁剪分区, 分区过多会使得生成执行计划非常耗时. 对于短平快的SQL, 性能损耗尤为突出.
- 老的PG版本可能使用pg_pathman进行此类优化(裁剪优化)
问题2: relcache 缓存暴增, 老版本的PG会缓存所有的分区元数据. 即使是优化后的PG新版本, 如果业务使用了长连接, 假使每个会话在会话的整个生命周期内访问过每个子分区的话, 同样会导致relcache占用内存过多, 使得每个会话占有大量内存. 甚至触发OOM.
问题3: 当SQL没有输入分区字段作为条件时, 需要访问所有分区, 分区过多会导致执行计划时间超长. 同时可能某些更优的访问路径就无法支持(例如某些情况下hashagg, hashjoin, merge join等可能无法支持)
- 目前未支持分区表全局索引, 所以按非分区字段排序需要访问所有分区, 使用merge sort方式进行优化.
- 不含分区字段的过滤, 无法裁剪分区, 即使有索引也需要访问所有分区, 需要访问更多数据块(每个本地索引的meta page, branch page, leaf page).
分两种情况, 假设你用了很好的SSD.
1、高频更新、删除、插入
这种表产生的垃圾多, 需要频繁回收垃圾, 否则会膨胀. 而且要保证垃圾回收的效率比产生垃圾快. 因此推荐单个分区不能太大.
经验值: 能够满足不因垃圾回收不及时而导致膨胀为宜. 例如: 5000万条.
2、append only, 少量更新
以不产生影响业务性能(抖动)的wal风暴为宜, 这种表的分区可以适当大一点, 例如5亿.
《PostgreSQL pg_rewrite 插件 - 在线将普通表转换为分区表》
期望 PostgreSQL 增加什么功能?
PolarDB for PostgreSQL云原生分布式开源数据库
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
