Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[Improve]add an manager command to show recently(doing or done) query detail stage #1575

Closed
yanhuqing666 opened this issue Dec 26, 2019 · 1 comment
Closed

[Improve]add an manager command to show recently(doing or done) query detail stage #1575

yanhuqing666 opened this issue Dec 26, 2019 · 1 comment
Assignees
@yanhuqing666
Labels
Improvement
Milestone
2.19.11.0

Comments

@yanhuqing666
Copy link
Member

If you enable the slow query log, you can show recently(doing or done) query detail stage by using manager command show @@connection.sql.status where FRONT_ID= ?;.

If the query is finished, the result looks like trace.
If the query is on the way, the result will try to show every step. For example:
1.simple broadcast select:

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)
  1. join 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.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)
@yanhuqing666 yanhuqing666 added this to the 2.19.11.0 milestone Dec 26, 2019
@yanhuqing666 yanhuqing666 self-assigned this Dec 26, 2019
yanhuqing666 added a commit that referenced this issue Dec 26, 2019
@yanhuqing666
add show sql status detail #1575
abe3fa6
@yanhuqing666 yanhuqing666 mentioned this issue Dec 26, 2019
Merged
yanhuqing666 added a commit that referenced this issue Dec 26, 2019
@yanhuqing666
FIX null check #1575
3145dd2
yanhuqing666 added a commit that referenced this issue Dec 30, 2019
@yanhuqing666
add show sql status detail #1575 (#1576)
ead2e00 
*  add show sql status detail #1575

*  FIX null check #1575
yanhuqing666 added a commit that referenced this issue Jan 2, 2020
@yanhuqing666
#1575 help
23d1769
@yanhuqing666
Copy link
Member Author

https://github.com/actiontech/dble-docs-cn/blob/develop/2.Function/2.1_manager_cmd/2.1.15_slow_query.md#211510---%E6%9F%A5%E7%9C%8B%E6%9F%90%E4%B8%AA%E8%BF%9E%E6%8E%A5%E7%9A%84%E5%BD%93%E5%89%8D%E6%89%A7%E8%A1%8C%E7%8A%B6%E6%80%81

Lordess added a commit to Lordess/dble that referenced this issue May 28, 2020
@Lordess
import feature from 2.19.11.0/rel to 2.19.03/lts
74ad660 
* actiontech#1575 [Improve]add an manager command to show recently(doing or done) query detail stage
@Lordess Lordess mentioned this issue May 28, 2020
Merged
yanhuqing666 added a commit that referenced this issue May 29, 2020
@Lordess @yanhuqing666
release 2.19.03.4/lts (#1865)
2173c4e 
* split merge hander #1139 (#1140)

* reachedConCount thread safe #1139

* import feature from 2.19.11.0/rel to 2.19.03/lts

 * #1564 [Improve] add STAGE for show @@connection.sql

* import feature from 2.19.11.0/rel to 2.19.03/lts

* #1575 [Improve]add an manager command to show recently(doing or done) query detail stage

* release 2.19.03.4/lts

Co-authored-by: tiger.yan <yanhuqing666@gmail.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@yanhuqing666 yanhuqing666

Labels
Improvement
Projects
None yet
Milestone
2.19.11.0
Development

No branches or pull requests
1 participant
@yanhuqing666