|
Killed query locks ExeMgr and PrimProc and get all cpu resources
after query is being killed it's observed that ExeMgr and PrimProc are still still consuming all modules cpu .
Note : ExeMgr and PrimProc get all cpu resources even after the query was remove from the mysql processlist. Below in 15 min after query was being killed
attached also traces of ExeMgr and PrimProc
run query towards 1TB data loaded DB
kill the query
check that ExeMgr and PrimProc are still using all cpu resources
WITH frequent_ss_items AS
|
(
|
SELECT Substr(i_item_desc, 1, 30 ) itemdesc,
|
i_item_sk item_sk,
|
d_date solddate,
|
Count(*) cnt
|
FROM store_sales,
|
date_dim,
|
item
|
WHERE ss_sold_date_sk = d_date_sk
|
AND ss_item_sk = i_item_sk
|
AND d_year IN (2000,
|
2000+1,
|
2000+2,
|
2000+3)
|
GROUP BY Substr(i_item_desc, 1, 30),
|
i_item_sk,
|
d_date
|
HAVING Count(*) >4), max_store_sales AS
|
(
|
SELECT Max(csales) tpcds_cmax
|
FROM (
|
SELECT c_customer_sk,
|
Sum(ss_quantity*ss_sales_price) csales
|
FROM store_sales,
|
customer,
|
date_dim
|
WHERE ss_customer_sk = c_customer_sk
|
AND ss_sold_date_sk = d_date_sk
|
AND d_year IN (2000,
|
2000+1,
|
2000+2,
|
2000+3)
|
GROUP BY c_customer_sk) a), best_ss_customer AS
|
(
|
SELECT c_customer_sk,
|
Sum(ss_quantity *ss_sales_price) ssales
|
FROM store_sales,
|
customer
|
WHERE ss_customer_sk = c_customer_sk
|
GROUP BY c_customer_sk
|
HAVING Sum(ss_quantity*ss_sales_price) >
|
(
|
SELECT (50/100.0)*tpcds_cmax
|
FROM max_store_sales))
|
SELECT Sum(sales)
|
FROM (
|
SELECT cs_quantity*cs_list_price sales
|
FROM catalog_sales,
|
date_dim
|
WHERE d_year = 2000
|
AND d_moy = 2
|
AND cs_sold_date_sk = d_date_sk
|
AND cs_item_sk IN
|
(
|
SELECT item_sk
|
FROM frequent_ss_items)
|
AND cs_bill_customer_sk IN
|
(
|
SELECT c_customer_sk
|
FROM best_ss_customer)
|
UNION ALL
|
SELECT ws_quantity*ws_list_price sales
|
FROM web_sales,
|
date_dim
|
WHERE d_year = 2000
|
AND d_moy = 2
|
AND ws_sold_date_sk = d_date_sk
|
AND ws_item_sk IN
|
(
|
SELECT item_sk
|
FROM frequent_ss_items)
|
AND ws_bill_customer_sk IN
|
(
|
SELECT c_customer_sk
|
FROM best_ss_customer)) a limit 100
|
MariaDB [(none)]> show processlist ;
|
+----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
|
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
|
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
|
| 12 | root | localhost | tpcds_1000 | Query | 201 | executing | create temporary table infinidb_vtable.$vtable_12 engine = aria as with frequent_ss_items as (sele | 0.000 |
|
| 14 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
|
+----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
|
7 rows in set (0.00 sec)
|
|
MariaDB [(none)]> kill 12 ;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [(none)]> show processlist ;
|
+----+-------------+-----------+------------+---------+------+--------------------------+----------------- -------------------------------------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+-------------+-----------+------------+---------+------+--------------------------+----------------- -------------------------------------------------------------------------------------+----------+
|
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
|
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
|
| 12 | root | localhost | tpcds_1000 | Killed | 222 | executing | create temporary table infinidb_vtable.$vtable_12 engine = aria as with frequent_ss_items as (sele | 0.000 |
|
| 14 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
|
+----+-------------+-----------+------------+---------+------+--------------------------+----------------- -------------------------------------------------------------------------------------+----------+
|
7 rows in set (0.00 sec)
|
|
MariaDB [(none)]> show processlist ;
|
+----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
|
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
|
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
|
| 15 | root | localhost | tpcds_1000 | Query | 29 | executing | create temporary table infinidb_vtable.$vtable_15 engine = aria as with frequent_ss_items as (sele | 0.000 |
|
| 16 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
|
+----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
|
7 rows in set (0.01 sec)
|
|
UM1
Tasks: 214 total, 2 running, 212 sleeping, 0 stopped, 0 zombie
|
%Cpu(s): 96.9 us, 0.0 sy, 0.0 ni, 1.6 id, 0.0 wa, 0.0 hi, 1.6 si, 0.0 st
|
KiB Mem : 61848092 total, 35093536 free, 26280216 used, 474340 buff/cache
|
KiB Swap: 1048572 total, 949844 free, 98728 used. 35036596 avail Mem
|
|
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
|
22307 root 19 -1 27.5g 24.4g 11572 S 381.2 41.4 84:28.69 ExeMgr
|
29494 root 20 0 162012 2272 1552 R 6.2 0.0 0:00.01 top
|
|
PM1
Tasks: 193 total, 1 running, 191 sleeping, 1 stopped, 0 zombie
|
%Cpu(s): 56.8 us, 0.6 sy, 0.0 ni, 42.1 id, 0.4 wa, 0.0 hi, 0.1 si, 0.0 st
|
KiB Mem : 61848092 total, 13504316 free, 46468524 used, 1875252 buff/cache
|
KiB Swap: 1048572 total, 883196 free, 165376 used. 14678580 avail Mem
|
|
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
|
21489 root 19 -1 45.4g 43.7g 12328 S 226.8 74.1 54:31.36 PrimProc
|
13071 root 20 0 1705576 10484 3200 S 1.3 0.0 17:49.15 ProcMgr
|
14704 root 20 0 162012 2332 1596 R 0.3 0.0 0:00.64 top
|
|
|
MCOL-2104.url
um_trace.txt