每天5分钟PG聊通透第20期,为什么分区表分区过多会导致性能下降?

背景

• 问题说明(现象、环境)
• 分析原因
• 结论和解决办法

链接、驱动、SQL

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 插件 - 在线将普通表转换为分区表》

用好周边工具, 数据库管理水平战胜90%老司机

1、管控软件

云猿生开源的kubeblocks, 如果你要管理很多套并且种类很多的数据库产品, 推荐选择.

• https://github.com/apecloud/kubeblocks

乘数开源的clup, 专门用来管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 企业可以关注一下.

• https://www.csudata.com/

若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套数据库, 并且对插件有特别多的需求, 推荐选择.

• https://pigsty.cc/zh/

2、审计监控诊断优化

海信聚好看的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.

• https://www.dbdoctor.cn/

Bytebase 的目标非常远大, 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。

• https://bytebase.cc/docs/introduction/what-is-bytebase/

D-Smart, Oracle老前辈白老大他们搞的, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.

• https://www.modb.pro/db/567140

3、国产数据库IDE

这个关注的人比较少但确是开发者的必备工具，请查看老程序员前辈达刚老师的DeskUI: https://www.deskui.com

4、数据同步&迁移&备份恢复

NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.

• https://www.ninedata.cloud/home

DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.

• https://www.dsgdata.com/

除了PolarDB还非常值得关注的几款PG栈国产数据库:

HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、ProtonBase(云原生分布式数仓. https://protonbase.com/ )、成都文武数据库（https://ww-it.cn）