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

阿里数据库性能诊断的利器——SQL执行干预

简介: 在业务数据库性能问题诊断中,如果发现一个业务性能很差跟某个SQL有关,应用连接池几乎被该SQL占满,同时数据库服务器上也不堪重负。 阿里数据库(AliSQL和OceanBase)都支持在运行中干预SQL的执行计划,以及对问题SQL并发进行限流,以快速将数据库和应用从某个问题SQL的影响中恢复出来。
+关注继续查看

概述

在业务数据库性能问题诊断中,如果发现一个业务性能很差跟某个SQL有关,应用连接池几乎被该SQL占满,同时数据库服务器上也不堪重负。此时情况很紧急,业务改SQL重发布已经来不及了,运维能选择的操作不多。如批量重启应用、数据库切换或者重启。此时业务中断一下,很可能很快压力又上来,问题依然在那个SQL。前篇文章阿里数据库性能诊断的利器——SQL全量日志分享了如何定位问题SQL,本文就分享阿里数据库们如何处理这个问题SQL。

ORACLE在SQL性能诊断方面的功能非常丰富,一直被其他数据库模仿。其中OutlineSQL Profile功能就非常有用。当业务SQL在生产环境执行计划走错后(如表连接顺序或算法不对,或者索引选择的不对等等),此时可以在该问题SQL上使用hint先生成一个正确的执行计划,然后用Outlines存储这个执行计划,然后再跟执行计划缓存里在用的执行计划进行交换,从而让业务SQL回到正确的执行计划上,性能问题也就迎刃而解了。虽然这个过程有点复杂,也有很多注意事项,但终究是一个不错的选择。ORACLE在10g后将Outlines功能进一步改进推出SQL Profiles功能,更容易生成、更改和控制SQL执行计划。详情参考后面文章。

MySQL数据库在SQL性能优化方面能力一般,数据库性能又严重依赖主机IOCPU能力。面对这个问题社区版的MySQL数据库只有选择切换或重启。

在互联网业务中,数据库请求数QPS非常高,当SQL有性能问题时,很快就会将数据库的某个资源(CPUIO)耗尽,进而拖慢其他正常的业务SQL。而应用服务器集群里每个APP的连接池也会相应被耗尽,从而可能出现应用相继挂掉,引起雪崩。阿里数据库AliSQL和OceanBase针对这个场景都有一个SQL干预手段,即SQL执行计划修改或者限流。

阿里数据库内核的SQL执行干预功能

AliSQL的SQL执行计划干预和SQL限流

AliSQL是阿里巴巴数据库内核团队曾经维护的一个开源MySQL的分支,针对MySQL内核做了很多加强和优化。其中一个独特的功能就是SQL执行计划干预和SQL限流。

AliSQL的SQL执行计划干预也是利用hint先生成正确的执行计划,然后再替换掉实际运行中的执行计划。能修改的也只是索引。并且其替换并不像ORACLE那样严格的使用SQL ID去替换,而是可以根据SQL特征去匹配替换。这个功能的关键字是sql_hints。其原理是在语法解析后,sql优化前,根据设定的规则,对语法解析树进行修改。模拟在语法解析中,解析到了index hint。

功能:可在Server端设定指定规则,为指定sql动态添加索引hint,以干预其执行计划。
语法:

  Set sql_hints=’+,<schema_name>,<table_name>,<index_name>,<keyword1>~<keyword2>~<keywordn>’;
Example: 
      Set sql_hints=’+,test,t1,idx_id1,select id from~orderid=~status=’;

这个功能也有些限制就是只能针对单表select限制,特征指定(keyword)不能太简单,db名称不能模糊匹配。在电商MySQL场景完全够用了。

示例设置规则:

root@(none) 01:03:17>set global sql_hints='+,xxx_xxxxxx_0000,xxxxxx_template_refer_,idx_refer,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` =';

示例查看规则,可以看到规则命中多少次,失败多少次。

root@(none) 01:03:14>show sql_hints; 
+---------+-----------------------+--------------------------+-----------+---------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rule_id | db                    | table                    | index     | key_num | hits    | errors | cmd_str                                                                                                                                                                              | 
+---------+-----------------------+--------------------------+-----------+---------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
|       6 | xxxxxx_xxxxxx_0000 | xxxxxx_template_refer_ | idx_refer |       5 |  452076 |      0 | xxxxxx_xxxxxx_0000,xxxxxx_template_refer_,idx_refer,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` =                           | 
|       2 | xxxxxx_xxxxxx_0000 | xxxxxx_template_refer_ | idx_refer |       3 | 2181691 |      0 | xxxxxx_xxxxxx_0000,xxxxxx_template_refer_,idx_refer,`xxxxxx_template_refer`.`refer_user_id` =~`xxxxxx_template_refer`.`user_id` =~`xxxxxx_template_refer`.`template_id` = | 
+---------+-----------------------+--------------------------+-----------+---------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
2 rows in set (0.00 sec)

当SQL性能问题不是执行计划走偏导致的时候,上面方法就没有用。这类往往是新发业务,业务上线前没有严格测试性能。此时需要业务改SQL,但是应用修改发布时间没那么快。所以AliSQL还有个功能就是针对SQL限流功能,即限制SQL并发数。

设置sql限流时可以针对select/update/delete 设置限流命令:

set global sql_select_filter='+,并发数,sql特征1~sql特征2';
set global sql_update_filter='+,并发数,sql特征1~sql特征2';
set global sql_delete_filter='+,并发数,sql特征1~sql特征2';

查看当前的sql限流设置命令:

show sql_filters;

举例,限制SQL并发数为4:

root@(none) 01:23:15>set global sql_select_filter='+,4,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` ='; 
Query OK, 0 rows affected (0.00 sec) 
root@(none) 01:24:34>show sql_filters; 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| type   | item_id | cur_conc | max_conc | key_num | key_str                                                                                               | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| SELECT |       1 |        0 |        4 |       5 | +,4,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` = | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

备注:

  1. 其中的cur_conc 列显示当前读并发数。如果一直为0,表示规则没有命中问题sql。
  2. sql特征不能太泛(误命中其他sql),也不能太具体(漏掉部分sql)。
  3. 限制的并发数(自然数)不能太高(超过16意义不大),也不能太低(太低容易限制过死,导致该sql的qps严重低于正常值,很容易引起业务访问量下跌),也要恰到好处。
  4. 如果特征中包含中文(不推荐),务必确保中文能正确的被mysql接收。os的session变量export.UTF-8 ,同时把终端字符集设置为 UTF-8。 详情参见 数据库字符乱码问题分析

该限流设置还可以撤销。首先查看sql限流规则的 item_id 值,然后通过相同的设置命令取消该项规则。
命令:

set global sql_select_filter='-,规则id';
set global sql_update_filter='-,规则id';
set global sql_delete_filter='-,规则id';

举例:

root@(none) 01:24:34>show sql_filters; 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| type   | item_id | cur_conc | max_conc | key_num | key_str                                                                                               | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| SELECT |       1 |        0 |        4 |       5 | +,4,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` = | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec) 
root@(none) 01:24:41>set global sql_select_filter='-,1'; 
Query OK, 0 rows affected (0.00 sec) 
root@(none) 01:24:49>show sql_filters; 
Empty set (0.00 sec)

AliSQL的这个SQL执行计划修改和限流功能,在开源的AliSQL(地址:https://github.com/alibaba/AliSQL)里已经包含了。

OceanBase的SQL Outlines功能

OceanBase是阿里巴巴和蚂蚁金服完全自主研发的通用的分布式关系型数据库,其在SQL执行和性能诊断方面的逻辑大量参考了ORACLE的设计思路。OceanBase也支持SQL Outline功能,能够修改在线运行的SQL执行计划。同时也支持SQL限流功能。

Outline的用法也是通过SQL Hint固定SQL的执行计划,可以调整表连接算法、使用的索引等等。
创建大纲的语法如下:

CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ];
  1. 其中stmt为一个带有HINT的DML语句。限流或固定计划,通过stmt中的HINT来区分。
  2. 如果期望对含有HINT的语句进行限流和固定计划,则需要TO target_stmt来指明相应的SQL。create outline outline_name on stmt1 to stmt2;的语意是说对stmt2创建outline,让stmt2使用stmt1中的hint
  3. 指定OR REPLACE后,可以对已经存在执行计划或限流规则进行replace。(注:限流规则和执行计划间可以彼此替换)
  4. 在使用target_stmt时,严格要求stmttarget_stmt在去掉hint后完全匹配(实现中为去掉hintsignature相同)。若是在创建限流时使用target_stmt,则同时要求fix_param完全匹配。

举例说明:

OceanBase (root@oceanbase)> create outline ol_1 on  select /*+index(t1 c2)*/ * from t1 where c1 =1;
Query OK, 0 rows affected (0.07 sec)

OceanBase (root@oceanbase)> select * from __all_outline\G;
*************************** 1. row ***************************
     gmt_create: 2016-06-08 16:09:39.058537
   gmt_modified: 2016-06-08 16:09:39.058537
      tenant_id: 1
     outline_id: 1099511628777
    database_id: 1099511627777
 schema_version: 1465373379055176
           name: ol_1
      signature: select  * from t1 where c1 =?
outline_content:   /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase.t1"@"SEL$1" "c2") END_OUTLINE_DATA  */
       sql_text: select /*+index(t1 c2)*/ * from t1 where c1 =1
          owner: root
           used: 0
        version: 60768-local-78cf62842644724e437542cd12c2cc1e76805ee0
     compatible: 1
        enabled: 1
         format: 01 row in set (0.07 sec)

OceanBase (root@oceanbase)> create table t1(c1 int, c2 int, c3 int, key(c2), key(c3,c2));
Query OK, 0 rows affected (0.13 sec)

OceanBase (root@oceanbase)> CREATE OUTLINE ol_1 ON select /*+index(t1 c3)*/ c3,c2 from t1 TO select  c3,c2 from t1;
Query OK, 0 rows affected (0.02 sec)
OceanBase (root@oceanbase)> select * from oceanbase.gv$outline\G
*************************** 1. row ***************************
        tenant_id: 1
      database_id: 1099511627777
       outline_id: 1099511628777
    database_name: oceanbase
     outline_name: ol_1
visible_signature: select  c3,c2 from t1
         sql_text: select /*+index(t1 c3)*/ c3,c2 from t1
   outline_target: select  c3,c2 from t1
      outline_sql: select /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase.t1"@"SEL$1" "c3") END_OUTLINE_DATA*/ c3,c2 from t1
1 row in set (0.00 sec)

创建完ol_1后,所有*select from t1 where c1 =?语句都会按照/+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase.t1"@"SEL$1" "c2") END_OUTLINE_DATA /固定的计划执行。其中?可以是任意值。

这里着重介绍下迁移outline过程中会用到的oceanbase.gv$outline视图中的列:

  1. database_name: outline所属的database的名字
  2. outline_name: outline自己的名字
  3. outline_sql: outline_sql是将原始查询和固定计划的hint拼接在一起的字符串。用于还原outline 创建语句,上面这个例子中,就是将select c3,c2 from t1 和 固定计划的 /+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase.t1"@"SEL$1" "c3") END_OUTLINE_DATA/拼接而成的,
  4. outline_target: outline_target是使用CREATE OUTLINE ol_name ON on_stmt TO to_stmt这种语法创建outline时的to_stmt, 用于支持在带有hint的DML语句上创建outline; 如果使用CREATE OUTLINE ol_name ON on_stmt ;语句创建outline,则outline_target内容为空。

OceanBase Outline更多的时候是用在数据库Failover、分布式数据库集群扩容(增加实例和分库等)等场景中,将正常数据库的SQL执行计划迁移到新的租户中,可以确保SQL性能不会发生意外的变化。

create outline时,如果指定MAX_CONCURRENT(NUM),将会对当前SQL进行限流。指定限流规则后会控制一个observer中可以并发执行的限流SQL个数。并发度控制的是限流SQL对应的physical_plan在单一observer可以并发执行的个数;也就是说对于整个集群,并发执行的限流SQL个数大于HINT中指定的并发度。

示例:

OceanBase (root@oceanbase)> create outline ol_1 on select /*+max_concurrent(1)*/ * from t2 where c1 = 1 and c2 = ?;
Query OK, 0 rows affected (0.04 sec)

创建完ol_1后,形如 select * from t2 where c1 = 1 and c2 = ? 的sql在单台observer中可执行的并发度为1;
c2 = ? 表明问号的位置可以被任意的const值代替,例如下面的sql都会被限流:

select  * from t2 where c1 = 1 and c2 = 1;
select  * from t2 where c1 = 1 and c2 = 2;
select  * from t2 where c1 = 1 and c2 = "2";
select  * from t2 where c1 = 1 and c2 = true;

注意:
限流和固定计划的使用方法类似,均是通过指定HINT的形式来实现;限流的HINT为MAX_CONCURRENT(NUM),其中NUM为并发度。当被限流的SQL达到最大并发个数后,再有新的限流SQL执行时会报,server会返回 SQL reach max concurrent num 错误。
当前限流和固定计划功能均通过create outline语句来实现,为了确保语意的正确性和清晰性,我们规定create outline中限流HINT和其他HINT不能同时存在。同时存在时只执行限流规则,不会固定执行计划。

高级用法:

  1. 同一个outline_name可以对应多个具有相同signature的限流规则。
  2. 当同一条SQL可以匹配多个限流规则时,会选择并发度最小的进行限流。
OceanBase (root@oceanbase)> create outline ol_1 on select /*+max_concurrent(1)*/ * from t1 where c1 =1 and c2 = 1;
Query OK, 0 rows affected (0.07 sec)

OceanBase (root@oceanbase)> alter outline ol_1 add select /*+max_concurrent(1)*/ * from t1 where c1 =1 and c2 = ?;
Query OK, 0 rows affected (0.09 sec)

OceanBase (root@oceanbase)> alter outline ol_1 add select /*+max_concurrent(10)*/ * from t1 where c1 =? and c2 = 1;
Query OK, 0 rows affected (0.04 sec)

OceanBase支持的Hints

  • 语句级别的hint
FROZEN_VERSION
QUERY_TIMEOUT
READ_CONSISTENCY
LOG_LEVEL
QB_NAME
ACTIVATE_BURIED_POINT
TRACE_LOG
MAX_CONCURRENT
  • 计划相关的hint
FULL
INDEX
LEADING
USE_MERGE
USE_HASH
USE_NL
ORDERED
NO_REWRITE
  • 存储outline的schema信息的系统表
oceanbase.__all_outline
oceanbase.__all_outline_history
  • 固定计划相关虚拟表和试图

展示的均是当前租户的信息:

oceanbase.__tenant_virtual_outline  用于outline迁移使用的虚拟表,同时显示固定计划的信息。

oceanbase.gv$outline 在__tenant_virutal_outline基础上创建的视图。

information_schema.dba_outlines  在__all_table上创建的视图。
  • 限流相关虚拟表和试图

下表展示的均是当前租户的信息:

oceanbase.__tenant_virtual_concurrent_limit_sql: 展示限流信息
oceanbase.gv$concurrent_limit_sql: 在__tenant_virtual_concurrent_limit_sql上创建的视图。

参考

总结

阿里数据库(AliSQL和OceanBase)都支持在运行中干预SQL的执行计划,以及对问题SQL并发进行限流,以快速将数据库和应用从某个问题SQL的影响中恢复出来。

推荐关注

0_5

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

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