# 降低I/O的处理方案 #### 问题现象 在DWS实际业务场景中因I/O高、I/O瓶颈导致的性能问题较多，其中应用业务设计不合理导致的问题占大多数。本文从应用业务优化角度，以常见触发I/O慢的业务SQL场景为例，指导如何通过优化业务去提升I/O效率和降低I/O。 #### 确定I/O瓶颈\&识别高I/O的语句 1. 通过以下内容掌握SQL级I/O问题分析的基础知识。 - [PGXC_THREAD_WAIT_STATUS视图功能](https://support.huaweicloud.com/devg-dws/dws_04_0826.html)。 - EXPLAIN功能，需了解并熟悉Scan算子、A-time、A-rows、E- rows，详细内容参见：[SQL执行计划](https://support.huaweicloud.com/devg-dws/dws_04_0432.html)。 2. 通过pgxc_thread_wait_status视图查看并确定I/O瓶颈。全部状态信息请参见[PG_THREAD_WAIT_STATUS](https://support.huaweicloud.com/devg-dws/dws_04_0783.html)。 ``` 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; ``` I/O瓶颈时常见的主要状态如下表所示。 表1I/O常见状态 | Wait status | Wait event | |:---|:---| | wait io：等待I/O完成。 | - BufFileRead：从临时文件中读取数据到指定buffer。 - BufFileWrite：向临时文件中写入指定buffer中的内容。 - DataFileRead：同步读取表数据文件。 - DataFileWrite：向表数据文件写入内容。 - ...... | | acquire lwlock：等待获取轻量级锁。 | WALWriteLock：用于避免并发WAL写盘。 | | wait wal sync：等待特定LSN的wal log完成到备机的同步。 | NA | | wait data sync：等待完成数据页到备机的同步。 | NA | | Material \| Material - write file：当前是Material算子，write file表示Material算子正在将数据写入磁盘。 | NA | 3. 获取高I/O消耗的SQL。 先通过OS命令识别消耗高的线程，然后结合DWS的线程号信息找到消耗高的业务SQL，获取工具和操作方法请下载[iowatcher](https://dws-lab01.obs.cn-north-4.myhuaweicloud.com/iowatcher/iowatcher.rar)。 #### 场景1：列存小CU膨胀 某业务SQL查询出390871条数据需43248ms，分析计划主要耗时在Cstore Scan。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760762689.png "点击放大") Cstore Scan的详细信息中，每个DN扫描出20000左右的数据，但是扫描了有数据的CU（CUSome）155079个，没有数据的CU（CUNone）156375个，说明当前小CU、未命中数据的CU极多，即CU膨胀严重。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760762761.png "点击放大") **触发因素**：对列存表（尤其是分区表）进行高频小批量导入会造成CU膨胀。 **处理方法** 1. 列存表的数据入库方式修改为攒批入库，单分区单批次入库数据量需大于DN个数\*60000。 2. 如果因业务原因无法攒批入库，则需定期VACUUM FULL此类高频小批量导入的列存表。 3. 当小CU膨胀很快时，频繁VACUUM FULL也会消耗大量I/O，甚至加剧整个系统的I/O瓶颈，此场景建议修改列存表为行存表（CU长期膨胀严重的情况下，列存的存储空间优势和顺序扫描性能优势将不复存在）。 #### 场景2：脏数据\&数据清理 某业务SQL总执行时间2.519s，其中Scan占了2.516s，同时该表的扫描最终只扫描到0条符合条件数据，过滤了20480条数据，即总共扫描了20480+0条数据却消耗了2s+，扫描时间与扫描数据量严重不符，此现象可判断为由于脏数据多从而影响扫描和I/O效率。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760762973.png "点击放大") 查看表脏页率为99%，VACUUM FULL后性能优化到100ms左右。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760765605.png "点击放大") **触发因素**：表频繁执行UPDATE/DELETE导致脏数据过多，且长时间未VACUUM FULL清理。 **处理方法** 1. 对频繁UPDATE/DELETE产生脏数据的表，定期VACUUM FULL，因大表的VACUUM FULL也会消耗大量I/O，因此需要在业务低峰时执行，避免加剧业务高峰期I/O压力。 2. 当脏数据产生很快，频繁VACUUM FULL也会消耗大量I/O，甚至加剧整个系统的I/O瓶颈，这时需要考虑脏数据的产生是否合理。针对频繁DELETE的场景，可以考虑如下方案： 1. 全量DELETE修改为TRUNCATE或者使用临时表替代。 2. 定期DELETE某时间段数据，使用分区表并使用TRUNCATE或DROP分区替代。 #### 场景3：表存储倾斜 例如表Scan的A-time中，max time DN执行耗时6554ms，min time DN耗时0s，DN之间扫描差异超过10倍以上，这种结合Scan的详细信息，基本可以确定为表存储倾斜导致。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760897509.png "点击放大") 通过table_distribution发现所有数据倾斜到了dn_6009单个DN，修改分布列使得表存储分布均匀后，max dn time和min dn time基本维持在相同水平400ms左右，Scan时间从6554ms优化到431ms。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001713086106.png "点击放大") **触发因素**：分布式场景，表分布列选择不合理会导致存储倾斜，同时导致DN间压力失衡，单DN I/O压力大，整体I/O效率下降。 **解决办法** ：修改表的分布列使表的存储分布均匀，分布列选择原则参见[选择分布列](https://support.huaweicloud.com/bestpractice-dws/dws_05_0076.html)。 #### 场景4：无索引、有索引不走 某一次点查询，Seq Scan扫描需要3767ms，因涉及从4096000条数据中获取8240条数据，符合索引扫描的场景（海量数据中寻找少量数据），在对过滤条件列增加索引后，计划依然是Seq Scan而没有走Index Scan。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760767489.png "点击放大") 对目标表ANALYZE后，计划能够自动选择索引，性能从3s+优化到2ms+，极大降低I/O消耗。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760767725.png "点击放大") **常见场景**：行存大表的查询场景，从大量数据中访问极少数据，没走索引扫描而是走顺序扫描，导致I/O效率低，不走索引常见有两种情况： - 过滤条件列上没建索引。 - 有索引但是计划没选索引扫描。 **触发因素**： - 常用过滤条件列没有建索引。 - 表中数据因执行DML操作后产生数据变化未及时ANALYZE，导致优化器无法选择索引扫描计划，ANALYZE介绍参见[ANALYZE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0245.html)。 **处理方式**： 1. 对行存表常用过滤列增加索引，索引基本设计原则： - 索引列选择distinct值多，且常用于过滤条件，过滤条件多时可以考虑建组合索引，组合索引中distinct值多的列排在前面，索引个数不宜超过3个。 - 大量数据带索引导入会产生大量I/O，如果该表涉及大量数据导入，需严格控制索引个数，建议导入前先将索引删除，导入完成后再重新建索引。 2. 对频繁做DML操作的表，业务中加入及时ANALYZE，主要场景： - 表数据从无到有。 - 表频繁进行INSERT/UPDATE/DELETE。 - 表数据即插即用，需要立即访问且只访问刚插入的数据。 #### 场景5：无分区、有分区不剪枝 例如某业务表经常使用createtime时间列作为过滤条件获取特定时间数据，对该表设计为分区表后没有走分区剪枝（Selected Partitions数量多），Scan花了701785ms，I/O效率极低。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001712928694.png "点击放大") 在增加分区键createtime作为过滤条件后，Partitioned scan走分区剪枝（Selected Partitions数量极少），性能从700s优化到10s，I/O效率极大提升。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760807609.png "点击放大") **常见场景**：按照时间存储数据的大表，查询特征大多为访问当天或者某几天的数据，这种情况应该通过分区键进行分区剪枝（只扫描对应少量分区）来极大提升I/O效率，不走分区剪枝常见的情况有： - 未设计成分区表。 - 设计了分区没使用分区键做过滤条件。 - 分区键做过滤条件时，对列值有函数转换。 **触发因素**：未合理使用分区表和分区剪枝功能，导致扫描效率低。 **处理方式**： - 对按照时间特征存储和访问的大表设计成分区表。 - 分区键一般选离散度高、常用于查询过滤条件中的时间类型的字段。 - 分区间隔一般参考高频的查询所使用的间隔，需要注意的是针对列存表，分区间隔过小（例如按小时）可能会导致小文件过多的问题，一般建议最小间隔为按天。 #### 场景6：行存表求count值 某行存大表频繁全表进行count（指不带过滤条件或者过滤条件过滤很少数据的count），其中Scan花费43s，持续占用大量I/O，此类作业并发起来后，整体系统I/O持续100%，触发I/O瓶颈，导致整体性能慢。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760807657.png "点击放大") 对比相同数据量的列存表（A-rows均为40960000），列存的Scan只花费14ms，I/O占用极低。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001713088254.png "点击放大") **触发因素**：行存表因其存储方式的原因，全表scan的效率较低，频繁的对大表全表扫描，导致I/O持续占用。 **解决办法**： - 业务侧审视频繁全表count的必要性，降低全表count的频率和并发度。 - 如果业务类型符合列存表，则将行存表修改为列存表，提高I/O效率。 #### 场景7：行存表求max值 计算某行存表某列的max值，花费了26772ms，此类作业并发起后，整体系统I/O持续100%，触发I/O瓶颈，导致整体性能慢。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760807685.png "点击放大") 针对max列增加索引后，语句耗时从26s优化到32ms，极大减少I/O消耗。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001713088334.png "点击放大") **触发因素**：行存表max值逐个scan符合条件的值来计算max，当scan的数据量很大时，会持续消耗I/O。 **解决办法**：给max列增加索引，凭借B-Tree索引数据有序存储的特征，加速扫描过程，降低I/O消耗。 #### 场景8：大量数据带索引导入 某业务场景数据往DWS同步时，延迟严重，集群整体I/O压力大。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760807757.png "点击放大") 后台查看等待视图有大量wait wal sync和WALWriteLock状态，均为xlog同步状态。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001760807765.png "点击放大") **触发因素**：大量数据带索引（一般超过3个）导入（insert/copy/merge into）会产生大量xlog，导致主备同步慢，备机长期Catchup，整体I/O利用率飙高。 **解决方案**： - 严格控制每张表的索引个数，建议3个以内。 - 大量数据导入前先将索引删除，导入完成后再重新建索引。 #### 场景9：行存大表首次查询 某业务场景出现备DN持续catchup，I/O压力大，观察某个SQL等待视图在wait wal sync。 ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001712928858.png "点击放大") 排查业务发现某查询语句执行时间较长，执行kill命令后恢复。 **触发因素**：行存表大量数据入库后，首次查询触发page hint产生大量XLOG，触发主备同步慢及大量I/O消耗。 **解决措施**： - 对该类一次性访问大量新数据的场景，修改行存表为列存表。 - 可关闭wal_log_hints和enable_crc_check参数（不推荐该方式，因故障期间有数据丢失风险）。 #### 场景10：小文件多IOPS高 某业务执行过程中，整个集群IOPS飙高，另外当出现集群故障后，长期Building不成功，IOPS飙高，相关表信息如下： ``` SELECT relname,reloptions,partcount FROM pg_class c INNER JOIN ( SELECT parentid,count(*) AS partcount FROM pg_partition GROUP BY parentid ) s ON c.oid = s.parentid ORDER BY partcount DESC; ``` ![](https://support.huaweicloud.com/trouble-dws/figure/zh-cn_image_0000001712928874.png "点击放大") **触发因素**：某业务库大量列存多分区（3000+）的表，导致小文件巨多（单DN文件2000万+），访问效率低，故障恢复Building极慢，同时Building也消耗大量IOPS，反向影响业务性能。 **解决办法**： - 整改列存分区间隔，减少分区个数来降低文件个数。 - 列存表修改为行存表，行存的存储特征决定其文件个数不会像列存般膨胀严重。 #### 小结 通过前面的场景总结得出，提升I/O使用效率可分为两个维度，即提升I/O的存储效率和计算效率（又称访问效率）。 - 提升存储效率包括整合小CU、减少脏数据、消除存储倾斜等。 - 提升计算效率包括分区剪枝、索引扫描等，可根据实际业务场景灵活处理。