本文分享自华为云社区《GaussDB(DWS)性能优化之业务降IO优化》，作者：along_2020。

IO 高？业务慢？在 DWS 实际业务场景中因 IO 高、IO 瓶颈导致的性能问题非常多，其中应用业务设计不合理导致的问题占大多数。本文从应用业务优化角度，以常见触发 IO 慢的业务 SQL 场景为例，指导如何通过优化业务去提升 IO 效率和降低 IO。

说明 ：因磁盘故障（如慢盘）、raid 卡读写策略（如 Write Through）、集群主备不均等非应用业务原因导致的 IO 高不在本次讨论。

一、确定 IO 瓶颈 &识别高 IO 的语句

1、查等待视图确定 IO 瓶颈

SELECT wait_status,wait_event,count(*) AS cnt FROM pgxc_thread_wait_status
WHERE wait_status <> 'wait cmd' AND wait_status <> 'synchronize quit' AND wait_status <> 'none'
GROUP BY 1,2 ORDER BY 3 DESC limit 50;

IO 瓶颈时常见等待状态如下：

2、抓取高 IO 消耗的 SQL

主要思路为先通过 OS 命令识别消耗高的线程，然后结合 DWS 的线程号信息找到消耗高的业务 SQL，具体方法参见附件中 iowatcher.py 脚本和 README 使用介绍

3、SQL 级 IO 问题分析基础

在抓取到消耗 IO 高的业务 SQL 后怎么分析？主要掌握以下两点基础知识：

1）PGXC_THREAD_WAIT_STATUS 视图功能，详细介绍参见：

https://support.huaweicloud.com/devg2-dws/dws_0402_0892.html

2）EXPLAIN 功能，至少需掌握的知识点有 Scan 算子、A-time、A-rows、E- rows，详细介绍参见：

https://bbs.huaweicloud.com/blogs/197945

二、常见触发 IO 瓶颈的高频业务场景

场景 1：列存小 CU 膨胀

某业务 SQL 查询出 390871 条数据需 43248ms，分析计划主要耗时在 Cstore Scan

Cstore Scan 的详细信息中，每个 DN 扫描出 2w 左右的数据，但是扫描了有数据的 CU(CUSome) 155079 个，没有数据的 CU（CUNone） 156375 个，说明当前小 CU、未命中数据的 CU 极多，也即 CU 膨胀严重。

触发因素：对列存表（分区表尤甚）进行高频小批量导入会造成 CU 膨胀

处理方法：

1、列存表的数据入库方式修改为攒批入库，单分区单批次入库数据量大于 DN 个数*6W 为宜

2、如果确因业务原因无法攒批，则考虑次选方案，定期 VACUUM FULL 此类高频小批量导入的列存表。

3、当小 CU 膨胀很快时，频繁 VACUUM FULL 也会消耗大量 IO，甚至加剧整个系统的 IO 瓶颈，这时需考虑整改为行存表（CU 长期膨胀严重的情况下，列存的存储空间优势和顺序扫描性能优势将不复存在）。

场景 2：脏数据 &数据清理

某 SQL 总执行时间 2.519s，其中 Scan 占了 2.516s，同时该表的扫描最终只扫描到 0 条符合条件数据，过滤了 20480 条数据，也即总共扫描了 20480+0 条数据却消耗了 2s+，这种扫描时间与扫描数据量严重不符的情况，基本就是脏数据多影响扫描和 IO 效率。

查看表脏页率为 99%，Vacuum Full 后性能优化到 100ms 左右

触发因素：表频繁执行 update/delete 导致脏数据过多，且长时间未 VACUUM FULL 清理

处理方法：

• 对频繁 update/delete 产生脏数据的表，定期 VACUUM FULL，因大表的 VACUUM FULL 也会消耗大量 IO，因此需要在业务低峰时执行，避免加剧业务高峰期 IO 压力。

• 当脏数据产生很快，频繁 VACUUM FULL 也会消耗大量 IO，甚至加剧整个系统的 IO 瓶颈，这时需要考虑脏数据的产生是否合理。针对频繁 delete 的场景，可以考虑如下方案：1）全量 delete 修改为 truncate 或者使用临时表替代 2）定期 delete 某时间段数据，设计成分区表并使用 truncate&drop 分区替代

场景 3：表存储倾斜

例如表 Scan 的 A-time 中，max time dn 执行耗时 6554ms，min time dn 耗时 0s，dn 之间扫描差异超过 10 倍以上，这种集合 Scan 的详细信息，基本可以确定为表存储倾斜导致

通过 table_distribution 发现所有数据倾斜到了 dn_6009 单个 dn，修改分布列使的表存储分布均匀后，max dn time 和 min dn time 基本维持在相同水平 400ms 左右，Scan 时间从 6554ms 优化到 431ms。

触发因素：分布式场景，表分布列选择不合理会导致存储倾斜，同时导致 DN 间压力失衡，单 DN IO 压力大，整体 IO 效率下降。

解决办法：修改表的分布列使表的存储分布均匀，分布列选择原则参《GaussDB 8.x.x 产品文档》中“表设计最佳实践”之“选择分布列章节”。

场景 4：无索引、有索引不走

例如某点查询，Seq Scan 扫描需要 3767ms，因涉及从 4096000 条数据中获取 8240 条数据，符合索引扫描的场景（海量数据中寻找少量数据），在对过滤条件列增加索引后，计划依然是 Seq Scan 而没有走 Index Scan。

对目标表 analyze 后，计划能够自动选择索引，性能从 3s+优化到 2ms+，极大降低 IO 消耗

常见场景：行存大表的查询场景，从大量数据中访问极少数据，没走索引扫描而是走顺序扫描，导致 IO 效率低，不走索引常见有两种情况：

• 过滤条件列上没建索引

• 有索引但是计划没选索引扫描

触发因素：

• 常用过滤条件列没有建索引

• 表中数据因 DML 产生数据特征变化后未及时 ANALYZE 导致优化器无法选择索引扫描计划，ANALYZE 介绍参见https://bbs.huaweicloud.com/blogs/192029
  

处理方式：

1、对行存表常用过滤列增加索引，索引基本设计原则：

• 索引列选择 distinct 值多，且常用于过滤条件，过滤条件多时可以考虑建组合索引，组合索引中 distinct 值多的列排在前面，索引个数不宜超过 3 个

• 大量数据带索引导入会产生大量 IO，如果该表涉及大量数据导入，需严格控制索引个数，建议导入前先将索引删除，导数完毕后再重新建索引；

2、对频繁做 DML 操作的表，业务中加入及时 ANALYZE，主要场景：

• 表数据从无到有

• 表频繁进行 INSERT/UPDATE/DELETE

• 表数据即插即用，需要立即访问且只访问刚插入的数据

场景 5：无分区、有分区不剪枝

例如某业务表进场使用 createtime 时间列作为过滤条件获取特定时间数据，对该表设计为分区表后没有走分区剪枝（Selected Partitions 数量多），Scan 花了 701785ms，IO 效率极低。

在增加分区键 creattime 作为过滤条件后，Partitioned scan 走分区剪枝（Selected Partitions 数量极少），性能从 700s 优化到 10s，IO 效率极大提升。

常见场景：按照时间存储数据的大表，查询特征大多为访问当天或者某几天的数据，这种情况应该通过分区键进行分区剪枝（只扫描对应少量分区）来极大提升 IO 效率，不走分区剪枝常见的情况有：

• 未设计成分区表

• 设计了分区没使用分区键做过滤条件

• 分区键做过滤条件时，对列值有函数转换

触发因素：未合理使用分区表和分区剪枝功能，导致扫描效率低

处理方式：

• 对按照时间特征存储和访问的大表设计成分区表

• 分区键一般选离散度高、常用于查询 filter 条件中的时间类型的字段

• 分区间隔一般参考高频的查询所使用的间隔，需要注意的是针对列存表，分区间隔过小（例如按小时）可能会导致小文件过多的问题，一般建议最小间隔为按天。

场景 6：行存表求 count 值

例如某行存大表频繁全表 count（指不带 filter 条件或者 filter 条件过滤很少数据的 count），其中 Scan 花费 43s，持续占用大量 IO，此类作业并发起来后，整体系统 IO 持续 100%，触发 IO 瓶颈，导致整体性能慢。

对比相同数据量的列存表（A-rows 均为 40960000），列存的 Scan 只花费 14ms，IO 占用极低

触发因素：行存表因其存储方式的原因，全表 scan 的效率较低，频繁的大表全表扫描，导致 IO 持续占用。

解决办法：

• 业务侧审视频繁全表 count 的必要性，降低全表 count 的频率和并发度

• 如果业务类型符合列存表，则将行存表修改为列存表，提高 IO 效率

场景 7：行存表求 max 值

例如求某行存表某列的 max 值，花费了 26772ms，此类作业并发起来后，整体系统 IO 持续 100%，触发 IO 瓶颈，导致整体性能慢。

针对 max 列增加索引后，语句耗时从 26s 优化到 32ms，极大减少 IO 消耗

触发因素：行存表 max 值逐个 scan 符合条件的值来计算 max，当 scan 的数据量很大时，会持续消耗 IO

解决办法：给 max 列增加索引，依靠 btree 索引天然有序的特征，加速扫描过程，降低 IO 消耗。

场景 8：大量数据带索引导入

某客户场景数据往 DWS 同步时，延迟严重，集群整体 IO 压力大。

后台查看等待视图有大量 wait wal sync 和 WALWriteLock 状态，均为 xlog 同步状态

触发因素：大量数据带索引（一般超过 3 个）导入（insert/copy/merge into）会产生大量 xlog，导致主备同步慢，备机长期 Catchup，整体 IO 利用率飙高。历史案例参考：

https://bbs.huaweicloud.com/blogs/242269

解决方案：

• 严格控制每张表的索引个数，建议 3 个以内

• 大量数据导入前先将索引删除，导数完毕后再重新建索引；

场景 9：行存大表首次查询

某客户场景出现备 DN 持续 Catcup，IO 压力大，观察某个 sql 等待视图在 wait wal sync

排查业务发现某查询语句执行时间较长，kill 后恢复

触发因素：行存表大量数据入库后，首次查询触发 page hint 产生大量 XLOG，触发主备同步慢及大量 IO 消耗。

解决措施：

• 对该类一次性访问大量新数据的场景，修改为列存表

• 关闭 wal_log_hints 和 enable_crc_check 参数（故障期间有丢数风险，不推荐）

场景 10：小文件多 IOPS 高

某业务现场一批业务起来后，整个集群 IOPS 飙高，另外当出现集群故障后，长期 building 不完，IOPS 飙高，相关表信息如下：

SELECT relname,reloptions,partcount FROM pg_class c INNER JOIN (
SELECT parented,count(*) AS partcount FROM pg_partition
GROUP BY parentid ) s ON c.oid = s.parentid ORDER BY partcount DESC;

触发因素：某业务库大量列存多分区（3000+）的表，导致小文件巨多（单 DN 文件 2000w+），访问效率低，故障恢复 Building 极慢，同时 building 也消耗大量 IOPS，发向影响业务性能。

解决办法：

• 整改列存分区间隔，减少分区个数来降低文件个数

• 列存表修改为行存表，行存的存储特征决定其文件个数不会像列存那么膨胀严重

三、小结

经过前面案例，稍微总结下不难发现，提升 IO 使用效率概括起来可分为两个维度，即提升 IO 的存储效率和计算效率（又称访问效率），提升存储效率包括整合小 CU、减少脏数据、消除存储倾斜等，提升计算效率包括分区剪枝、索引扫描等，大家根据实际场景灵活处理即可。

• 附件：iowatcher.rar
  

华为伙伴暨开发者大会 2022 火热来袭，重磅内容不容错过！

【精彩活动】

勇往直前·做全能开发者→12 场技术直播前瞻，8 大技术宝典高能输出，还有代码密室、知识竞赛等多轮神秘任务等你来挑战。即刻闯关，开启终极大奖！点击踏上全能开发者晋级之路吧！

【技术专题】

未来已来，2022 技术探秘→华为各领域的前沿技术、重磅开源项目、创新的应用实践，站在智能世界的入口，探索未来如何照进现实，干货满满点击了解

点击关注，第一时间了解华为云新鲜技术~