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

🌞 建议反馈 产品缺陷
1

Bug 反馈
清晰准确地描述您发现的问题，提供任何可能复现问题的步骤有助于研发同学及时处理问题
【 TiDB 版本】v6.5.2
【 Bug 的影响】kill 杀不掉语句导致语句一直卡住

【可能的问题复现步骤】
创建tpch测试表fs=1。
mysql> select count(*) from nation;
±---------+
| count(*) |
±---------+
| 25 |
±---------+
1 row in set (0.00 sec)
select sleep(1),a.* from nation a; --表数据量较小，可以及时返回（比如整体25秒后返回，在第3秒kill该语句则立刻执行成功）

mysql> select count(*) from customer;
±---------+
| count(*) |
±---------+
| 150000 |
±---------+
1 row in set (0.02 sec)
select sleep(1),a.* from customer a; --表数据量较大，预计150000 秒后返回，在第N秒（N<150000 ）kill该语句则处于一直等待状态。

【看到的非预期行为】
kill query 语句未终止
【期望看到的行为】
kill query 直接触发语句中断

【相关组件及具体版本】

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

另外sleep函数引发的超内存不触发cancel问题，对于select sleep(1),a.* from table 这种形式，tikv-client堆积大量数据内存使用量超过tidb_mem_quota_query也不会发生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)
--禁用paging，让tikv-client堆积RPC消息更多
mysql> set tidb_enable_paging=OFF;
Query OK, 0 rows affected (0.00 sec)
--下面语句一直处于执行状态，另起会话观察其内存占用情况
mysql> select sleep(1),a.* from lineitem_bak a;

观察processlist，其用到了15GB还未被kill

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

开了个 Issue，后续这里跟踪 https://github.com/pingcap/tidb/issues/44675

4

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。