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 修改并查看慢日志队列无空间时后续日志的处理策略
slowQueueOverflowPolicy配置体现在bootstrap.cnf文件中
mysql> reload @@slow_query.queue_policy=1;
Query OK, 1 row affected (0.02 sec)
reload @@slow_query.queue_policy success
mysql> select * from dble_variables where variable_name = 'slowQueueOverflowPolicy';
+-------------------------+----------------+--------------------------------------------------+-----------+
| variable_name | variable_value | comment | read_only |
+-------------------------+----------------+--------------------------------------------------+-----------+
| slowQueueOverflowPolicy | 1 | Slow log queue overflow policy, the default is 2 | false |
+-------------------------+----------------+--------------------------------------------------+-----------+
1 row in set (0.01 sec)
2.1.15.11 查看某个连接的当前执行状态
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) | SHARDING_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) | SHARDING_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)