When adding a sleep function with a slightly large amount of data, it cannot be killed

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 数据量略大时加入sleep函数，kill不掉

| username: 人如其名

Bug Report
Clearly and accurately describe the issue you found. Providing any possible steps to reproduce the problem will help the development team address it promptly.
【TiDB Version】v6.5.2
【Impact of the Bug】Unable to kill the statement, causing it to hang indefinitely

【Possible Steps to Reproduce the Issue】
Create tpch test table fs=1.
mysql> select count(*) from nation;
±---------+
| count(*) |
±---------+
| 25 |
±---------+
1 row in set (0.00 sec)
select sleep(1),a.* from nation a; – The table has a small amount of data and can return promptly (e.g., returns after 25 seconds in total, and if killed at the 3rd second, it executes successfully immediately)

mysql> select count(*) from customer;
±---------+
| count(*) |
±---------+
| 150000 |
±---------+
1 row in set (0.02 sec)
select sleep(1),a.* from customer a; – The table has a large amount of data, expected to return after 150000 seconds. If killed at the Nth second (N<150000), it remains in a waiting state indefinitely.

【Observed Unexpected Behavior】
kill query statement does not terminate
【Expected Behavior】
kill query should immediately trigger statement interruption

【Related Components and Specific Versions】

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.5.2
Edition: Community
Git Commit Hash: 29116c0256c52b224da2b34d712c1063d171c0ad
Git Branch: heads/refs/tags/v6.5.2
UTC Build Time: 2023-04-19 10:52:06
GoVersion: go1.19.8
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2
| username: 人如其名 | Original post link

Additionally, the issue of excessive memory usage caused by the sleep function not triggering cancel occurs with queries like select sleep(1),a.* from table. In this form, even if the memory usage of tikv-client accumulates a large amount of data exceeding tidb_mem_quota_query, it will not trigger an OOM-kill.

mysql> select count(*) from lineitem_bak;
+-----------+
| count(*)  |
+-----------+
| 179998372 |
+-----------+
1 row in set (17.05 sec)

mysql> explain select count(*) from lineitem_bak;
+----------------------------+--------------+-----------+--------------------------------------------+-----------------------------------+
| id                         | estRows      | task      | access object                              | operator info                     |
+----------------------------+--------------+-----------+--------------------------------------------+-----------------------------------+
| StreamAgg_20               | 1.00         | root      |                                            | funcs:count(Column#72)->Column#67 |
| └─IndexReader_21           | 1.00         | root      |                                            | index:StreamAgg_8                 |
|   └─StreamAgg_8            | 1.00         | cop[tikv] |                                            | funcs:count(1)->Column#72         |
|     └─IndexFullScan_19     | 179998372.00 | cop[tikv] | table:lineitem_bak, index:idx1(L_SHIPDATE) | keep order:false                  |
+----------------------------+--------------+-----------+--------------------------------------------+-----------------------------------+
4 rows in set (0.00 sec)

mysql> desc lineitem_bak;
+-----------------+---------------+------+------+---------+-------+
| Field           | Type          | Null | Key  | Default | Extra |
+-----------------+---------------+------+------+---------+-------+
| L_ORDERKEY      | bigint(20)    | NO   | PRI  | NULL    |       |
| L_PARTKEY       | bigint(20)    | NO   |      | NULL    |       |
| L_SUPPKEY       | bigint(20)    | NO   |      | NULL    |       |
| L_LINENUMBER    | bigint(20)    | NO   | PRI  | NULL    |       |
| L_QUANTITY      | decimal(15,2) | NO   |      | NULL    |       |
| L_EXTENDEDPRICE | decimal(15,2) | NO   |      | NULL    |       |
| L_DISCOUNT      | decimal(15,2) | NO   |      | NULL    |       |
| L_TAX           | decimal(15,2) | NO   |      | NULL    |       |
| L_RETURNFLAG    | char(1)       | NO   |      | NULL    |       |
| L_LINESTATUS    | char(1)       | NO   |      | NULL    |       |
| L_SHIPDATE      | date          | NO   | MUL  | NULL    |       |
| L_COMMITDATE    | date          | NO   |      | NULL    |       |
| L_RECEIPTDATE   | date          | NO   |      | NULL    |       |
| L_SHIPINSTRUCT  | char(25)      | NO   |      | NULL    |       |
| L_SHIPMODE      | char(10)      | NO   |      | NULL    |       |
| L_COMMENT       | varchar(44)   | NO   |      | NULL    |       |
| col1            | decimal(15,2) | YES  |      | NULL    |       |
| col2            | decimal(15,2) | YES  |      | NULL    |       |
| col3            | decimal(15,2) | YES  |      | NULL    |       |
| col4            | decimal(15,2) | YES  |      | NULL    |       |
| col5            | decimal(15,2) | YES  |      | NULL    |       |
| col6            | decimal(15,2) | YES  |      | NULL    |       |
| col7            | decimal(15,2) | YES  |      | NULL    |       |
| col8            | decimal(15,2) | YES  |      | NULL    |       |
| col9            | decimal(15,2) | YES  |      | NULL    |       |
| col10           | decimal(15,2) | YES  |      | NULL    |       |
| col11           | decimal(15,2) | YES  |      | NULL    |       |
| col12           | decimal(15,2) | YES  |      | NULL    |       |
| col13           | decimal(15,2) | YES  |      | NULL    |       |
| col14           | decimal(15,2) | YES  |      | NULL    |       |
| col15           | decimal(15,2) | YES  |      | NULL    |       |
| col16           | decimal(15,2) | YES  |      | NULL    |       |
| col17           | decimal(15,2) | YES  |      | NULL    |       |
| col18           | decimal(15,2) | YES  |      | NULL    |       |
| col19           | decimal(15,2) | YES  |      | NULL    |       |
| col20           | decimal(15,2) | YES  |      | NULL    |       |
| col21           | decimal(15,2) | YES  |      | NULL    |       |
| col22           | decimal(15,2) | YES  |      | NULL    |       |
| col23           | decimal(15,2) | YES  |      | NULL    |       |
| col24           | decimal(15,2) | YES  |      | NULL    |       |
| col25           | decimal(15,2) | YES  |      | NULL    |       |
| col26           | decimal(15,2) | YES  |      | NULL    |       |
| col27           | decimal(15,2) | YES  |      | NULL    |       |
| col28           | decimal(15,2) | YES  |      | NULL    |       |
| col29           | decimal(15,2) | YES  |      | NULL    |       |
| col30           | decimal(15,2) | YES  |      | NULL    |       |
| col31           | decimal(15,2) | YES  |      | NULL    |       |
| col32           | decimal(15,2) | YES  |      | NULL    |       |
| col33           | decimal(15,2) | YES  |      | NULL    |       |
| col34           | decimal(15,2) | YES  |      | NULL    |       |
| col35           | decimal(15,2) | YES  |      | NULL    |       |
| col36           | decimal(15,2) | YES  |      | NULL    |       |
| col37           | decimal(15,2) | YES  |      | NULL    |       |
| col38           | decimal(15,2) | YES  |      | NULL    |       |
| col39           | decimal(15,2) | YES  |      | NULL    |       |
| col40           | decimal(15,2) | YES  |      | NULL    |       |
| col41           | decimal(15,2) | YES  |      | NULL    |       |
| col42           | decimal(15,2) | YES  |      | NULL    |       |
| col43           | decimal(15,2) | YES  |      | NULL    |       |
| col44           | decimal(15,2) | YES  |      | NULL    |       |
| col45           | decimal(15,2) | YES  |      | NULL    |       |
| col46           | decimal(15,2) | YES  |      | NULL    |       |
| col47           | decimal(15,2) | YES  |      | NULL    |       |
| col48           | decimal(15,2) | YES  |      | NULL    |       |
| col49           | decimal(15,2) | YES  |      | NULL    |       |
| col50           | decimal(15,2) | YES  |      | NULL    |       |
+-----------------+---------------+------+------+---------+-------+
66 rows in set (0.00 sec)
mysql> show variables like 'tidb_mem_quota_query';
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| tidb_mem_quota_query | 1073741824 |
+----------------------+------------+
1 row in set (0.00 sec)
mysql> show variables like '%oom%';
+--------------------------------+--------+
| Variable_name                  | Value  |
+--------------------------------+--------+
| tidb_enable_tmp_storage_on_oom | ON     |
| tidb_mem_oom_action            | CANCEL |
+--------------------------------+--------+
2 rows in set (0.00 sec)
-- Disable paging to allow tikv-client to accumulate more RPC messages
mysql> set tidb_enable_paging=OFF;
Query OK, 0 rows affected (0.00 sec)
-- The following statement remains in execution state, observe its memory usage in another session
mysql> select sleep(1),a.* from lineitem_bak a;

Observing the processlist, it used 15GB and has not been killed.

mysql> select * from information_schema.processlist where id=937076376958140833\G
*************************** 1. row ***************************
      ID: 937076376958140833
    USER: root
    HOST: 192.168.31.200:50786
      DB: tpch
 COMMAND: Query
    TIME: 251
   STATE: autocommit
    INFO: select sleep(1),a.* from lineitem_bak a
  DIGEST: e08acffb1b819d8e81d84d651cc4e0f4a60e33f04773e58032495900d79da40c
     MEM: 15557042042
    DISK: 0
TxnStart: 06-11 08:59:55.400(442091489302937601)
1 row in set (0.00 sec)
3
| username: aytrack | Original post link

I have opened an issue, and will follow up here: can not kill the session while query with sleep · Issue #44675 · pingcap/tidb · GitHub

4
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.