全部产品
分布式关系型数据库 DRDS

如何排查 DRDS 慢 SQL

更新时间:2017-08-14 14:42:15   分享:   

在 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 明细文档

    1. mysql> SHOW SLOW limit 10;
    2. +-----------------+--------------+-------------------------+--------------+------------+-----------------------------------------------------------------+
    3. | TRACE_ID | HOST | START_TIME | EXECUTE_TIME | AFFECT_ROW | SQL |
    4. +-----------------+--------------+-------------------------+--------------+------------+-----------------------------------------------------------------+
    5. | 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; |
    6. ......
    7. +-----------------+--------------+-------------------------+--------------+------------+-----------------------------------------------------------------+
    8. 10 rows in set (0.01 sec)
  • 查看当前实时 SQL 执行信息

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

    1. mysql> SHOW PROCESSLIST WHERE COMMAND != 'Sleep';
    2. +---------------+-----------+--------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
    3. | ID | USER | DB | COMMAND | TIME | STATE | INFO | ROWS_SENT | ROWS_EXAMINED | ROWS_READ |
    4. +---------------+-----------+--------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
    5. | 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 |
    6. | 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 |
    7. | 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 |
    8. ......
    9. +---------------+-----------+--------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
    10. 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,可以执行以下指令:

  1. mysql> trace select detail_url, sum(distinct price) from t_item group by detail_url;
  2. +---------------+---------------+
  3. | detail_url | sum(price) |
  4. +---------------+---------------+
  5. | www.xxx.com | 1084326800.00 |
  6. | www.xx1.com | 1084326800.00 |
  7. | www.xx2.com | 1084326800.00 |
  8. | www.xx3.com | 1084326800.00 |
  9. | www.xx4.com | 1084326800.00 |
  10. | www.xx5.com | 1084326800.00 |
  11. ......
  12. +---------------+---------------+
  13. 1 row in set (7 min 2.72 sec)

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

  1. mysql> SHOW TRACE;
  2. +------+------------+--------------+----------------------------------------------------------+----------------------------------------------+---------------+--------------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------+--------+
  3. | ID | TIMESTAMP | TYPE | GROUP_NAME | DBKEY_NAME | TIME_COST(MS) | CONNECTION_TIME_COST(MS) | ROWS | STATEMENT | PARAMS |
  4. +------+------------+--------------+----------------------------------------------------------+----------------------------------------------+---------------+--------------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------+--------+
  5. | 0 | 0.000 | Optimize | DRDS | DRDS | 2 | 0.00 | 0 | select detail_url, sum(price) from t_item group by detail_url | NULL |
  6. | 1 | 423507.342 | Merge Sorted | DRDS | DRDS | 411307 | 0.00 | 8 | Using Merge Sorted, Order By (`t_item`.`detail_url` asc ) | NULL |
  7. | 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 |
  8. | 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 |
  9. | 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 |
  10. | 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 |
  11. | 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 |
  12. | 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 |
  13. | 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 |
  14. | 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 |
  15. | 10 | 423807.906 | Aggregate | DRDS | DRDS | 1413 | 0.00 | 1 | Aggregate Function (SUM(`t_item`.`price`)), Group By (`t_item`.`detail_url` asc ) | NULL |
  16. +------+------------+--------------+----------------------------------------------------------+----------------------------------------------+---------------+--------------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------+--------+
  17. 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 的执行计划信息。

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

  1. 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;
  2. +----+-------------+--------+------+---------------+-----+---------+-----+---------+---------------------------------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+-------------+--------+------+---------------+-----+---------+-----+---------+---------------------------------+
  5. | 1 | SIMPLE | t_item | ALL | NULL |NULL | NULL | NULL| 1322263 | Using temporary; Using filesort |
  6. +----+-------------+--------+------+---------------+-----+---------+-----+---------+---------------------------------+
  7. 1 row in set (0.01 sec)

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

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

本文导读目录
本文导读目录
以上内容是否对您有帮助?