mq4096 2019-05-04 19:29:41 浏览 273 评论 0
Oceanbase
性能
SQL
数据库
Create
云数据库 Oceanbase
摘要: 在业务数据库性能问题诊断中, 如果发现一个业务性能很差跟某个 SQL 有关, 应用连接池几乎被该 SQL 占满, 同时数据库服务器上也不堪重负. 阿里数据库 (AliSQL 和 OceanBase) 都支持在运行中干预 SQL 的执行计划, 以及对问题 SQL 并发进行限流, 以快速将数据库和应用从某个问题 SQL 的影响中恢复出来.
概述
在业务数据库性能问题诊断中, 如果发现一个业务性能很差跟某个 SQL 有关, 应用连接池几乎被该 SQL 占满, 同时数据库服务器上也不堪重负. 此时情况很紧急, 业务改 SQL 重发布已经来不及了, 运维能选择的操作不多. 如批量重启应用, 数据库切换或者重启. 此时业务中断一下, 很可能很快压力又上来, 问题依然在那个 SQL. 前篇文章阿里数据库性能诊断的利器 --SQL 全量日志分享了如何定位问题 SQL, 本文就分享阿里数据库们如何处理这个问题 SQL.
ORACLE 在 SQL 性能诊断方面的功能非常丰富, 一直被其他数据库模仿. 其中 Outline 和 SQL Profile 功能就非常有用. 当业务 SQL 在生产环境执行计划走错后(如表连接顺序或算法不对, 或者索引选择的不对等等), 此时可以在该问题 SQL 上使用 hint 先生成一个正确的执行计划, 然后用 Outlines 存储这个执行计划, 然后再跟执行计划缓存里在用的执行计划进行交换, 从而让业务 SQL 回到正确的执行计划上, 性能问题也就迎刃而解了. 虽然这个过程有点复杂, 也有很多注意事项, 但终究是一个不错的选择. ORACLE 在 10g 后将 Outlines 功能进一步改进推出 SQL Profiles 功能, 更容易生成, 更改和控制 SQL 执行计划. 详情参考后面文章.
MySQL 数据库在 SQL 性能优化方面能力一般, 数据库性能又严重依赖主机 IO 和 CPU 能力. 面对这个问题社区版的 MySQL 数据库只有选择切换或重启.
在互联网业务中, 数据库请求数 QPS 非常高, 当 SQL 有性能问题时, 很快就会将数据库的某个资源 (CPU 或 IO) 耗尽, 进而拖慢其他正常的业务 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)
备注:
其中的 cur_conc 列显示当前读并发数. 如果一直为 0, 表示规则没有命中问题 sql.
sql 特征不能太泛(误命中其他 sql), 也不能太具体(漏掉部分 sql).
限制的并发数 (自然数) 不能太高(超过 16 意义不大), 也不能太低(太低容易限制过死, 导致该 sql 的 qps 严重低于正常值, 很容易引起业务访问量下跌), 也要恰到好处.
如果特征中包含中文(不推荐), 务必确保中文能正确的被 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 ];
其中 stmt 为一个带有 HINT 的 DML 语句. 限流或固定计划, 通过 stmt 中的 HINT 来区分.
如果期望对含有 HINT 的语句进行限流和固定计划, 则需要 TO target_stmt 来指明相应的 SQL.
create outline outline_name on stmt1 to stmt2;
的语意是说对 stmt2 创建 outline, 让 stmt2 使用 stmt1 中的 hint.
指定 OR REPLACE 后, 可以对已经存在执行计划或限流规则进行 replace.(注: 限流规则和执行计划间可以彼此替换)
在使用 target_stmt 时, 严格要求 stmt 与 target_stmt 在去掉 hint 后完全匹配(实现中为去掉 hint 的 signature 相同). 若是在创建限流时使用 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 视图中的列:
database_name: outline 所属的 database 的名字
outline_name: outline 自己的名字
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 / 拼接而成的,
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 不能同时存在. 同时存在时只执行限流规则, 不会固定执行计划.
高级用法:
同一个 outline_name 可以对应多个具有相同 signature 的限流规则.
当同一条 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 上创建的视图.
参考
oracle 如何固定执行计划(一)
执行计划: Oracle 的 Profile 特性与 SQL 执行计划的稳定性
阿里数据库性能诊断的利器 --SQL 全量日志
总结
阿里数据库 (AliSQL 和 OceanBase) 都支持在运行中干预 SQL 的执行计划, 以及对问题 SQL 并发进行限流, 以快速将数据库和应用从某个问题 SQL 的影响中恢复出来.
推荐关注
来源: https://yq.aliyun.com/articles/701091