摘要:路径生成是表关联方式确定的主要阶段,本文介绍了几个影响路径生成的要素:cost_param, scan方式,join方式,stream方式,并从原理上分析如何干预路径的生成。

一、cost模型选择

顾名思义,cost_param是控制cost相关的一个参数。在了解cost_param之前,先回顾一下选择率的概念,GaussDB优化器中的选择率是指,当一个表有一个过滤或关联条件时,通过该条件能被选中的行数占总行数的比例,是介于0~1之间的一个实数。选择率在优化器中是一个重要的概念,主要应用于行数和distinct值的估算,行数和distinct值是计划生成中的基本要素。

首先,我们来看带有过滤条件的基表行数如何估算。如果一个表只有一个过滤条件,那么以选择率乘以表的行数,即可得到过滤完的行数;如果有多个过滤条件,那么就需要算出一个综合的选择率,如何计算?方式有二:一是通过多列统计信息直接计算,二是通过组合单列的选择率。那么组合的方式就由参数cost_param决定了,具体地,

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_性能调优

举一个例子,TPC-H 1x的part表,过滤条件是:p_brand = 'Brand#45' and p_container = 'WRAP CASE',查看不同cost_param下的过滤后行数。

(1)cost_param=0

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_GaussDB_02

(2)cost_param=2

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_性能调优_03

从估算出的行数(E-rows)和实际的行数(A-rows)对比可以看出,cost_param=0的不相关模型适合part表的p_brand和p_container列。

其次,Join的行数怎么估算的呢?原理跟过滤条件的行数估算是类似的,如果没有多列统计信息可以使用,则也需要单独计算每个条件的选择率,然后计算出综合选择率,得出行数。例如 TPC-H 1x lineitem和orders关联,关联条件是:l_orderkey = o_orderkey and o_custkey = l_suppkey,不同cost_param的执行情况如下:

(1)cost_param=0

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_GaussDB_04

(2)cost_param=2

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_性能调优_05

此例中,Join的列之间也适合完全相关模型,这与l_orderkey和l_suppkey的分布是吻合的。

由于TPC-H的模型接近完全不相关模型,因此cost_param=0模型可以较好的描述场景,实际应用中,用户可以根据具体业务场景来调整模型,行数估算的准确性是计划生成的重要保证,在调优中检查估算的最直接的地方。GaussDB会在后续版本中新增更多的模型供业务需求选择。

二、Scan方式的选择

GaussDB中扫描方式主要分顺序扫描和索引扫描,每种扫描方式都对应若干扫描算子,顺序扫描在行列存中对应的扫描算子分别是Seq Scan和CStore Scan算子(下面我们讨论中不加区分)。这些扫描算子大部分都可以通过开关来进行调控,例如Seq Scan,如果设置enable_seqscan=off,则表示不会优先选择Seq Scan,而不是一定不会选。扫描方式的选择,很大程度上决定了获取基表数据的路径。我们以如下的例子来说明:

select l_orderkey, o_custkey from lineitem, orders where l_orderkey = o_orderkey;

lineitem分布键是l_orderkey,并且在l_orderkey上有index,orders分布键是o_orderkey。默认情况下,Scan的方式如下:

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_GaussDB_06

两个表都是顺序扫描的路径,关联方式选择了Hash Join。如果把Seq Scan关掉(enable_seqscan=off),计划如下:

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_性能调优_07

lineitem的扫描变成了Index Only Scan(因为l_orderkey的类型是int),而在orders表上仍然选择Seq Scan(因为没有其他路径),同时关联方式也变为了Nest Loop,因为Hash Join需要全表扫描数据(lineitem的Seq Scan已经被关掉了)。优化器的选择方式我们从代价(E-costs)一栏中也可以看出。再把Index Only Scan关掉,看看计划如何变化:

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_GaussDB_08

扫描路径都变为了Seq Scan,而且Seq Scan的代价都很大。此时既然都走了Seq Scan,为什么不选Hash Join呢,把Nest Loop关掉,看看Hash Join计划的代价:

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_性能调优_09

从代价上看出Hash Join的总代价比Nest Loop的小,但优化器没有选择Hash Join,这是因为优化器比较路径代价时,会比较Startup和Total代价,即启动代价和总代价,综合考虑,E-costs栏中显示的是总代价。把explain_perf_mode设置为normal,查看原Nest Loop的启动代价:

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_GaussDB_10

红框中的两个cost,分别是启动代价和总代价,在看Hash Join的cost,明显Hash Join的启动代价比Nest Loop的大很多(启动代价代表了输出第一条数据的代价),优化器在比较路径时,综合了这两个代价,最终推荐了Nest Loop的路径。

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_性能调优_11

从上面的例子可以看出,扫描路径的调控,可以改变路径生成,合理的搭配是生成最优计划的前提,默认情况下,GaussDB优化器可以根据现有的路径选择(如上面的lineitem有两条扫描路径,orders只有一条扫描路径),最后确定出最优的一条。两条路径代价比较时,总代价不是唯一要素,但总代价越小,一般也会越容易被选中。

三、关联方式的选择

GaussDB优化器中表关联的主要方式有:Nest Loop,Hash Join和Merge Join,分别可以通过enable_nestloop、enable_hashjoin、enable_mergejoin进行控制,这种控制也不是绝对的,可以理解为是否优先选择。大部分场景下,三种路径的代价关系:Hash Join < Merge Join < Nest Loop。我们以一个简单的关联示例说明,store_returns和store_sales是TPC-DS 1x中两个表,SQL如下:

select count(*) from store_returns, store_sales where sr_customer_sk = ss_customer_sk;

默认情况下,优化器推荐Hash Join路径,计划如下:

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_GaussDB_12

如果把Hash Join关掉,则优化器选择了Merge Join路径:

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_性能调优_13

如果再把Merge Join路径关掉,可能就会选择Nest Loop路径。关联方式的控制开关一般用于调优或规避问题,但具体是否能够起作用要看具体的语句,除了当前关联方式,还有没有其他方式。实际场景中,一个语句中关联的算子较多,一般很难用参数enable_hashjoin或enable_nestloop或enable_mergejoin来控制某两个表的Join方式,GaussDB中更细致的语句级别的调优手段是Plan Hint,感兴趣的读者可以参考产品手册。

四、Stream方式的选择

Stream算子是GaussDB分布式执行的关键算子之一,主要起到网络传输的作用, 概要介绍可以参考:GaussDB(DWS)性能调优系列实战篇一:十八般武艺之总体调优策略。 Stream算子由参数enable_stream_operator控制,如果关掉Stream算子,则可能导致生成不下推的计划,例如:

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_GaussDB_14

因为lineitem表关联的键l_partkey不是lineitem的分布键,需要添加Stream算子,但Stream功能被禁,于是只能生成不下推计划。

GaussDB计划中常见的主要Stream算子包括Redistribute、Broadcast和Gather。Gather一般是分布式计划中,CN用于收集DN的数据进行最后的处理,除非最后收集的行数非常多,这个算子涉及性能问题一般较少。Redistribute和Broadcast一是对“互补”的算子,前者用于重分布,后者用于广播,生成计划时,优化器会根据代价大小来选择。当Join Key没有包含表的分布键的时候,一般会添加Redistribute路径,能选择Redistribute路径理论上也可选择Broadcast路径,最终选择哪条路径要看优化器估算的代价是多少。这两个算子可以通过参数enable_redistribute和enable_broadcast进行控制。

在SMP开启的情况下,当并行度(dop)大于1时,一般还会有Local Redistribute、Split Redistribute、Local Broadcast和Split Broadcast;当倾斜优化开启时,还有PART REDISTRIBUTE PART ROUNDROBIN、PART_REDISTRIBUTE_PART_BROADCAST、PART_REDISTERIBUTE_PART_LOCAL等等,这些也是Stream算子,主要就是重分布、广播、RoundRobin的一些扩展形式,这里我们不一一介绍了,感兴趣的读者可以参考GaussDB DWS 产品手册。

我们考虑两个表的简单关联,store_sales和sr_tbl,它们的分布键分别是ss_item_sk 和sr_returned_date_sk,Join 条件是store_sales.ss_customer_sk =sr_tbl. sr_customer_sk,执行结果如下:

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_GaussDB_15

由于两个表的分布键都不是Join Key,因此走Hash Join路径的话需要有一个表做Broadcast或者两个表都做Redistribute,但是store_sales表比较大(E-rows显示28.7亿行),而sr_tbl表行数估算比较少(E-rows显示100行),优化器认为适合做Broadcast。于是最终选择了一边Broadcast的计划。

对于这个计划,由于sr_tbl表统计信息不准确(如果是中间结果集,则表示中间结果集估算不准),一种调优的方法是,将sr_tbl的表统计信息重新收集准确一些(如果sr_tbl是中间结果集,则无法收集),另一种方法是让sr_tbl走Redistribute路径,而后者我们又有两种方式来实现,一是用Plan Hint,即在生成计划时,告诉优化器走Redistribute路径,二是把Broadcast关掉。禁用Broadcast后,执行计划如下:

十八般武艺玩转GaussDB(DWS)性能调优:路径干预_性能调优_16

本列中,开启了SMP自适应,即优化器会根据系统资源和当前Active SQL数量来自行决定并行度(dop),如果Redistribute和Broadcast选择不当,则可能导致

(1)Broadcast计划会出现下盘

(2)两个计划的并行度不一样,最终执行时间可能会差异比较大。

对于Stream方式的控制,一般的调优方式有Plan Hint、GUC参数、改善统计信息或估算信息。

五、结束语

本文介绍的cost_param属于cost底层参数,建议对数据特征和使用场景比较熟悉的DBA慎重使用。Scan、Join、Stream调控的基本依据也是代价,代价一般体现在执行耗时上,调优时可从Performance中识别出性能的瓶颈点,分析选择的算子是否与代价匹配。另外,除了本文介绍的Session级别的控制参数外,还有基表、中间结果的行数,也可以通过Plan Hint进行语句级别的调控,感兴趣读者可通过GaussDB DWS产品文档进一步了解。

本文分享自华为云社区《GaussDB(DWS)性能调优系列实战篇五:十八般武艺之路径干预》,原文作者:- 大道至简 - 。