前言:
众所周知,ob是分布式数据库,所以sql避免不了会有远程和分布式执行计划。
前一段时间业务遇到一条sql超时,执行效率很低,反馈过来要求优化排查。
处理过程:
因为我们有几套核心系统业务类型基本一样,去另一套ob库查询该sql的执行历史,发现并不慢,对比执行计划也一样,只是远程算子位置不一样,手工将该sql涉及的表副本leader切到同一台server上,将他解决。
思路分析:
测试环境:OceanBase 3.2.3.2 (r105000062022090916-4dc1f420f94fe716a60cce42b110437c4aad731e
生产环境:OceanBase 3.2.3.3 (r107060012023041113-3cfe0f0783ae40aa8a41ec7d074ab7dedb93f702
因为该问题可以稳定复现,所以处理后去测试分析。
sql文本脱敏后如下
obclient> Select count(*)
-> from PPPPDDDDD t
-> WHERE (t.CCCCCCID = 'SSS.AA' or exists
-> (Select 1
-> FROM OOOOOOOO_CCCCC
-> Where PAAAID = 'SSS.AA'
-> and SUUUUUID = t.CCCCCCID) or exists
-> (select 1
-> from PPPPDDDD_GG B
-> where exists (select 1
-> from OOOOOOOO_CCCCC
-> where SUUUUUID = 'SSS.AA'
-> and PAAAID = b.OGGID)
-> or exists (select 1
-> from OOOOOOOO_CCCCC
-> where PAAAID = 'SSS.AA'
-> and PAAAID = b.OGGID)
-> and b.status = 1
-> and b.POOID = t.POOID))
-> AND t.ISSSSSTION = 0
-> And Exists
-> (select 1
-> from PPPPDDD_IIIMENT i
-> where i.POOID = t.POOID
-> and i.RRRRON in (111, 123))
-> AND (t.PRDDDTYPE like '%' || 'Ptype_Pppson' || '%' or
-> t.PRDDDTYPE = 'Ptype_Wddd' or
-> t.PRDDDTYPE = 'Ptype_Apppp' or
-> t.PRDDDTYPE = 'Ptype_Prooo' or
-> t.PRDDDTYPE = 'Ptype_Prmmm' or
-> t.PRDDDTYPE = 'Ptype_FFF' or t.PRDDDTYPE = 'PppType_Ssss' or
-> (t.PRDDDTYPE = 'Ptype_Unnnnn' and exists
-> (select 1
-> from PP_RRR_UNNN_CCCSI a
-> where a.uniiiiiiid = 'ALL'
-> and a.solllluuuoid = t.POOID)))
-> AND (t.PRDDDTYPE <> 'REwriterticty')
-> and t.auuuustatus in (1, 2)
-> AND t.MAAAAROD = 0
-> AND nvl(t.enddate, sysdate + 1) > sysdate
-> and t.Status = 1
-> Order by t.POOID;
测试环境进行了3次测试,为了方便下文分别称PPPPDDDDD为t表,PPPPDDDD_GG为B表,OOOOOOOO_CCCCC为C表,PPPPDDD_IIIMENT为i表,PP_RRR_UNNN_CCCSI为a表,生产出问题时B和C表副本不在一个server,
>当5张表的主副本在一个server上时,在测试环境执行是8s
>当b,a两表leader副本在一个server,另外3张表的leader在另一个server上,通过2881分别直连两个节点的效率分别是1分钟和14.5分钟,使用2883默认连接的时候执行时间是14.5分钟
说明下b表10w数据,c表1000w数据,a表600数据,t表8w数据,i表6w数据。
因为我们做了多次测试,暂时不详细说了,简单介绍下
select 1
-> from PPPPDDDD_GG B
-> where exists (select 1
-> from OOOOOOOO_CCCCC
-> where SUUUUUID = 'SSS.AA'
-> and PAAAID = b.OGGID)
-> or exists (select 1
-> from OOOOOOOO_CCCCC
-> where PAAAID = 'SSS.AA'
-> and PAAAID = b.OGGID)
-> and b.status = 1
单独把B,C的关联条件拿出来,执行很快,0点几秒,走的hash,因为他在原sql中是两层exist嵌套没办法转换成hash连接,用hint指定nest_loop仍然很快,去除B,C的条件后原语句执行也很快,结果160条数据。正是这160条数据导致我一开始感觉走filter效率也会很快。
那么下面对比下执行计划
通过对比可以看到慢的执行计划15-17号是个远程算子,问题主要出在这里。limit截断算子正常是流式执行,外层传参进入内层,匹配到一条数据后就会停止,继续下面的执行,但是右面慢的执行计划有个exchange的远程查询推入,会进行查询重写,相当于外层传参一次,b表会全表数据拿出来进行一次比对,这个过程进行160次,导致变慢被放大。
结论:
因为分布式执行计划的特殊性,所以传统dba接触时会有一些忽略和知识上的盲区,需要通过不断的学习丰富自己。这个问题,在4.x最新版本这个机制会有优化,现在是计算下压,下压到不同server上去执行,所以外层驱动每传参一次就会去协调者节点查询重写一次,相当于一次新的执行,现在ob的新版本中会将远程节点数据拉取到协调者节点,就可以避免多次的查询重写。我们这种迁移副本的处理方式需要关闭rebalance参数避免分区自动均衡回来,长时间关闭该参数可能造成节点数据不均衡,当然这个问题还有别的解决方法,比如去改写语句,将bc关联的这部分条件改写到上一层,让优化器自动改写走hash连接,也可以规避。
行之所向,莫问远方。