开发者社区> mq4096> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

阿里数据库性能诊断的利器——SQL全量性能数据

简介: 在业务数据库调优过程中,如果数据库能记录执行过的每个SQL的性能信息,那对应用诊断性能异常问题会很有帮助。传统商业数据库在这方面做了一些探索。 阿里数据库内核产品能输出全量SQL执行性能数据,数据库的PaaS平台在这些数据基础上可以开发自动化数据库性能诊断产品。
+关注继续查看

概述

在业务数据库调优过程中,如果数据库能记录执行过的每个SQL的性能信息,那对应用诊断性能异常问题会很有帮助。传统商业数据库在这方面做了一些探索。

如ORACLE的AWR或ASH视图里记录的SQL都是参数化的SQL,并且还有去重。所以不能准确关联到有问题的业务SQL上。ORACLE的诊断思路是如果SQL性能不好,就执行计划上找原因,相应的解决方案就是调整索引、收集统计信息或者用大纲(OUTLINE)修改或固定执行计划。

MySQL没有这样的SQL历史视图,有慢日志(Slow Log)可以收集执行时间超过指定阈值(long_query_time)的SQL记录在慢日志里。MySQL的思路是只关注执行慢的SQL。不过这个记录的性能数据信息并不是很多。此外记录的SQL也只是业务SQL的很小部分。此外MySQL有查询日志(Query Log),默认是关闭的。如果打开,MySQL的性能会下降50%以上,所以生产环境基本不敢用。

在生产中,有时会碰到这种情形就是并不复杂的业务SQL突然变得很慢,其执行计划也并没有走错。单独跑这个SQL又很快。这个就让人很困惑。要分析这个问题需要认识到两点:

  1. 业务SQL在数据库的响应时间 等于SQL排队等被调度过程中的等待时间加上SQL的执行时间。当SQL请求非常高的时候,SQL工作线程非常繁忙时会引起排队。
  2. 我们从客户端监控到的SQL响应时间多是平均响应时间,只是一个时间段内全部SQL执行时间的统计值。对于具体的每个SQL,其响应时间可能在这个均值之下或者之上。或者说全部SQL的响应时间实际呈现一个类似正太分布的。

所以,如果数据库内核能记录每个SQL详细的执行信息,就能观察到上面两点。如总的时间、等待IO时间、锁等待时间和服务时间、逻辑读、物理读信息等,甚至更多。有了这些基础信息后,数据库性能诊断可以自动化,不再单纯依赖DBA的精力和能力。

阿里数据库内核的SQL全量功能

AliSQL的SQL全量日志

AliSQL是阿里巴巴数据库内核团队曾经维护的一个开源MySQL的分支,针对MySQL内核做了很多加强和优化。其中一个独特的功能就是SQL全量信息。
AliSQL的内核会在SQL执行结束时拿到执行性能信息后就答复客户端。然后异步的将SQL文本和执行性能信息以字符流形式写入一个SQL日志缓冲区。然后一个单独的日志线程循环读取该SQL日志缓冲区内容并写入到磁盘上一个管道文件。管道文件每个MySQL实例一个(实例监听端口不一样),格式为:/u01/my$port/run/mysql.fifo。这个管道文件很特殊,如果写满了需要有其他客户端连接到这个管道并消费(读取)数据。否则,内核会停止输出(写入)SQL全量性能信息。这个设计很巧妙,不用担心SQL全量日志会占用空间(管道文件的大小很小)。它需要有客户端不停的读取这个管道文件。这个客户端就是MySQL的运维平台的监控客户端。

SQL全量输出的信息格式如下:

#time:1464861208504797
#user@host:root[root] @ [127.0.0.1]
#db:chuck
#table_name:tt
select * from tt;
#query_time:0.000232
#lock_time:0.000000
#rows_sent:0
#rows_examined:0
#rows_affected:0
#innodb_pages_read:0
#innodb_pages_io_read:0
#id:342

SQL全量输出通过参数log_sql_info控制。默认是false,开启就设置为true。开启后要先保证监控客户端在读取管道文件。通过命令show Sql_log_info_status 可以查看SQL全量日志输出状态。即使在天猫双11大促期间,这个功能也是开启的,对性能的影响在2%以内,完全可以接受。

OceanBase的SQL全量日志

OceanBase是阿里巴巴和蚂蚁金服完全自主研发的通用的分布式关系型数据库,其在SQL执行和性能诊断方面的逻辑大量参考了ORACLE的设计思路。所以在OceanBase里也有执行计划、硬解析和软解析,以及类似AWR设计的性能视图等。同时OceanBase还有自己的创新就是提供了一个SQL审计的功能。

OceanBase的视图(v$sql_audit)会以类似队列形式缓存当前集群内运行的所有SQL的执行性能信息,并且包括那些执行报错的SQL(报错原因很多如内部执行超时、锁等待超时、违反约束等各种原因)。这部份数据全部在内存里,内存大小由参数控制(sql_audit_memory_limit),默认是3G。超出这个大小的SQL审计信息就遵循先入先出原则,并不会保存在磁盘文件里。当然有人可能会说如果OceanBase集群挂了这个数据不就丢失了吗?实际上由于OceanBase独特的高可用特性,不是那么容易挂掉的。此外,OceanBase运维平台(OCP)也会部署监控客户端定时拉取这个性能数据用作后面分析。
OceanBase的SQL审计功能由参数(enable_sql_audit)控制,可以针对每个节点设置是否开启。默认值是开启的(true)。同样即使在蚂蚁双11大促期间,这个参数也可以不关闭,由此可见开启这个对性能并没有什么影响。
15566305474279

OceanBase的SQL审计包含了SQL文本以及执行的详细信息,如执行节点、总耗时、等待时间、服务时间、逻辑读、影响行数、等待事件及其参数和其他信息,内容非常丰富。如下图

15566303584288
15566303765575
15566303930326

下面是查询某个节点上某个租户的某个用户的的最近的 100条SQL执行信息

SELECT /*+ read_consistency(weak) ob_querytimeout(100000000) */ substr(usec_to_time(request_time),1,19) request_time_, s.svr_ip, s.client_Ip, s.sid,s.tenant_id, s.tenant_name, s.user_name, s.db_name, s.query_sql, s.affected_rows, s.return_rows, s.ret_code, s.event, s.elapsed_time, s.queue_time, s.execute_time, round(s.request_memory_used/1024/1024,2) req_mem_mb, is_executor_rpc, is_inner_sql
FROM gv$sql_audit s
WHERE  s.tenant_id=1001 and user_name='testuser'  and svr_ip in ('11.166.84.78')
ORDER BY request_time DESC
LIMIT 100;

15566309266473

SQL全量日志的用处

数据库诊断引擎 CloudDBA

在电商有上万的AliSQL实例,上千的数据库菜鸟开发,上百个高流量敏感的核心业务。然后支持业务的DBA团队只有十几个人,其中还不乏兼职的运维平台开发人员。数据库的性能问题不能再依赖DBA去一一分析,更别提提前发现预防性能问题。当AliSQL的运维平台收集了所有实例7*24小时的SQL全量执行信息后,接入到一个数据库智能诊断平台中,就能依据一定的规则和机器学习算法去识别性能异常并告警给开发。同时平台自动读出该SQL的详细性能数据、执行计划甚至给出优化建议(如调整索引等)给到开发。极大的降低了研发同学做数据库诊断的技术门槛。在电商,这个产品叫CloudDBA,在阿里云数据库上也有一个类似的产品,思路是一样的。

下面是CloudDBA曾经某个版本的一个功能示意图。能看到全部SQL的QPS和RT,以及每个SQL在不同区间的分布状况。
15566327282811

进一步点击SQL前面的id值,显示该SQL的不同时间段的执行时间RT分布。

15566323871662

蓝色的是链接,还可以进一步做性能下钻分析。

CloudDBA的价值在于降低了业务研发做数据库诊断的技术门槛,同时解放了业务DBA的人力,专心去做更难的事情。不过也不能过度夸大其作用。作为一个大规模的性能诊断平台,无论是基于规则(DBA的经验)还是基于机器学习(计算机的经验),都是基于统计的,都可能存在”不命中“或者”误判“的情形。

OceanBase数据库云平台 OCP

同样,OCP采集了各个OceanBase集群的SQL审计信息后也提供页面可以查看TOP SQL信息,并提供SQL性能分析下钻功能。

15566345914112

点击 蓝色序号链接,可以查看SQL明细
15566346314681

这些还只是的简单的展示。在SQL审计日志信息上也可以继续发展自动诊断,定位问题SQL,并给出优化建议。

参考

更多分享,欢迎关注微信公众号:obpilot
0_5

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL数据库学习之路(九)
目录   数据库创建补充: 为数据库用户赋予权限 在数据库表中使用视图进行添加修改 数据库创建补充: 以文本格式显示自己创建的数据库表                                          以网格格式显示自己创建的数据库表 可以右键数据库--属性---选项,在选项中可以设置数据库的兼容性,还有是否可以让多人同时访问或者单人访问。
863 0
SQL数据库学习之路(练习)---C#登录界面连数据库
目录 C#登录界面连数据库 一、在数据库中先创建一个数据库。 二、在VS中创建C#的windows窗体应用程序 三、在VS中连接到数据库 四、设计登录界面 五、设置注册界面 六、将form1的按钮和form2进行关联 七、设置form2界面的注册按钮 八、form1的登录按钮设置 九、运行成果 参考文章: C#登录界面连数据库 一、在数据库中先创建一个数据库。
2728 0
SQL数据库学习之路(八)
数据库介绍: 在网页上的主程序中进行注册操作,然后把数据发送给人,人传递这些数据到数据库当中。   为什么学ADO.NET:之前我们所学的只能在查询分析器中查看数据,操作数据。
886 0
SQL数据库学习之路(七)
在SQL server managerment中可以点击帮助,会有帮助文档。 一、联合查询 将多个查询结果集合并成一个。
821 0
SQL数据库学习之路(六)
一、连接查询:当需要的结果需要从多张表中取时 关键问题:哪些表、关系(学生表的Id联系到班级表的Id) 内连接:inner join,两表中完全匹配的数据。
901 0
SQL数据库学习之路(四)
要求:通过SQL语句创建以下基本表:    教师关系 T(T#, TNAME,TITLE)    课程关系 C(C#,CNAME,T#)    学生关系 S(S#,SNAME,AGE,SEX)    选课关系SC(S#,C#,SCORE)    班级关系CLASS(CLASSID,CLASSNAME)    其中红色粗体为主键,带下划线的属性为外键。
812 0
SQL数据库学习之路(三)
一.表数据的操作 1.简单查询 select * from 表名 2.增加数据 insert into 表名(列名) values(值)                   ----要求值的列名与值要位置对应   ...
789 0
SQL数据库学习之路(二)
(一)1.约束:实现数据的有效性检查---------主键(保证值是唯一的) 2.非空(输入的数据不允许为空) 3.唯一(存入的数据不能重复) 4.默认(自己不给数据添加值,值会默认添加) 5.检查(给值一个选定的范围) 6.外键(对关系的有效性进行检查,有关系才有外键) (二)脚本操作数据库 1.不区分大小写,字符串使用单引号,末尾不需要加分号。
802 0
SQL数据库学习之路(一)
1.数据库简介(一个放数据的仓库)  解决的问题:持久化存储,优化读写,保证数据的有效性 关系型数据库:    基于E-R模型(实体-联系图Entity Relationship)    使用sq|语言进行操作(SQL...
1013 0
T-SQL查询:语句执行顺序
原文:T-SQL查询:语句执行顺序 读书笔记:《Microsoft SQL Server 2008技术内幕:T-SQL查询》   ===============  T-SQL查询的执行顺序 ===============      =============== T-SQL查询的示意图...
792 0
+关注
mq4096
OceanBase 对外技术输出。欢迎关注个人公众号:obpilot
23
文章
20
问答
来源圈子
更多
蚂蚁OceanBase数据库团队,用于OceanBase技术原理、运维经验和案例分享、对外交流。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载