gaussdb(dws)性能优化之业务降io优化【这次高斯不是数学家】-4008云顶国际网站

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

说明 ：因磁盘故障（如慢盘）、raid卡读写策略（如write through）、集群主备不均等非应用业务原因导致的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视图功能，详细介绍参见：

2）explain功能，至少需掌握的知识点有scan算子、a-time、a-rows、e- rows，详细介绍参见：

场景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介绍参见

处理方式：

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利用率飙高。历史案例参考：

4008云顶国际网站的解决方案：

• 严格控制每张表的索引个数，建议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、减少脏数据、消除存储倾斜等，提升计算效率包括分区剪枝、索引扫描等，大家根据实际场景灵活处理即可。

【这次高斯不是数学家】有奖征文火热进行中：https://bbs.huaweicloud.com/blogs/345260