GBase 8a集群一个开窗全表排序语句导致磁盘IO瓶颈问题分析

概述
 

本文记录某项目GBase 8a集群一个开窗全表排序语句导致磁盘IO瓶颈问题的分析过程及优化举措。

集群环境

集群版本：GBase 8a V862-Build33-R11
集群结构：14个data节点，5个coor节点（复合节点）

异常现象

集群1月2日17点30分左右，集群报警26节点gbased无响应，应用厂商反馈集群任务卡住，持续约10分钟后自动恢复。

问题分析
1）集群运行总体正常，无组件宕机，硬件暂未发现异常
2）26节点nmon日志显示，异常时段sdb的磁盘DisyBusy接近100%，导致集群任务短暂卡住
3）根据nmon日志的磁盘IO瓶颈时间以及集群任务日志分析，初步判断造成异常的大SQL如下: 
select /*+ to('16066') */  substr(to_char(case when mod(row_number() over(partition by 1 order by 1),99999999)=0 then 999999999 else mod(row_number() over(partition by 1 order by 1),99999999) end),1,8) 
, nvl(to_char(OPERTYPE),'') , nvl(to_char(USERID),'') , nvl(to_char(BASICOPERATOR),'') , nvl(to_char(PAYMENTMETHOD),'') 
.....
, nvl(to_char(COMPANYTELEPHONE),'') , nvl(to_char(COMPANYFAX),'') , nvl(to_char(AES_DECRYPT(unhex(CORPORATIONNAME),'7754321')),'') , nvl(to_char(CORPORATIONCERTTYPE),'') , nvl(to_char(AES_DECRYPT(unhex(CORPORATIONCERTNUMBER),'7754321')),'') , nvl(to_char(AES_DECRYPT(unhex(CORPORATIONCERTADDRESS),'7754321')),'') , nvl(to_char(AES_DECRYPT(unhex(CORPORATIONPOSTERADDRESS),'7754321')),'') , nvl(to_char(CORPORATIONTELEPHONE),'') , nvl(to_char(AES_DECRYPT(unhex(OPERATORNAME),'7754321')),'') , nvl(to_char(OPERATORCERTTYPE),'') , nvl(to_char(AES_DECRYPT(unhex(OPERATORCERTNUMBER),'7754321')),'') , nvl(to_char(AES_DECRYPT(unhex(OPERATORCERTADDRESS),'7754321')),'') , nvl(to_char(AES_DECRYPT(unhex(OPERATORPOSTERADDRESS),'7754321')),'') , nvl(to_char(OPERATORTELEPHONE),'') , nvl(to_char(EXTENDPARA),'') , nvl(to_char(USERTYPE),'') 
from mi_202312_02289_006 into outfile '/d1_data1/sccrm/bass/data/202312_02289_006' 
character set gbk fields length '8,1,30,1,1,5,20,4,20,20,32,1,500,1,2,15,14,14,14,14,1,20,160,20,100,3000,1,1,1,1,8,1,11,8,1024,
该SQL执行时间与sdb磁盘IO瓶颈时间段一致（17点27分至18点18分），持续了约50分钟（节点夯住持续了约10分钟，后自动回复），在异常时间段，只有这一条长SQL，其他SQL未发现明显异常
4）集群并发任务数不大，约15

优化举措

1）引起异常的SQL为开窗全表排序语句，导致单节点磁盘IO消耗较大（全表排序必须将集群各节点数据拉到一个节点汇总再排序），SQL没有明显优化空间（如业务上必须全表排序的话）
2）如果不是业务上必须，开窗排序语句最好选择表的哈希分布键进行partition，同时选择合理的排序字段（最好不要全表排序），以提升SQL效率、降低资源消耗
3）表结构，有个别字段长度达到3000，行长较大，建议尽量减小字段长度定义以降低行长
4）可能与磁盘使用量较高或操作系统的文件管理机制有一定关系，建议尽量清理磁盘历史数据
5）类似现象，目前建议等待或稍后重试，持续观察