2.1.15 慢日志相关命令

慢日志相关的命令:

2.1.15.1 查询慢查询日志的开启状态

mysql> show @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0                |
+------------------+
1 row in set (0.00 sec)

2.1.15.2 开启慢查询日志

mysql> enable @@slow_query_log;
Query OK, 1 row affected (0.09 sec)
enable slow_query_log success

2.1.15.3 关闭慢查询日志

mysql> disable @@slow_query_log;
Query OK, 1 row affected (0.03 sec)
disable slow_query_log success

2.1.15.4 查看慢查询日志统计阈值

mysql> show @@slow_query.time;
+-------------------+
| @@slow_query.time |
+-------------------+
| 100               |
+-------------------+
1 row in set (0.00 sec)

2.1.15.5 修改慢查询日志统计阈值

mysql> reload @@slow_query.time=200;
Query OK, 1 row affected (0.10 sec)
reload @@slow_query.time success

mysql> show @@slow_query.time;
+-------------------+
| @@slow_query.time |
+-------------------+
| 200               |
+-------------------+
1 row in set (0.00 sec)

2.1.15.6 查看慢查询日志刷盘周期

mysql> show @@slow_query.flushperiod;
+--------------------------+
| @@slow_query.flushperiod |
+--------------------------+
| 1                        |
+--------------------------+
1 row in set (0.00 sec)

2.1.15.7 修改慢查询日志刷盘周期

mysql> reload @@slow_query.flushperiod=2;
Query OK, 1 row affected (0.05 sec)
reload @@slow_query.flushPeriod success

mysql> show @@slow_query.flushperiod;
+--------------------------+
| @@slow_query.flushperiod |
+--------------------------+
| 2                        |
+--------------------------+
1 row in set (0.00 sec)

2.1.15.8 查看慢查询日志刷盘条数阈值

mysql> show @@slow_query.flushsize;
+------------------------+
| @@slow_query.flushsize |
+------------------------+
| 1000                   |
+------------------------+
1 row in set (0.01 sec)

2.1.15.9 修改慢查询日志刷盘条数阈值

mysql> reload @@slow_query.flushsize=1100;
Query OK, 1 row affected (0.03 sec)
reload @@slow_query.flushSize success

mysql> show @@slow_query.flushsize;
+------------------------+
| @@slow_query.flushsize |
+------------------------+
| 1100                   |
+------------------------+
1 row in set (0.00 sec)

2.1.15.10 查看某个连接的当前执行状态

show @@connection.sql.status where FRONT_ID= ?; 此功能需要开启慢日志才有效,当对应的连接当前query已经执行完毕时,执行此命令的结果与 trace功能相同。 如果query正在执行,本结果将试图展示query执行到哪一个步骤了。 例如,广播查询

mysql> show @@connection.sql.status where FRONT_ID= 1;
+--------------------------+-------------+-------------+--------------+-----------+-----------------------------+
| OPERATION                | START(ms)   | END(ms)     | DURATION(ms) | DATA_NODE | SQL/REF                     |
+--------------------------+-------------+-------------+--------------+-----------+-----------------------------+
|                 Read_SQL | 0.0         | 0.082598    | 0.082598     | -         | -                           |
|                Parse_SQL | 0.082598    | 0.676424    | 0.593826     | -         | -                           |
|        Route_Calculation | 0.676424    | 0.895382    | 0.218958     | -         | -                           |
| Prepare_to_Push/Optimize | 0.895382    | 6743.838628 | 6742.943246  | -         | -                           |
|              Execute_SQL | 6743.838628 | 6753.488422 | 9.649794     | dn1       | select * from sharding_4_t1 |
|              Execute_SQL | 6743.838628 | 6751.472835 | 7.634207     | dn3       | select * from sharding_4_t1 |
|              Execute_SQL | 6743.838628 | 6750.981646 | 7.143018     | dn4       | select * from sharding_4_t1 |
|              Execute_SQL | 6743.838628 | 6753.31394  | 9.475312     | dn2       | select * from sharding_4_t1 |
|             Fetch_result | 6753.488422 | 6754.383316 | 0.894894     | dn1       | select * from sharding_4_t1 |
|             Fetch_result | 6751.472835 | 6751.656604 | 0.183769     | dn3       | select * from sharding_4_t1 |
|             Fetch_result | 6750.981646 | 6751.188385 | 0.206739     | dn4       | select * from sharding_4_t1 |
|             Fetch_result | 6753.31394  | 6754.286055 | 0.972115     | dn2       | select * from sharding_4_t1 |
|          Write_to_Client | 6750.981646 | unfinished  | unknown      | -         | -                           |
+--------------------------+-------------+-------------+--------------+-----------+-----------------------------+
13 rows in set (0.04 sec)

再比如join

mysql> show @@connection.sql.status where FRONT_ID= 1;
+--------------------------+-------------+-------------+--------------+-------------------+--------------------------------------------------------------------------------------+
| OPERATION                | START(ms)   | END(ms)     | DURATION(ms) | DATA_NODE         | SQL/REF                                                                              |
+--------------------------+-------------+-------------+--------------+-------------------+--------------------------------------------------------------------------------------+
|                 Read_SQL | 0.0         | 0.039588    | 0.039588     | -                 | -                                                                                    |
|                Parse_SQL | 0.039588    | 0.756578    | 0.71699      | -                 | -                                                                                    |
|        Route_Calculation | 0.756578    | 1.5547      | 0.798122     | -                 | -                                                                                    |
| Prepare_to_Push/Optimize | 1.5547      | 3.428551    | 1.873851     | -                 | -                                                                                    |
|              Execute_SQL | 3.428551    | 2362.10579  | 2358.677239  | dn1_0             | select `a`.`age`,`a`.`id`,`a`.`name` from  `sharding_4_t1` `a` ORDER BY `a`.`id` ASC |
|             Fetch_result | 2362.10579  | unfinished  | unknown      | dn1_0             | select `a`.`age`,`a`.`id`,`a`.`name` from  `sharding_4_t1` `a` ORDER BY `a`.`id` ASC |
|              Execute_SQL | 3.428551    | 2362.122407 | 2358.693856  | dn2_0             | select `a`.`age`,`a`.`id`,`a`.`name` from  `sharding_4_t1` `a` ORDER BY `a`.`id` ASC |
|             Fetch_result | 2362.122407 | unfinished  | unknown      | dn2_0             | select `a`.`age`,`a`.`id`,`a`.`name` from  `sharding_4_t1` `a` ORDER BY `a`.`id` ASC |
|              Execute_SQL | 3.428551    | 2362.307153 | 2358.878602  | dn3_0             | select `a`.`age`,`a`.`id`,`a`.`name` from  `sharding_4_t1` `a` ORDER BY `a`.`id` ASC |
|             Fetch_result | 2362.307153 | unfinished  | unknown      | dn3_0             | select `a`.`age`,`a`.`id`,`a`.`name` from  `sharding_4_t1` `a` ORDER BY `a`.`id` ASC |
|              Execute_SQL | 3.428551    | 2364.523615 | 2361.095064  | dn4_0             | select `a`.`age`,`a`.`id`,`a`.`name` from  `sharding_4_t1` `a` ORDER BY `a`.`id` ASC |
|             Fetch_result | 2364.523615 | unfinished  | unknown      | dn4_0             | select `a`.`age`,`a`.`id`,`a`.`name` from  `sharding_4_t1` `a` ORDER BY `a`.`id` ASC |
|          MERGE_AND_ORDER | 2362.639012 | unfinished  | unknown      | merge_and_order_1 | dn1_0; dn2_0; dn3_0; dn4_0                                                           |
|            SHUFFLE_FIELD | 4178.383366 | unfinished  | unknown      | shuffle_field_1   | merge_and_order_1                                                                    |
|              Execute_SQL | 3.428551    | 2365.71371  | 2362.285159  | dn1_1             | select `b`.`id` from  `sharding_2_t1` `b` ORDER BY `b`.`id` ASC                      |
|             Fetch_result | 2365.71371  | unfinished  | unknown      | dn1_1             | select `b`.`id` from  `sharding_2_t1` `b` ORDER BY `b`.`id` ASC                      |
|              Execute_SQL | 3.428551    | 2365.952707 | 2362.524156  | dn2_1             | select `b`.`id` from  `sharding_2_t1` `b` ORDER BY `b`.`id` ASC                      |
|             Fetch_result | 2365.952707 | unfinished  | unknown      | dn2_1             | select `b`.`id` from  `sharding_2_t1` `b` ORDER BY `b`.`id` ASC                      |
|          MERGE_AND_ORDER | 2366.164823 | unfinished  | unknown      | merge_and_order_2 | dn1_1; dn2_1                                                                         |
|            SHUFFLE_FIELD | not started | unfinished  | unknown      | -                 | -                                                                                    |
|                     JOIN | not started | unfinished  | unknown      | -                 | -                                                                                    |
|            SHUFFLE_FIELD | not started | unfinished  | unknown      | -                 | -                                                                                    |
|          Write_to_Client | not started | unfinished  | unknown      | -                 | -                                                                                    |
+--------------------------+-------------+-------------+--------------+-------------------+--------------------------------------------------------------------------------------+
23 rows in set (0.04 sec)

results matching ""

    No results matching ""