Reasons why MySQL kill cannot kill threads

background

In daily use, you may encounter individual or large number of connections piling up in MySQL from time to time. At this time, you will generally consider using the kill command to forcibly kill these long-pile connections to release the number of connections and the CPU resources of the database server as soon as possible.

Problem Description

When actually using the kill command, you may find that the connection is not killed immediately and can still be seen in processlist, but the displayed Command is Killed instead of the common Query or Execute. For example:

mysql> show processlist;
+----+------+--------------------+--------+---------+------+--------------+--------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+--------------------+--------+---------+------+--------------+--------------------------------+
| 31 | root | 192.168.1.10:50410 | sbtest | Query | 0 | starting | show processlist |
| 32 | root | 192.168.1.10:50412 | sbtest | Query | 62 | User sleep | select sleep(3600) from sbtest1 |
| 35 | root | 192.168.1.10:51252 | sbtest | Killed | 47 | Sending data | select sleep(100) from sbtest1 |
| 36 | root | 192.168.1.10:51304 | sbtest | Query | 20 | Sending data | select sleep(3600) from sbtest1 |
+----+------+--------------------+--------+---------+------+--------------+--------------------------------+

Cause Analysis

When in doubt, look up the official documentation first. Here are some excerpts from the official documentation:

When you use KILL, a thread-specific kill flag is set for the thread. In most cases, it might take some time for the thread to die because the kill flag is checked only at specific intervals: During SELECT operations, for ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.
ALTER TABLE operations that make a table copy check the kill flag periodically for each few copied rows read from the original table. If the kill flag was set, the statement is aborted and the temporary table is deleted.
The KILL statement returns without waiting for confirmation, but the kill flag check aborts the operation within a reasonably small amount of time. Aborting the operation to perform any necessary cleanup also takes some time.
During UPDATE or DELETE operations, the kill flag is checked after each block read and after each updated or deleted row. If the kill flag is set, the statement is aborted. If you are not using transactions, the changes are not rolled back.
GET_LOCK() aborts and returns NULL.
If the thread is in the table lock handler (state: Locked), the table lock is quickly aborted.
If the thread is waiting for free disk space in a write call, the write is aborted with a "disk full" error message.

The first paragraph of the official document clearly states the mechanism of kill: a thread-level kill mark will be set for the connected thread, which will not take effect until the next "mark detection". This also means that if the next "mark detection" does not occur in time, the phenomenon described in the problem may occur.

The official documentation lists a number of scenarios. Here are a few common problem scenarios based on the official description:

• When performing order by and group by in a select statement, if the server CPU resources are tight, the time to read/acquire a batch of data will be longer, thus affecting the time of the next "mark detection".
• When performing DML operations on large amounts of data, SQL statements such as kill will trigger a transaction rollback (InnoDB engine). Although the statement is killed, the rollback operation will take a very long time.
• During the kill alter operation, if the server load is high, the time to operate a batch of data will be longer, thus affecting the time of the next "mark detection".
• In fact, referring to the mechanism of kill, if we make an inductive description, then: any behavior that blocks/slows down the normal execution of SQL statements will cause the next "mark detection" to be postponed or unable to occur, and will eventually lead to the failure of the kill operation.
  

Simulate it

Here we use a parameter innodb_thread_concurrency to simulate the scenario of blocking the normal execution of SQL statements:

Defines the maximum number of threads permitted inside of InnoDB. A value of 0 (the default) is interpreted as infinite concurrency (no limit). This variable is intended for performance tuning on high concurrency systems.

According to the official documentation, when this parameter is set to a low value, InnoDB queries that exceed the limit will be blocked. Therefore, in this simulation, this parameter was set to a very low value.

mysql> show variables like '%innodb_thread_concurrency%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 1 |
+---------------------------+-------+
1 row in set (0.00 sec)

Then open two database connections (Session 1 and Session 2), execute select sleep(3600) from sbtest.sbtest1 statement in each, and then kill the query of Session 2 on the third connection:

Session 1:
mysql> select sleep(3600) from sbtest.sbtest1;

Session 2:
mysql> select sleep(3600) from sbtest.sbtest1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Session 3:
mysql> show processlist;
+----+------+--------------------+------+---------+------+--------------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+--------------------+------+---------+------+--------------+----------------------------------------+
| 44 | root | 172.16.64.10:39290 | NULL | Query | 17 | User sleep | select sleep(3600) from sbtest.sbtest1 |
| 45 | root | 172.16.64.10:39292 | NULL | Query | 0 | starting | show processlist |
| 46 | root | 172.16.64.10:39294 | NULL | Query | 5 | Sending data | select sleep(3600) from sbtest.sbtest1 |
+----+------+--------------------+------+---------+------+--------------+----------------------------------------+
3 rows in set (0.00 sec)

mysql> kill 46;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+------+--------------------+------+---------+------+--------------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+--------------------+------+---------+------+--------------+----------------------------------------+
| 44 | root | 172.16.64.10:39290 | NULL | Query | 26 | User sleep | select sleep(3600) from sbtest.sbtest1 |
| 45 | root | 172.16.64.10:39292 | NULL | Query | 0 | starting | show processlist |
| 46 | root | 172.16.64.10:39294 | NULL | Killed | 14 | Sending data | select sleep(3600) from sbtest.sbtest1 |
+----+------+--------------------+------+---------+------+--------------+----------------------------------------+
3 rows in set (0.00 sec)

mysql>

As you can see, after the kill command is executed, the connection of Session 2 is disconnected immediately, but the query initiated by Session 2 still remains in MySQL. Of course, if similar problems are caused by innodb_thread_concurrency parameter, you can directly use the set global command to increase the upper limit, or directly set it to 0 to solve it. The change of this parameter is effective in real time for all connections.

To sum up

The kill operation of MySQL does not directly and forcibly terminate the database connection as imagined. It only sends a termination signal. If the execution efficiency of SQL itself is too slow, or is affected by other factors (high server load, triggering a large amount of data rollback), then this kill operation may not be able to terminate these problematic queries in time. On the contrary, it may trigger a reconnection after the program side connection is disconnected, resulting in more inefficient queries and further dragging down the database.

The above is the details of why MySQL kill cannot kill threads. For more information about MySQL kill threads, please pay attention to other related articles on 123WORDPRESS.COM!