当前位置: 首页 > 科技观察

新一代云原生数据仓库AnalyticDB“SQL智能诊断”功能详解

时间:2023-03-18 22:42:45 科技观察

SQL是一种简单易用的业务逻辑表达语言,但随着扫描数据量和查询复杂度的增加,查询性能会变得越来越退化。慢点。如果要调优SQL,往往需要关注以下几个部分:需要了解引擎架构:用户往往需要对SQL引擎的架构特点有一定的了解,才能与数据分布完美结合业务的特点和业务场景的特点。进行数据建模,设计出符合SQL引擎架构特点的表结构。SQL的特点则大不相同:即席查询的SQL往往变化很大,包括参与Join的表数、Join条件、分组聚合的字段数、过滤条件等。数据特征差异较大:用户的数据分布特征会随着业务特征的变化而变化。如果一直沿用原有的建模方式和SQL语句,则无法保证SQL引擎的最大优势得以发挥。数据特性或业务模型的变化会导致SQL性能回滚。基于此,AnalyticDBForMySQL(新一代云原生实时数据仓库,语法兼容MySQL,以下简称ADB)为用户提供高效、实时、功能丰富、智能的“SQL智能诊断””和“SQL智能调优”功能,为用户提供SQL性能调优的思路、方向和具体方法,降低用户成本,提高用户使用ADB的效率。接下来,我们将通过“发现慢查询”+“诊断慢查询”两个步骤,结合一个场景Case来介绍ADB新发布的“SQL智能诊断”功能。(PS:《SQLSmartTuning》将在后续版本发布)1、查找慢查询。要定位慢查询,用户首先需要找到慢查询。ADB的用户控制台提供了多种方式帮助用户,例如“甘特图”和“查询列表”,可以多维度搜索,帮助用户快速定位慢查询,诊断工具保证用户可以进行全查询检索和过去两周的分析。(1)甘特图用户可以通过“集群控制台”-“诊断与优化”-“SQL诊断”进入SQL智能诊断功能。首先,你会看到一个甘特图(也称为泳道图,查询流经不同的泳道。这里的泳道不是ADB查询队列,而是为了区分不同时间执行的查询)。甘特图以图形方式展示查询在ADB实例上的执行顺序。每个色块代表一个查询。色块左侧是查询的提交时间,色块右侧是查询结束时间。色块的相对长度表示某次查询的执行时间,色块的颜色没有特殊意义,只是为了区分不同的车道。通过甘特图,用户可以直观的看到当前时间范围内执行时间较长的查询,并且可以直观的看到哪些查询是并行执行的,以及并行执行的时间段,可以帮助用户判断哪些查询是并行执行的受BadSQL影响。通过色块的疏密程度可以直观判断ADB实例的高压时段是否与某些查询的高并发相关。(2)查询列表甘特图可以直观的反映查询之间的时间相关性,但是当用户选择的时间范围较大时,甘特图中的色块会分布密集,难以区分,而甘特图图中指标有限,此时用户可以使用诊断工具中的查询列表功能。查询列表提供了10多个查询级别的重要指标,如数据库名、用户名、客户段IP、耗时、内存消耗、扫描量等,这些信息和指标可以帮助用户进一步判断来源和来源。慢查询的起源。资源消耗等。在高级搜索能力方面,诊断工具提供了三种搜索方式:1.模糊搜索和精确搜索:用户可以根据SQL中的关键字进行模糊匹配,精确搜索功能帮助用户确定查询ID。准确检索此查询;2、字符串型检索条件:检索工具会自动识别用户选择的时间范围内使用的数据名、用户名、客户端IP、资源组,并提供下拉框供用户选择,以提高用户检??索效率;3、数值型检索条件:用户可以自由选择检索的索引单位,如KB、MB、GB等,无需人工换算。同时,用户在使用诊断工具时,往往会有下载慢查询的需求。下载慢查询后,他们可以在Excel等工具中进行更多定制化的慢查询管理和分析,所以我们也提供了查询列表。下载功能。2.慢查询的诊断(1)ADB中查询执行过程在介绍ADB执行过程之前,需要简单介绍三个相关的基本概念:Stage在执行阶段,ADB中的查询首先会根据是否Shuffle进行分段为多个Stage生成Execute,一个Stage是执行计划中某一部分的物理实体。Stage的数据源可以是底层存储系统中的数据,也可以是网络中传输的数据。一个阶段由分布在不同计算节点上的相同类型的任务组成,多个任务将并行处理数据。TaskTask是Executor节点上Stage的执行实体。多个相同类型的Task组成一个Stage,在集群内并行处理数据。OperatorOperator(运算符)是ADB最小的数据处理单元。ADB会根据算子表达的语义或者算子之间的依赖关系来决定是使用并行执行还是串行执行来处理数据。下面以一个典型的分支聚合查询为例,来了解查询在ADB中的执行过程。SQL语句如下:SELECTCOUNT(*),SUM(salary)FROMemplayeeWHEREage>30ADNage<32GROUPBYsex在ADB内部,前端Controller节点首先收到SQL语句Request,对SQL进行语句解析和语法分析(Parser)语句,最后使用优化器(Optimizer)生成最终的执行计划。整体执行计划会按照阶段划分原则划分为子计划,如Plan0、Plan1和Plan2分别下发到相应的节点。其中,子计划Plan2会在四个计算节点上以Task实例的形式并行处理数据。首先对数据进行扫描过滤,然后对数据进行部分聚合。数据处理后会根据性别字段重新分区给下游计算。该节点,即Stage1的节点,根据子计划Plan1的要求进行数据的最终聚合。最后,数据由Stage0的节点聚合并返回给客户端。和典型的数据仓库一样,ADB的执行计划一般分为“逻辑执行计划”和“物理执行计划”:逻辑执行计划:从宏观层面理解查询的处理流程逻辑执行计划展示了查询的处理逻辑在更高层次上,基于规则的执行计划(RBO)会判断过滤条件是否可以下推,基于成本的执行计划(CBO)会判断多表关联的顺序等。因此,逻辑执行计划不关注物理执行时的具体处理方式,比如执行时是否需要融合多个算子来减少函数调用,或者自动生成代码的粒度。这些逻辑执行计划不用注意。因此,逻辑执行计划通常只包含阶段级别的执行统计信息。然而,用户在调优时往往需要精确到操作员级别的统计信息。物理执行计划:从微观层面了解各个算子的处理性能。与逻辑执行计划相比,物理执行计划包括算子之间的数据处理流图和算子的执行统计。还可以查看Join算子或聚合算子占用的内存,也可以查看filter算子过滤前后的数据量。但是,并不是所有的算子用户都需要能够正确理解其含义,尤其是一些物理算子是找不到与用户的SQL语句有任何联系的,这也会给单独使用物理执行计划的用户带来很大的问题。疑惑。ADB的“SQL智能诊断”功能为用户提供了逻辑执行计划和物理执行计划的融合视图。用户可以使用融合执行计划从宏观层面理解查询处理流程,从微观层面理解每一个查询。算子的处理性能可以帮助用户更准确、更快速地定位查询的性能瓶颈。(2)SQL自诊断功能虽然我们提供了集成分层的执行计划来帮助用户分析查询性能问题,但是我们发现用户在使用集成执行计划时会遇到两类场景的困难:主要使用ADB为了减少MySQL用户的学习和迁移成本,ADB已经让大部分语法兼容MySQL,但是ADB的后台并不是MySQL内核,而是自己独立开发的一套分布式数据存储和分布式计算系统。ADB的执行计划,ADB的初级用户往往不知道优化的重点在哪里,无从下手。ADB中的复杂SQL对于复杂的SQL,往往涉及到数百张表的连接操作,stage的数量会达到几百个以上,operator的数量会达到数千个。执行计划图非常大,即使是ADBAdvanced用户也常常无从下手这么复杂的执行计划。下图是196个Stages的执行计划图:针对以上问题,我们在执行计划图中增加了SQL自诊断能力。SQL自诊断能力专家的经验会以规则的形式体现在执行计划中。对于初次接触ADB的用户,可以根据诊断结果确定查询执行过程中的性能瓶颈点,并根据诊断结果学习ADB执行计划。关键操作员。对于复杂的执行计划,SQL自诊断可以帮助用户快速定位到执行计划中需要调优的位置,并提供相关的调优方法和文档,让用户在调优过程中更有针对性。SQL自诊断能力通过“Query级诊断结果”、“Stage级诊断结果”、“Operator级诊断结果”三层展示诊断结果和优化建议。我们以一条线上的复杂SQL为例,介绍一个使用执行计划和SQL自诊断工具来定位性能问题的例子。首先,我们通过慢查询检索工具搜索到一个消耗大量内存的查询,点击“诊断”打开该查询的诊断页面,切换到“执行计划”选项卡,我们会看到当前查询有通过查询级别的诊断结果判断数据是一个消耗内存比较大的查询,如下图1所示:这时候我们需要定位大内存效应的原因。我们点击SortbyMemory,可以看到右边会按照Stage消耗内存的百分比进行反向排序,可以清楚的看到Stage[1]占用了87%以上的内存当前查询的内存。当我们点击Stage[1]时,诊断工具会自动关注Stage[1]在执行计划树中的位置,点击Stage[1],我们可以看到Stage[1]的执行统计,在同时,我们可以看到5处的位置,提示我们Stage1中有一个运算符占用大量内存,但是没有详细信息,所以接下来,我们需要进入Stage[1]的内部查看Stage[1]中哪个运算符占用的内存较多。点击“查看阶段执行计划”进入阶段[1]。首先,我们还是按照内存排序。可以看到Join[317]运算符占用了整个Stage内存的99%以上。点击操作员操作员执行计划树自动定位到当前操作员。此时我们可以看到操作员诊断结果的详细信息。信息提示,在构造Hash表userLeftJoin时,会占用大量内存。诊断结果还提供了官方调优文档的链接。根据文档中给出的调优方法,我们可以降低算子的内存占用。在上面的例子中,SQL性能问题是通过“查询级诊断结果”和“运算符级诊断结果”来定位的。让我们看一个“阶段级诊断结果”的例子。如下图所示,我们可以看到按照耗时排序后,Stage[10]的耗时比例最大。点击执行计划图中的Stage[10],在诊断结果栏可以看到两种诊断结果,一种是“StageDiagnosis”,一种是“OperatorDiagnosis”,其中StageDiagnosis告诉我们,当前Stage的输出数据是倾斜的,哪些字段是倾斜的(数据倾斜是分布式系统中严重影响性能的问题),stage输出数据倾斜不仅会导致当前stage处理数据在时间上出现长尾,还会导致下游数据处理出现长尾现象),同时我们可以看到有算子诊断结果,说明表扫描存在偏斜,那么我们可以初步判断当前Stage输出数据倾斜是由扫描具有倾斜数据的表引起的。接下来我们进入Stage[0]进行定位分析。进入stagememory,我们可以看到TableScan算子按照耗时排序耗时最多。这时我们点击TableScan算子,可以看到在诊断结果中,有详细的表数据倾斜的诊断信息。由于张表数据分布字段选择不当,存在严重的数据倾斜问题。同时,我们可以看到相关的官方调优文档。根据调优文档,我们可以调整到合适的分布字段,减少表数据倾斜的影响。对查询性能的影响。通过上面两个例子我们可以看出,执行计划和SQL自诊断功能的结合可以快速帮助我们定位查询的性能问题,并给出一定的调优建议,减少了很多不必要的时间和精力的浪费,降低初级用户使用ADB的门槛。SQL自诊断更多的诊断结果可以参考官网文档:SQL自诊断,目前在线有20+条诊断规则,涉及查询相关的内存消耗,耗时,数据倾斜,磁盘IO,以及执行计划。将来会添加更多诊断规则。3.后续规划通过上面的描述和实例分析,我们可以看出,目前的诊断和调优工具已经可以帮助用户排查各个方面的SQL性能问题,但是在实际的在线问题处理和值班中,我们还是发现了总结多用户分析实例性能问题时的需求:我该调优哪个SQL?当用户打开诊断调优页面时,面对实例上运行的数万条甚至上千万条SQL,虽然可以通过耗时排序、耗内存排序、扫描等方式初步筛选出需要调优的SQLvolume,但实际上,用户缺乏对某个具体诊断结果的视角,例如:哪条SQL被数据扫描偏斜了?哪个SQL对索引过滤效率不高?哪个SQL是该阶段的输出偏斜?分区选择不合理的SQL有哪些?用户调优完某条SQL的具体诊断结果后,其实需要知道有哪些类似的SQL需要调优,所以我们会为用户提供一个工具,从具体诊断结果的维度进行分析,一-时间解决一个特定的问题。我的SQL有问题,是否与创建表的方式不当有关?ADB后台是一个分布式数据存储和分布式执行框架,依赖数据均匀分布到各个后台节点。同时,ADB针对不同的业务场景设计了不同的表类型,比如分区表、复制表、部分表等。存储时对字段进行聚合存储也会提高查询性能,但用户往往不知道哪些查询受到糟糕的建表方式的影响。以后我们会将“数据建模诊断结果”与“查询诊断结果”关联起来。用户可以通过数据建模的诊断结果,快速知道哪些SQL受到了不良表结构的影响。诊断结果知道哪些表需要优化。对两类诊断结果进行联合优化,可以从根源上解决实例的查询性能问题。