实战案例丨GaussDB for DWS如何识别坏味道的SQL

摘要:SQL中的坏味道,你知道吗?

SQL语言是关系型数据库(RDB)的标准语言,其作用是将使用者的意图翻译成数据库能够理解的语言来执行。人类之间进行交流时,同样的意思用不同的措辞会产生不同的效果。

类似地,人类与数据库交流信息时,同样的操作用不同的SQL语句来表达,也会导致不同的效率。而有时同样的SQL语句,数据库采用不同的方式来执行,效率也会不同。那些会导致执行效率低下的SQL语句及其执行方式,我们称之为SQL中的“坏味道”。

下面这个简单的例子,可以说明什么是SQL中的坏味道。

图1-a 用union合并集合

在上面的查询语句中,由于使用了union来合并两个结果集,在合并后需要排序和去重,增加了开销。实际上符合dept_id = 1和dept_id > 2的结果间不会有重叠,所以完全可以用union all来合并,如下图所示。

图1-b 用union all合并集合

而更高效的做法是用or条件,在扫描的时候直接过滤出所需的结果,不但节省了运算,也节省了保存中间结果所需的内存开销,如下图所示。

图1-c 用or条件来过滤结果

可见完成同样的操作,用不同的SQL语句,效率却大相径庭。前两条SQL语句都不同程度地存在着“坏味道”。

对于这种简单的例子,用户可以很容易发现问题并选出最佳方案。但对于一些复杂的SQL语句,其性能缺陷可能很隐蔽,需要深入分析才有可能挖掘出来。这对数据库的使用者提出了很高的要求。即便是资深的数据库专家,有时也很难找出性能劣化的原因。

GaussDB在执行SQL语句时,会对其性能表现进行分析和记录,通过视图和函数等手段呈现给用户。本文将简要介绍如何利用GaussDB提供的这些“第一手”数据,分析和定位SQL语句中存在的性能问题,识别和消除SQL中的“坏味道”。

◆ 识别SQL坏味道之自诊断视图

GaussDB在执行SQL时,会对执行计划以及执行过程中的资源消耗进行记录和分析,如果发现异常情况还会记录告警信息,用于对原因进行“自诊断”。用户可以通过下面的视图查询这些信息:

• gs_wlm_session_info

• pgxc_wlm_session_info

• gs_wlm_session_history

• pgxc_wlm_session_history

其中gs_wlm_session_info是基本表,其余3个都是视图。gs_开头的用于查看当前CN节点上收集的信息,pgxc_开头的则包含集群中所有CN收集的信息。各表格和视图的定义基本相同,如下表所示。

名称

类型

描述

datid

oid

连接后端的数据库OID。

dbname

text

连接后端的数据库名称。

schemaname

text

模式的名字。

nodename

text

语句执行的CN名称。

username

text

连接到后端的用户名。

application_name

text

连接到后端的应用名。

client_addr

inet

连接到后端的客户端的IP地址。 如果此字段是null,它表明通过服务器机器上UNIX套接字连接客户端或者这是内部进程,如autovacuum。

client_hostname

text

客户端的主机名,这个字段是通过client_addr的反向DNS查找得到。这个字段只有在启动log_hostname且使用IP连接时才非空。

client_port

integer

客户端用于与后端通讯的TCP端口号,如果使用Unix套接字,则为-1。

query_band

text

用于标示作业类型,可通过GUC参数query_band进行设置,默认为空字符串。

block_time

bigint

语句执行前的阻塞时间,包含语句解析和优化时间,单位ms。

start_time

timestamp with time zone

语句执行的开始时间。

finish_time

timestamp with time zone

语句执行的结束时间。

duration

bigint

语句实际执行的时间,单位ms。

estimate_total_time

bigint

语句预估执行时间,单位ms。

status

text

语句执行结束状态:正常为finished,异常为aborted。

abort_info

text

语句执行结束状态为aborted时显示异常信息。

resource_pool

text

用户使用的资源池。

control_group

text

语句所使用的Cgroup。

min_peak_memory

integer

语句在所有DN上的最小内存峰值,单位MB。

max_peak_memory

integer

语句在所有DN上的最大内存峰值,单位MB。

average_peak_memory

integer

语句执行过程中的内存使用平均值,单位MB。

memory_skew_percent

integer

语句各DN间的内存使用倾斜率。

spill_info

text

语句在所有DN上的下盘信息:

None:所有DN均未下盘。

All: 所有DN均下盘。

[a:b]: 数量为b个DN中有a个DN下盘。

min_spill_size

integer

若发生下盘,所有DN上下盘的最小数据量,单位MB,默认为0。

max_spill_size

integer

若发生下盘,所有DN上下盘的最大数据量,单位MB,默认为0。

average_spill_size

integer

若发生下盘,所有DN上下盘的平均数据量,单位MB,默认为0。

spill_skew_percent

integer

若发生下盘,DN间下盘倾斜率。

min_dn_time

bigint

语句在所有DN上的最小执行时间,单位ms。

max_dn_time

bigint

语句在所有DN上的最大执行时间,单位ms。

average_dn_time

bigint

语句在所有DN上的平均执行时间,单位ms。

dntime_skew_percent

integer

语句在各DN间的执行时间倾斜率。

min_cpu_time

bigint

语句在所有DN上的最小CPU时间,单位ms。

max_cpu_time

bigint

语句在所有DN上的最大CPU时间,单位ms。

total_cpu_time

bigint

语句在所有DN上的CPU总时间,单位ms。

cpu_skew_percent

integer

语句在DN间的CPU时间倾斜率。

min_peak_iops

integer

语句在所有DN上的每秒最小IO峰值(列存单位是次/s,行存单位是万次/s)。

max_peak_iops

integer

语句在所有DN上的每秒最大IO峰值(列存单位是次/s,行存单位是万次/s)。

average_peak_iops

integer

语句在所有DN上的每秒平均IO峰值(列存单位是次/s,行存单位是万次/s)。

iops_skew_percent

integer

语句在DN间的IO倾斜率。

warning

text

显示告警信息。

queryid

bigint

语句执行使用的内部query   id。

query

text

执行的语句。

query_plan

text

语句的执行计划。

node_group

text

语句所属用户对应的逻辑集群。

 

表1-自诊断表格&函数字段定义

 

其中的query字段就是执行的SQL语句。通过分析每个query对应的各字段,例如执行时间,内存,IO,下盘量和倾斜率等等,可以发现疑似有问题的SQL语句,然后结合query_plan(执行计划)字段,进一步地加以分析。特别地,对于一些在执行过程中发现的异常情况,warning字段还会以human-readable的形式给出告警信息。目前能够提供的自诊断信息如下:

◇多列/单列统计信息未收集

优化器依赖于表的统计信息来生成合理的执行计划。如果没有及时对表中各列收集统计信息,可能会影响优化器的判断,从而生成较差的执行计划。如果生成计划时发现某个表的单列或多列统计信息未收集,warning字段会给出如下告警信息:

Statistic Not Collect:
schemaname.tablename(column name list)

此外,如果表格的统计信息已收集过(执行过analyze),但是距离上次analyze时间较远,表格内容发生了很大变化,可能使优化器依赖的统计信息不准,无法生成最优的查询计划。针对这种情况,可以用pg_total_autovac_tuples系统函数查询表格中自从上次分析以来发生变化的元组的数量。如果数量较大,最好执行一下analyze以使优化器获得最新的统计信息。

◇SQL未下推

执行计划中的算子,如果能下推到DN节点执行,则只能在CN上执行。因为CN的数量远小于DN,大量操作堆积在CN上执行,会影响整体性能。如果遇到不能下推的函数或语法,warning字段会给出如下告警信息:

SQL is not plan-shipping, reason : %s

◇Hash连接大表做内表

如果发现在进行Hash连接时使用了大表作为内表,会给出如下告警信息:

PlanNode[%d] Large Table is INNER in HashJoin \"%s\"

目前“大表”的标准是平均每个DN上的行数大于100,000,并且内表行数是外表行数的10倍以上。

◇大表等值连接使用NestLoop

如果发现对大表做等值连接时使用了NestLoop方式,会给出如下告警信息:

PlanNode[%d] Large Table with Equal-Condition use Nestloop\"%s\"

目前大表等值连接的判断标准是内表和外表中行数最大者大于DN的数量乘以100,000。

◇数据倾斜

数据在DN之间分布不均匀,可导致数据较多的节点成为性能瓶颈。如果发现数据倾斜严重,会给出如下告警信息:

PlanNode[%d] DataSkew:\"%s\", min_dn_tuples:%.0f, max_dn_tuples:%.0f

目前数据倾斜的判断标准是DN中行数最多者是最少者的10倍以上,且最多者大于100,000。

◇代价估算不准确

GaussDB在执行SQL语句过程中会统计实际付出的代价,并与之前估计的代价比较。如果优化器对代价的估算与实际的偏差很大,则很可能生成一个非最优化的计划。如果发现代价估计不准确,会给出如下告警信息:

"PlanNode[%d] Inaccurate Estimation-Rows: \"%s\" A-Rows:%.0f, E-Rows:%.0f

目前的代价由计划节点返回行数来衡量,如果平均每个DN上实际/估计返回行数大于100,000,并且二者相差10倍以上,则认定为代价估算不准。

◇Broadcast量过大

Broadcast主要适合小表。对于大表来说,通常采用Hash+重分布(Redistribute)的方式效率更高。如果发现计划中有大表被广播的环节,会给出如下告警信息:

PlanNode[%d] Large Table in Broadcast \"%s\"

目前对大表广播的认定标准为平均广播到每个DN上的数据行数大于100,000。

◇索引设置不合理

如果对索引的使用不合理,比如应该采用索引扫描的地方却采用了顺序扫描,或者应该采用顺序扫描的地方却采用了索引扫描,可能会导致性能低下。

索引扫描的价值在于减少数据读取量,因此认为索引扫描过滤掉的行数越多越好。如果采用索引扫描,但输出行数/扫描总行数>1/1000,并且输出行数>10000(对于行存表)或>100(对于列存表),则会给出如下告警信息:

PlanNode[%d] Indexscan is not properly used:\"%s\", output:%.0f, filtered:%.0f, rate:%.5f

顺序扫描适用于过滤的行数占总行数比例不大的情形。如果采用顺序扫描,但输出行数/扫描总行数<=1/1000,并且输出行数<=10000(对于行存表)或<=100(对于列存表),则会给出如下告警信息:

PlanNode[%d] Indexscan is ought to be used:\"%s\", output:%.0f, filtered:%.0f, rate:%.5f

◇下盘量过大或过早下盘

SQL语句执行过程中,因为内存不足等原因,可能需要将中间结果的全部或一部分转储的磁盘上。下盘可能导致性能低下,应该尽量避免。如果监测到下盘量过大或过早下盘等情况,会给出如下告警信息:

• Spill file size large than 256MB

• Broadcast size large than 100MB

• Early spill

• Spill times is greater than 3

• Spill on memory adaptive

• Hash table conflict

下盘可能是因为缓冲区设置得过小,也可能是因为表的连接顺序或连接方式不合理等原因,要结合具体的SQL进行分析。可以通过改写SQL语句,或者HINT指定连接方式等手段来解决。

使用自诊断视图功能,需要将以下变量设成合适的值:

▲ use_workload_manager(设成on,默认为on)

▲ enable_resource_check(设成on,默认为on)

▲ resource_track_level(如果设成query,则收集query级别的信息,如果设成operator,则收集所有信息,如果设成none,则以用户默认的log级别为准)

▲ resource_track_cost(设成合适的正整数。为了不影响性能,只有执行代价大于resource_track_cost语句才会被收集。该值越大,收集的语句越少,对性能影响越小;反之越小,收集的语句越多,对性能的影响越大。)

执行完一条代价大于resource_track_cost后,诊断信息会存放在内存hash表中,可通过pgxc_wlm_session_history或gs_wlm_session_history视图查看。

视图中记录的有效期是3分钟,过期的记录会被系统清理。如果设置enable_resource_record=on,视图中的记录每隔3分钟会被转储到gs_wlm_session_info表中,因此3分钟之前的历史记录可以通过gs_wlm_session_info表或pgxc_wlm_session_info视图查看。

◆ 发现正在运行的SQL的坏味道

上一节提到的自诊断视图可以显示已完成SQL的信息。如果要查看正在运行的SQL的情况,可以使用下面的视图:

• gs_wlm_session_statistics

• pgxc_wlm_session_statistics

类似地,gs_开头的用于查看当前CN节点上收集的信息,pgxc_开头的则包含集群中所有CN收集的信息。两个视图的定义与上一节的自诊断视图基本相同,使用方法也基本一致。通过观察其中的字段,可以发现正在运行的SQL中存在的性能问题。

例如,通过“select queryid, duration from gs_wlm_session_statistics order by duration desc limit 10;”可以查询当前运行的SQL中,已经执行时间最长的10个SQL。如果时间过长,可能有必要分析一下原因。

图2-a 通过gs_wlm_session_statistics视图发现可能hang住SQL

查到queryid后,可以通过query_plan字段查看该SQL的执行计划,分析其中可能存在的性能瓶颈和异常点。

图2-b 通过gs_wlm_session_statistics视图查看当前SQL的执行计划

再下一步,可以结合等待视图等其他手段定位性能劣化的原因。

图2-c 通过gs_wlm_session_statistics视图结合等待视图定位性能问题

另外,活动视图pg_stat_activity也能提供一些当前执行SQL的信息。

◆ Top SQL——利用统计信息发现SQL坏味道

除了针对逐条SQL进行分析,还可以利用统计信息发现SQL中的坏味道。另一篇文章“Unique SQL特性原理与应用”中提到的Unique SQL特性,能够针对执行计划相同的一类SQL进行了性能统计。与自诊断视图不同的是,如果同一个SQL被多次执行,或者多个SQL语句的结构相同,只有条件中的常量值不同。这些SQL在Unique SQL视图中会合并为一条记录。因此使用Unique SQL视图能更容易看出那些类型的SQL语句存在性能问题。

利用这一特性,可以找出某一指标或者某一资源占用量最高/最差的那些SQL类型。这样的SQL被称为“Top SQL”。例如,查找占用CPU时间最长的SQL语句,可以用如下SQL:

select unique_sql_id,query,cpu_time from pgxc_instr_unique_sql order by cpu_time desc limit 10。

Unique SQL的使用方式详见https://bbs.huaweicloud.com/blogs/197299。

◆ 结论

发现SQL中的坏味道是性能调优的前提。GaussDB对数据库的运行状况进行了SQL级别的监控和记录。这些打点记录的数据可以帮助用户发现可能存在的异常情况,“嗅”出潜在的坏味道。从这些数据和提示信息出发,结合其他视图和工具,可以定位出坏味道的来源,进而有针对性地进行优化。

 

点击关注,第一时间了解华为云新鲜技术~

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/49040.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

GBase 8s 数据库监控(4)

5&#xff0e;Session 的连接情况 通过 Session 的连接信息&#xff0c;可以分析出数据库系统业务的负载情况以及来自哪些客户端的任务较多&#xff0c;并且根据 Session 的空闲情况判断客户端连接池是否存在过多的连接。查询 Session 连接情况的 SQL 语句如下。 SELECT s.sid…

一篇就够了-带你走进DB2分区

目录 先决条件&#xff1a; DB2数据库分区 1、概念描述 2、DPF对数据库性能产生的影响 3、DB2分区与Oracle的比较 4、总结 进入正文&#xff1a; 简介 特性概述 三个互补的 CREATE TABLE 选项 简要对比 互补特性 表设计 表设计的经验法则 设计的例子 再添上 MQ…

GBase8s 数据库实例化

1.切换到数据库用户gbasedbt su - gbasedbt 2.切换到目录/opt/gbase/etc, 执行实例化脚本GBaseInit_gbasedbt.sh: cd /opt/gbase/etc sh GBaseInit_gbasedbt.sh 3.实例名称使用默认值gbaseserver: ENTER THE INSTANCE INFORMATION or PRESS <ENTER> TO ACCEPT THE D…

DB2数据库降低高水位方案

https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0055399.html如何确认高水位&#xff1a;https://www.cndba.cn/hbhe0316/article/4796 db2pd -db testdb -tablespaces 如上图所示&#xff0c;这些表空间中的UsedPgs如果…

GBASE监控工具简介

统一监控主要包含采集代理、采集中心和监控网站三大部分功能模块&#xff0c;其中每个功能模块又由多个组件组成。 采集代理模块包含 GAgent 组件&#xff0c;需要部署在 GCluster 集群节点上。该模块负责采集集群节点的操作系统、磁盘、内存、 CPU、网络流量、节点运行…

HRTransNet阅读理解

E. Dual-direction short connection fusion module HRFormer applies transformer blocks to enlarge receptive field of fused feature Frs, and uses exchange units to absorb the merits of multi-scales features. The process is described as: HRFormer使用TRM块来扩…

考研英语阅读分析--03Text3

考研英语一阅读分析--03Text3 paragraph 1paragraph 2paragraph 3paragraph 4paragraph 5question 2question 3question 4question 5 本文章仅是个人对于文章的理解。以及记录错题的思路&#xff0c;如果对大家有什么启发我将不胜荣幸&#xff0c;如果小伙伴们有什么其他不同见…

【英语】大学英语CET考试,阅读部分2(长篇阅读,选词填空,综合演练)

文章目录 1、长篇阅读&#xff08;连连看&#xff0c;要会做&#xff09;1.1 解题技巧&#xff08;定位词扫读&#xff0c;看到大于看懂&#xff0c;一题带练&#xff09;1.2 做题方法复习总结1.3 题目练习&#xff08;2篇文章&#xff09; 2、选词填空&#xff08;只有5分&…

英语阅读(2)

基础阶段的文章练习 13 46 by … 通过… 47(cuo ) 48 49&#xff08;错&#xff09; nature 本质&#xff0c; i’ve broken eggs 打破鸡蛋 50&#xff08;错&#xff09; 14 46(没整出来&#xff0c;选项都没读懂) small non-food reward 小的非食物的奖励 stimulate …

TPO69 01|Why Snakes Have Forked Tongues P5P6|阅读真题精读|17:50~19:35

17:00&#xff5e;17:50 吃饭 目录 P56 生词 段落大意 P5段落大意 P6段落大意 题目 【5】事实信息题|定位准确非常重要✅ 【6】事实信息题|定位准确非常重要✅ 【7】推理题|文章是否提及|不要过度推理 【8】修辞目的题|举例一般为了说明✅ 【9】句子插入题|in other words|同义…

习题难度预测(英语阅读题)-Question Difficulty Prediction for READING Problems in Standard Tests

Question Difficulty Prediction for READING Problems in Standard Tests 一、基本信息 时间&#xff1a;2017年 会议&#xff1a;AAAI&#xff08;CCF推荐的A类会议 人工智能与模式识别&#xff09; 作者&#xff1a;黄振亚等&#xff08;陈恩红团队&#xff09; 机构&…

【英语】大学英语CET考试,阅读部分1(阅读概述,SectionC仔细阅读140)

文章目录 1、阅读概述1.1 考试概况&#xff1a;大纲解读备考策略1.2 做题原则&#xff1a;定位1.3 标点符号和句子逻辑1.4 一级词汇 2、细节题&#xff08;10题占9题&#xff09;2.1 逻辑关系&#xff08;并列和递进&#xff0c;同一方向&#xff09;2.2 逻辑关系&#xff08;转…

英语二 阅读专题

1.话题一:商业经济类 Part 1数字经济带来的挑战&反垄断问题 1.获利 2.打破科技巨头公司垄断Epic -呼吁监管强调科技公司&传统企业竞争FTC congress修订反垄断法保障工人利益角度Big tech大型科技公司 Police v.监督;管制 Monopoly n.垄断 monopolistic a.垄断的 corn…

聚观早报 |阿里清空印度支付宝Paytm股票;Meta终于成功收购Within

今日要闻&#xff1a;全球多所学校禁止学生使用ChatGPT&#xff1b;阿里清空印度支付宝Paytm股票&#xff1b;Meta终于成功收购Within&#xff1b;极氪完成 7.5 亿美元 A 轮融资&#xff1b;现代汽车在美电动汽车销量突破10万 全球多所学校禁止学生使用ChatGPT 2月12日消息&am…

蚂蚁集团入局 AI “百模大战”,定名“贞仪”!

整理 | 郑丽媛 出品 | CSDN&#xff08;ID&#xff1a;CSDNnews&#xff09; 由 ChatGPT 强势开局&#xff0c;这场突然爆发的 AI “百模大战”已持续了半年&#xff1a;期间&#xff0c;国外微软、谷歌、Meta 等巨头接连下场&#xff0c;国内百度、阿里、讯飞等大厂也陆续迎战…

测绘的行业标准规范和国家标准全部可下载

工作中我们经常需要参考一些测绘相关标准规范&#xff0c;一些规范在网上真是很难搜到&#xff0c;好不容易废了九牛二虎之力找到了还没看到一半就提示需要付费购买看全文…更别想打印出来随时翻阅了。 每次找规范都是一顿操作猛如虎&#xff0c;一看下载要付2块5。 这些痛苦小…

威妥玛与汉语拼音

威妥玛&#xff0c;Thomas Francis Wade&#xff08;1818&#xff0d;1895&#xff09;&#xff0c;英国外交官、著名汉学家&#xff0c;曾在中国生活四十余年&#xff0c;因发明用罗马字母标注汉语发音系统---威妥玛注音---而著称&#xff0c;此方法在欧美广为使用&#xff0c…

希腊字母读音表

希腊字母读音表 大写 小写 英文注音 国际音标注音 中文注音 Α α alpha alfa 阿耳法 Β β beta beta 贝塔 Γ γ gamma gamma 伽马 Δ δ deta delta 德耳塔 Ε ε epsilon epsilon 艾普西隆 Ζ ζ zeta zeta 截塔 Η η eta eta 艾塔 Θ θ theta θita 西塔 Ι ι…

All Attention You Need

点击上方“Datawhale”&#xff0c;选择“星标”公众号 第一时间获取价值内容 Attention的产生 起因&#xff1a; 《Sequence to Sequence Learning with Neural Networks》 Attention模型的引入原因&#xff1a; seq2seq将输入序列都压缩成一个固定大小的隐变量&#xff0c;就…

大话数据结构系列之数学基础知识补充

文章目录 数学归纳法实际以多米诺效应推导求证等差数列结论是否正确&#xff1a; 二分法的对数推导时间复杂度常规比较快速查询的时间复杂度推导对数对数的由来对数的定义log2^n > lgn 推导 相关的数学公式了解 数学归纳法 数学归纳法是以一种不同的方式来证明任意一个给定…