Release Notes
Product Announcements
CREATE TABLE lineitem (L_ORDERKEY INTEGER NOT NULL,L_PARTKEY INTEGER NOT NULL,L_SUPPKEY INTEGER NOT NULL,L_LINENUMBER INTEGER NOT NULL,L_QUANTITY DECIMAL(15, 2) NOT NULL,L_EXTENDEDPRICE DECIMAL (15, 2) NOT NULL,L_DISCOUNT DECIMAL(15, 2) NOT NULL,L_TAX DECIMAL(15, 2) NOT NULL,L_RETURNFLAG CHAR(1) NOT NULL,L_LINESTATUS CHAR(1) NOT NULL,L_SHIPDATE DATE NOT NULL,L_COMMITDATE DATE NOT NULL,L_RECEIPTDATE DATE NOT NULL,L_SHIPINSTRUCT CHAR(25) NOT NULL,L_SHIPMODE CHAR(10) NOT NULL,L_COMMENT VARCHAR(44) NOT NULL);
SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qtyFROM lineitemWHERE l_shipdate <= '1998-09-02'GROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatus;
EXPLAIN SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qtyFROM lineitemWHERE l_shipdate <= '1998-09-02'GROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatus;
MySQL [tpch100g]> explain SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty FROM lineitem WHERE l_shipdate <= '1998-09-02' GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus;+----+-------------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------------------------------------+| 1 | SIMPLE | lineitem | NULL | ALL | i_l_shipdate | NULL | NULL | NULL | 593184480 | 50.00 | Parallel scan (4 workers); Using where; Using temporary || 1 | SIMPLE | <sender1> | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00 | Send to (<receiver1>) || 1 | SIMPLE | <receiver1> | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00 | Receive from (<sender1>); Using temporary; Using filesort |+----+-------------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------------------------------------+3 rows in set, 1 warning (0.00 sec)
EXPLAIN format=tree query SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qtyFROM lineitemWHERE l_shipdate <= '1998-09-02'GROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatus;
MySQL [tpch100g]> explain format=tree SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty FROM lineitem WHERE l_shipdate <= '1998-09-02' GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus\\G*************************** 1. row ***************************EXPLAIN: -> Sort: lineitem.L_RETURNFLAG, lineitem.L_LINESTATUS-> Table scan on <temporary>-> Final Aggregate using temporary table-> PX Receiver (slice: 0; workers: 1)-> PX Sender (slice: 1; workers: 4)-> Table scan on <temporary>-> Aggregate using temporary table-> Filter: (lineitem.L_SHIPDATE <= DATE'1998-09-02') (cost=65449341.10 rows=296592240)-> Parallel table scan on lineitem (cost=65449341.10 rows=593184480)1 row in set (0.00 sec)
lineitem table.show processlist command displays which threads are running. You can view not only the total number of current connections but also the connection status to identify abnormal query statements.
Based on the show processlist command, TDSQL-C for MySQL offers the proprietary show parallel processlist statement, which displays only the threads related to parallel query and filters out irrelevant threads.
Sample SQL statement:SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qtyFROM lineitemWHERE l_shipdate <= '1998-09-02'GROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatus;
show processlist query result:mysql> show processlist;+--------+-------------+-----------------+-----------+---------+-------+------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+-------------+-----------------+-----------+---------+-------+------------+------------------------------------------------------------------------------------------------------+| 7 | tencentroot | 127.0.0.1:49238 | NULL | Sleep | 0 | | NULL || 11 | tencentroot | 127.0.0.1:49262 | NULL | Sleep | 0 | | NULL || 13 | tencentroot | 127.0.0.1:49288 | NULL | Sleep | 1 | | NULL || 237062 | tencentroot | localhost | tpch100g | Query | 24 | Scheduling | SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty FROM lineitem WHERE l_shipdate <= '199 || 237107 | tencentroot | localhost | NULL | Query | 0 | init | show processlist |+--------+-------------+-----------------+-----------+---------+-------+------------+------------------------------------------------------------------------------------------------------+6 rows in set (0.00 sec)
show parallel processlist query result:mysql> show parallel processlist;+--------+-------------+-----------+----------+---------+------+-------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+-------------+-----------+----------+---------+------+-------------+------------------------------------------------------------------------------------------------------+| 237062 | tencentroot | localhost | tpch100g | Query | 18 | Scheduling | SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty FROM lineitem WHERE l_shipdate <= '199 || 237110 | | | | Task | 18 | Task runing | connection 237062, worker 0, task 1 || 237111 | | | | Task | 18 | Task runing | connection 237062, worker 1, task 1 || 237112 | | | | Task | 18 | Task runing | connection 237062, worker 2, task 1 || 237113 | | | | Task | 18 | Task runing | connection 237062, worker 3, task 1 |+--------+-------------+-----------+----------+---------+------+-------------+------------------------------------------------------------------------------------------------------+5 rows in set (0.00 sec)
info, all these four threads are executing task 1.show processlist, show parallel processlist can precisely find all running threads of parallel query and will not be affected by other threads.Was this page helpful?
You can also Contact sales or Submit a Ticket for help.
Help us improve! Rate your documentation experience in 5 mins.
Feedback