最佳实践—如何限流慢SQL-阿里云开发者社区

开发者社区> -技术小能手-> 正文

最佳实践—如何限流慢SQL

简介: 本文介绍了如何对慢SQL进行有效限流。
+关注继续查看

在数据库会话或者慢日志中发现大量慢SQL,大量占用数据库资源,同时活跃会话数、CPU使用率、IOPS、内存使用率等监控指标一项或者多项处于高位。分析后发现这些慢SQL不属于核心业务,是优化不足的烂SQL,为保障核心业务的稳定运行,此时我们需要对其进行限流。

相关限流语法,请参见SQL限流

SQL限流的运维操作步骤

  1. 实例会话页面或使用如下语句发现慢SQL。
select *
  from information_schema.processlist
 where COMMAND!= 'SLEEP'
   and TIME>= 1000
 order by TIME DESC;
  1. 分析慢SQL,请参见如何分析及优化慢SQL
  2. 创建限流规则,可使用SQL命令,或者实例会话里SQL限流功能上的白屏化操作。
  3. 从以下几方面观察限流规则效果。
    • 监控指标恢复情况;
    • 业务侧反馈;
    • show ccl_rules查看每个限流规则的限流情况的统计信息;
    • 查看会话和SQL日志。
  1. 创建索引、修改SQL、增加资源等。
  2. 关闭限流规则,使用DROP CCL_RULE或者CLEAR CCL_RULES语句。

如下案例说明了如何对发现的慢SQL进行限流,您可以参照案例中的限流规则,修改后使用。

案例1: 慢SQL属于同一个SQL模版

某DBA收到了数据库资源某指标处于高位的报警,查看数据库慢日志和会话后均发现有如下的慢SQL:


+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+
| ID     | USER          | HOST                | DB                 | COMMAND | TIME | STATE | INFO                                         | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 222.0.0.1:33830     | analy_db           | Query   |   40 |       | select * from bmsql_oorder where `o_id` > 12 | 65c92c88        |
| 952468 | userxxxxxxxxx | 222.0.0.1:33517     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where `o_id` > 10 | 65c92c88        |
| 953468 | userxxxxxxxxx | 222.0.0.1:33527     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where `o_id` > 23 | 65c92c88        |
| 954468 | userxxxxxxxxx | 222.0.0.1:33537     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where `o_id` > 25 | 65c92c88        |
| 955468 | userxxxxxxxxx | 222.0.0.1:33547     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where `o_id` > 27 | 65c92c88        |
+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+

可见,这些慢SQL属于同一个SQL模版(模版ID为65c92c88):


select * from bmsql_oorder where `o_id` > ?

bmsql_oorder为一个数据量较大的表,而且列o_id上没有索引,显然这个一个未经优化的SQL,占尽了数据库资源影响了其他重要SQL的正常执行。这是一个非常适合利用模版ID去做SQL限流的场景。

创建限流规则

  • 如果这个SQL模版的SQL不应该在当时执行,而且应该在业务低峰期执行,则我们可以创建SQL限流规则不让它执行:
CREATE CCL_RULE `KILL_CCL`       //限流规则名称为KILL_CCL
    ON `analy_db`.`*`              //&匹配analy_db下的所有表上执行的SQL
  TO 'userxxxxxxxxx'@'%'         //&匹配来自userxxxxxxxxx用户的SQL
    FOR SELECT                     //&匹配是SELECT类型的SQL语句
  FILTER BY TEMPLATE '65c92c88'  //&匹配模版ID
  WITH MAX_CONCURRENCY = 0;      //设置单节点并发度为0,不允许匹配到的SQL执行
  • 客户端再次执行这类SQL的时候将会返回报错信息:
ERROR 3009 (HY000): [13172dbaf2801000][10.93.159.222:3029][analy_db]Exceeding the max concurrency 0 per node of ccl rule KILL_CCL
  • 如果允许这个SQL模版的SQL少量执行,只要不占尽数据库资源就行,则我们可以在创建限流规则的时候设置一定的并发度:
CREATE CCL_RULE `KILL_CCL_2`       //限流规则名称为KILL_CCL
    ON `analy_db`.`*`              //&匹配analy_db下的所有表上执行的SQL
  TO 'userxxxxxxxxx'@'%'         //&匹配来自userxxxxxxxxx用户的SQL
    FOR SELECT                     //&匹配是SELECT类型的SQL语句
  FILTER BY TEMPLATE '65c92c88'  //&匹配模版ID 65c92c88
  WITH MAX_CONCURRENCY = 2;      //允许单个节点可以同时有两个这样的SQL在执行
  • 也可使用实例会话页面里的SQL限流功能,进行如下操作:8..png
  • 如果希望这个SQL模版的SQL执行的时候可以慢,但尽量不要出错,则可以设置等待队列和等待超时时间(默认为600秒):


CREATE CCL_RULE `QUEUE_CCL_2`       //限流规则名称为KILL_CCL
    ON `analy_db`.`*`              //&匹配analy_db下的所有表上执行的SQL
  TO 'userxxxxxxxxx'@'%'         //&匹配来自userxxxxxxxxx用户的SQL
    FOR SELECT                     //&匹配是SELECT类型的SQL语句
  FILTER BY TEMPLATE '65c92c88'  //&匹配模版ID
  WITH MAX_CONCURRENCY = 2, WAIT_QUQUE_SIZE=20, WAIT_TIMEOUT=500; //单节点并发度为2,单节点等待队列长度为20,等待超时时间为500秒

创建完后,可以通过show ccl_rules指令查询各个限流规则的实际效果,比如当前匹配到某个限流规则的正在执行的SQL的数量、被限流报错的SQL数量、总匹配成功次数等。如果想放开被限流SQL,比如在增加了某个索引后,被限流SQL的执行效率变高了,则可以通过drop ccl_rule命令来关闭指定限流规则,或者使用clear ccl_rules来关闭所有的限流规则。

当然上面的SQL也可以通过关键字来限流,将SQL语句上的关键字做拆分,我们得到关键字列表:

  • select
  • from
  • bmsql_oorder
  • where
  • `o_id`

创建限流规则:


CREATE CCL_RULE `KILL_CCL`       //限流规则名称为KILL_CCL
    ON `analy_db`.`*`              //&匹配analy_db下的所有表上执行的SQL
  TO 'userxxxxxxxxx'@'%'         //&匹配来自userxxxxxxxxx用户的SQL
    FOR SELECT                     //&匹配是SELECT类型的SQL语句
  FILTER BY KEYWORD('select','from','bmsql_oorder','where','`o_id`')  //&匹配模版ID
  WITH MAX_CONCURRENCY = 0;      //设置单节点并发度为0,不允许匹配到的SQL执行

在能获取到模版ID(在SQL日志、explain命令、会话中)的情况下,我们还推荐使用更精准的基于模版ID的限流。

也可使用实例会话页面里的SQL限流功能,操作如下:

案例2: 慢SQL都是同一个SQL

某DBA收到了数据库资源某指标处于高位的报警,查看数据库慢日志和会话后均发现有如下的慢SQL:


+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| ID     | USER          | HOST                | DB                 | COMMAND | TIME | STATE | INFO                                              | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 222.0.0.1:33830     | analy_db           | Query   |   40 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
| 952468 | userxxxxxxxxx | 222.0.0.1:33517     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
| 953468 | userxxxxxxxxx | 222.0.0.1:33527     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
| 954468 | userxxxxxxxxx | 222.0.0.1:33537     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
| 955468 | userxxxxxxxxx | 222.0.0.1:33547     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+

bmsql_oorder中的符合o_carrier_id = 2条件的有较多记录,导致了慢SQL,如果使用模版ID限流,则会影响o_carrier_id不是2的SQL语句,如果使用关键字限流则会影响类似如下的正常SQL:


select * from bmsql_oorder where o_carrier_id = 2 limit 1;
select * from bmsql_oorder where o_carrier_id = 2 and o_c_id = 1;

限流具体的SQL,可以使用模版ID加关键字的方法,创建如下限流规则:


CREATE CCL_RULE `KILL_CCL`       //限流规则名称为KILL_CCL
    ON `analy_db`.`*`              //&匹配analy_db下的所有表上执行的SQL
  TO 'userxxxxxxxxx'@'%'         //&匹配来自userxxxxxxxxx用户的SQL
    FOR SELECT                     //&匹配是SELECT类型的SQL语句
  FILTER BY TEMPLATE '438b00e4'  //&匹配模版ID 438b00e4
  FILTER BY KEYWORD('o_carrier_id','2')  //&匹配参数关键字
  WITH MAX_CONCURRENCY = 0;      //设置单节点并发度为0,不允许匹配到的SQL执行

如果使用PolarDB-X的CN内核版本为5.4.11以上,且该SQL不在prepare模式下执行,还可以使用如下高阶语法进行限流:


CREATE CCL_RULE `KILL_CCL`       //限流规则名称为KILL_CCL
    ON `analy_db`.`*`              //&匹配analy_db下的所有表上执行的SQL
  TO 'userxxxxxxxxx'@'%'         //&匹配来自userxxxxxxxxx用户的SQL
    FOR SELECT                     //&匹配是SELECT类型的SQL语句
  FILTER BY QUERY 'select * from bmsql_oorder where o_carrier_id = 2'  //&匹配SQL语句
  WITH MAX_CONCURRENCY = 0;      //设置单节点并发度为0,不允许匹配到的SQL执行

案例3: 慢SQL集包含多个SQL模版

某DBA收到了数据库资源某指标处于高位的报警,查看数据库慢日志和会话后均发现有如下的慢SQL:


+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| ID     | USER          | HOST                | DB                 | COMMAND | TIME | STATE | INFO                                              | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 222.0.0.1:33830     | analy_db           | Query   |   40 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
| 952468 | userxxxxxxxxx | 222.0.0.1:33517     | analy_db           | Query   |   43 |       | select * from bmsql_order_line where ol_o_id = 2  | 57a572f9        |
| 953468 | userxxxxxxxxx | 222.0.0.1:33527     | analy_db           | Query   |   43 |       | select * from bmsql_new_order where no_w_id = 2   | de6eefdb        |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+

此种情况较为复杂,如果一条明显执行效率很高的SQL也成了慢SQL,则不排除是由于网络抖动或者服务节点异常等原因导致运行效率降低从而产生大批量的慢SQL,也可能是由于真正的烂SQL完全耗尽了资源,导致原本正常的SQL也成了慢SQL,需要通过SQL分析具体原因,不在本文的讨论范围内。假设已经确定了需要限流的慢SQL,我们则可以针对每个模版ID创建一个限流规则。但随着限流规则增加,匹配效率会略有降低,当PolarDB-X的CN内核版本为5.4.11以上时,我们推荐使用多模版限流:


CREATE CCL_RULE `KILL_CCL`       //限流规则名称为KILL_CCL
    ON `analy_db`.`*`              //&匹配analy_db下的所有表上执行的SQL
  TO 'userxxxxxxxxx'@'%'         //&匹配来自userxxxxxxxxx用户的SQL
    FOR SELECT                     //&匹配是SELECT类型的SQL语句
  FILTER BY TEMPLATE('438b00e4','57a572f9','de6eefdb')  //&匹配中其中一个模版ID,则该匹配项算匹配成功
  WITH MAX_CONCURRENCY = 0;      //设置单节点并发度为0,不允许匹配到的SQL执行

如果确定会话中的慢SQL是都是需要限流的烂SQL,且PolarDB-X的CN内核版本为5.4.11以上时,可以开启慢SQL限流。也可以使用实例会话页面里的SQL限流功能,进行如下操作:9..png

总结

SQL限流为应急措施,可在数据库由于烂SQL导致效率降低的时候,起到快速恢复的作用。对烂SQL进行限流后,用户需要将注意力集中在如何优化烂SQL上,并在合适的时机清空SQL限流规则。

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

相关文章
【巡检问题分析与最佳实践】RDS PostgreSQL 慢SQL问题
慢SQL会消耗较多的数据库资源,很容易造成数据库的负载增加,慢SQL的问题直接影响着数据库的性能,排查慢SQL问题,首先要找到RDS PG中的慢SQL,在学习EXPLAIN的语法,学会阅读执行计划,并对SQL进行优化。
220 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
2153 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
3951 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
5663 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
4962 0
最佳实践—如何分析及优化慢SQL
本文介绍了分析和解决慢SQL的方法。
28 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
3794 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
16265 0
951
文章
0
问答
来源圈子
更多
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载