全部产品
弹性计算 会员服务 网络 安全 移动云 数加·大数据分析及展现 数加·大数据应用 管理与监控 云通信 阿里云办公 培训与认证 智能硬件
存储与CDN 数据库 域名与网站(万网) 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 更多
分布式关系型数据库 DRDS

如何排查 DRDS 慢 SQL

更新时间:2017-12-11 21:28:21

在 DRDS 中,一条 SQL 语句会在 DRDS 和 RDS 节点上逐步执行。任意节点上的执行损耗过大都会导致慢 SQL。

慢 SQL 的一般排查步骤为:

  1. 定位慢 SQL;

  2. 定位性能损耗节点;

  3. 定位性能损耗原因并处理。

说明:排查过程中,建议通过 MySQL 命令行进行连接:mysql -hIP -PPORT -uUSER -pPASSWORD -c 。请务必加上 “-c”,防止 MySQL 客户端过滤掉注释(默认)从而影响 HINT 的执行。

定位慢 SQL

定位慢 SQL 一般有两种场景:历史信息可从慢 SQL 记录中查询;实时慢 SQL 执行信息可使用 SHOW PROCESSLIST 指令展示。

  • 查看慢 SQL 记录

    执行以下指令查询慢 SQL Top 10。此查询针对 DRDS 层面的逻辑 SQL 。一个逻辑 SQL 对应一个或者多个 RDS 库表的 SQL 执行。详情见慢 SQL 明细文档

    mysql> SHOW SLOW limit 10;
    +-----------------+--------------+-------------------------+--------------+------------+-----------------------------------------------------------------+
    | TRACE_ID        | HOST         | START_TIME              | EXECUTE_TIME | AFFECT_ROW | SQL                                                             |
    +-----------------+--------------+-------------------------+--------------+------------+-----------------------------------------------------------------+
    | ac3133132801001 | 42.120.74.97 | 2017-03-06 15:48:32.330 |       900392 |         -1 | select detail_url, sum(price) from t_item group by detail_url;  |
    ......
    +-----------------+--------------+-------------------------+--------------+------------+-----------------------------------------------------------------+
    10 rows in set (0.01 sec)
    
  • 查看当前实时 SQL 执行信息

    如果当前服务器中正在执行的 SQL 比较慢,可以使用 SHOW PROCESSLIST 指令来查看当前 DRDS 数据库中实时的执行信息。其中 TIME 列代表的是该 SQL 已经执行的时间。

    mysql> SHOW PROCESSLIST WHERE COMMAND != 'Sleep';
    +---------------+-----------+--------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
    | ID            | USER      | DB                 | COMMAND     | TIME    | STATE                                                                 | INFO                                                                                                 | ROWS_SENT | ROWS_EXAMINED | ROWS_READ |
    +---------------+-----------+--------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
    | 0-0-352724126 | ifisibhk0 | test_123_wvvp_0000 | Query       |     13  | Sending data                                                          | /*DRDS /42.120.74.88/ac47e5a72801000/ */select `t_item`.`detail_url`,SUM(`t_item`.`price`) from `t_i |      NULL |          NULL |      NULL |
    | 0-0-352864311 | cowxhthg0 | NULL               | Binlog Dump |     17  | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                                                                                 |      NULL |          NULL |      NULL |
    | 0-0-402714795 | ifisibhk0 | test_123_wvvp_0005 | Alter       |     114 | Sending data                                                          | /*DRDS /42.120.74.88/ac47e5a72801000/ */ALTER TABLE `Persons` ADD `Birthday` date                    |      NULL |          NULL |      NULL |
    ......
    +---------------+-----------+--------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
    12 rows in set (0.03 sec)
    

    各列的信息如下:

    • ID:连接标识。

    • USER:执行该 SQL 的分库用户名。

    • DB:指定的数据库,如果没有指定则为 NULL。

    • COMMAND:正在执行的命令类型。SLEEP 代表空闲连接。其它命令详情见 MySQL 线程信息文档

    • TIME:SQL 已执行的时间,单位是秒。

    • STATE:当前的执行状态。详见 MySQL 线程状态文档

    • INFO:正在执行的 SQL 语句,有可能因为过长而无法完全显示,此时可以结合业务参数等信息把完整 SQL 推导出来。

    在当前的示例中定位到以下的慢 SQL:

    ALTER TABLE `Persons` ADD `Birthday` date

定位性能损耗节点

从慢 SQL 记录或者实时 SQL 执行信息中定位到慢 SQL 后,可以执行 TRACE 指令跟踪该 SQL 在 DRDS 和 RDS 上的运行时间,以便定位瓶颈。TRACE 命令会实际执行 SQL,在执行过程中记录所有节点消耗的时间,并返回执行结果。TRACE 及其他控制指令详情见自定义控制指令文档

说明: DRDS TRACE 命令需要保持连接的上下文信息,某些 GUI 客户端可能会使用连接池,导致命令不正常。因此建议使用 MySQL 命令行执行。

针对上文定位的慢 SQL,可以执行以下指令:

    mysql> trace select detail_url, sum(distinct price) from t_item group by detail_url;
    +---------------+---------------+
    | detail_url    | sum(price)    |
    +---------------+---------------+
    | www.xxx.com   | 1084326800.00 |
    | www.xx1.com   | 1084326800.00 |
    | www.xx2.com   | 1084326800.00 |
    | www.xx3.com   | 1084326800.00 |
    | www.xx4.com   | 1084326800.00 |
    | www.xx5.com   | 1084326800.00 |
    ......    
    +---------------+---------------+
    1 row in set (7 min 2.72 sec)

TRACE 指令执行完毕后,可以执行 SHOW TRACE 命令查看结果,根据每个组件的时间消耗来判断慢 SQL 的瓶颈。

 mysql> SHOW TRACE;
    +------+------------+--------------+----------------------------------------------------------+----------------------------------------------+---------------+--------------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------+--------+
    | ID   | TIMESTAMP  | TYPE         | GROUP_NAME                                               | DBKEY_NAME                                   | TIME_COST(MS) | CONNECTION_TIME_COST(MS) | ROWS | STATEMENT                                                                                                                                    | PARAMS |
    +------+------------+--------------+----------------------------------------------------------+----------------------------------------------+---------------+--------------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------+--------+
    |    0 |      0.000 | Optimize     | DRDS                                                     | DRDS                                         | 2             | 0.00                     |    0 | select detail_url, sum(price) from t_item group by detail_url                                                                                | NULL   |
    |    1 | 423507.342 | Merge Sorted | DRDS                                                     | DRDS                                         | 411307        | 0.00                     |    8 | Using Merge Sorted, Order By (`t_item`.`detail_url` asc )                                                                                    | NULL   |
    |    2 |      2.378 | Query        | TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | rdso6g5b6206sdq832ow_test_123_wvvp_0003_hbpz | 15            | 1.59                     |    1 | select `t_item`.`detail_url`,SUM(distinct `t_item`.`price`) from `t_item` group by `t_item`.`detail_url` order by `t_item`.`detail_url` asc  | NULL   |
    |    3 |      2.731 | Query        | TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | rdso6g5b6206sdq832ow_test_123_wvvp_0000_hbpz | 11            | 1.78                     |    1 | select `t_item`.`detail_url`,SUM(distinct `t_item`.`price`) from `t_item` group by `t_item`.`detail_url` order by `t_item`.`detail_url` asc  | NULL   |
    |    4 |      2.933 | Query        | TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | rdso6g5b6206sdq832ow_test_123_wvvp_0004_hbpz | 15            | 1.48                     |    1 | select `t_item`.`detail_url`,SUM(distinct `t_item`.`price`) from `t_item` group by `t_item`.`detail_url` order by `t_item`.`detail_url` asc  | NULL   |
    |    5 |      3.111 | Query        | TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | rdso6g5b6206sdq832ow_test_123_wvvp_0001_hbpz | 15            | 1.56                     |    1 | select `t_item`.`detail_url`,SUM(distinct `t_item`.`price`) from `t_item` group by `t_item`.`detail_url` order by `t_item`.`detail_url` asc  | NULL   |
    |    6 |      3.323 | Query        | TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | rdso6g5b6206sdq832ow_test_123_wvvp_0007_hbpz | 15            | 1.54                     |    1 | select `t_item`.`detail_url`,SUM(distinct `t_item`.`price`) from `t_item` group by `t_item`.`detail_url` order by `t_item`.`detail_url` asc  | NULL   |
    |    7 |      3.496 | Query        | TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | rdso6g5b6206sdq832ow_test_123_wvvp_0006_hbpz | 18            | 1.30                     |    1 | select `t_item`.`detail_url`,SUM(distinct `t_item`.`price`) from `t_item` group by `t_item`.`detail_url` order by `t_item`.`detail_url` asc  | NULL   |
    |    8 |      3.505 | Query        | TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | rdso6g5b6206sdq832ow_test_123_wvvp_0005_hbpz | 423507        | 1.97                     |    1 | select `t_item`.`detail_url`,SUM(distinct `t_item`.`price`) from `t_item` group by `t_item`.`detail_url` order by `t_item`.`detail_url` asc  | NULL   |
    |    9 |      3.686 | Query        | TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | rdso6g5b6206sdq832ow_test_123_wvvp_0002_hbpz | 14            | 1.47                     |    1 | select `t_item`.`detail_url`,SUM(distinct `t_item`.`price`) from `t_item` group by `t_item`.`detail_url` order by `t_item`.`detail_url` asc  | NULL   |
    |   10 | 423807.906 | Aggregate    | DRDS                                                     | DRDS                                         | 1413          | 0.00                     |    1 | Aggregate Function (SUM(`t_item`.`price`)), Group By (`t_item`.`detail_url` asc )                                                            | NULL   |
    +------+------------+--------------+----------------------------------------------------------+----------------------------------------------+---------------+--------------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------+--------+
    11 rows in set (0.01 sec)

SHOW TRACE 返回的结果中,根据 TIME_COST (单位毫秒)列可以判断哪个节点上的执行时间消耗大。同时可以看到对应的 GROUP_NAME (即 DRDS/RDS 节点),以及 STATEMENT 列信息(即正在执行的 SQL)。通过 GROUP_NAME 是否等于 DRDS 可以判断该慢节点存在于 DRDS 还是 RDS。

在以上结果中,分析可知是 DRDS 节点的 Merge Sorted 动作和 RDS 的 TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS 节点上消耗了大量时间。

定位性能损耗原因并处理

DRDS 慢节点处理

当慢 GROUP_NAME 是 DRDS 时,请检查执行过程中是否存在 Merge Sorted、Temp Table Merge、Aggregate 等计算耗时操作。如果存在的话请参考 SQL 优化文档进行优化。

RDS 慢节点处理

当慢节点在 RDS 时,请检查该 SQL 语句在 RDS 上的执行计划

在 DRDS 中,可以使用 /!TDDL:node={GROUP_NAME}*/ EXPLAIN 来查看某个 RDS 的执行计划。执行计划展示了 RDS 执行该 SQL 的过程信息,包括表间关联及索引信息等。

详细过程如下:

  1. 依据 GROUP_NAME 组装 HINT:/!TDDL:node='TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS'*/

  2. 将组装好的 HINT 及带 EXPLAIN 前缀的 STATEMENT 拼装成新的 SQL 并执行。EXPLAIN 指令不会真正执行,而只是显示该 SQL 的执行计划信息。

以上文定位的慢节点为例查询执行计划:

    mysql> /!TDDL:node='TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS'*/ EXPLAIN  select `t_item`.`detail_url`,SUM(distinct `t_item`.`price`) from `t_item` group by `t_item`.`detail_url` order by `t_item`.`detail_url` asc;
    +----+-------------+--------+------+---------------+-----+---------+-----+---------+---------------------------------+
    | id | select_type | table  | type | possible_keys | key | key_len | ref | rows    | Extra                           |
    +----+-------------+--------+------+---------------+-----+---------+-----+---------+---------------------------------+
    |  1 | SIMPLE      | t_item | ALL  | NULL          |NULL | NULL    | NULL| 1322263 | Using temporary; Using filesort |
    +----+-------------+--------+------+---------------+-----+---------+-----+---------+---------------------------------+
    1 row in set (0.01 sec)

根据观察发现以上 SQL 在 RDS 中执行时,出现了 Using temporary; Using filesort 现象,说明没有正确的使用索引从而导致执行缓慢。此时可以修正索引问题后重新执行。

如果观察执行计划后仍然无法判断 RDS 执行时间过长的原因,请查阅 RDS 性能调优文档

本文导读目录