张信哲结婚了吗MySQLkill不掉线程的原因
⽬录
背景
问题描述
原因分析
模拟⼀下
总结⼀下
背景
在⽇常的使⽤过程中，时不时会遇到个别，或者⼤量的连接堆积在 MySQL 中的现象，这时⼀般会考虑使⽤ kill 命令强制杀死这些长时间堆积起来的连接，尽快释放连接数和数据库服务器的 CPU 资源。
问题描述
在实际操作 kill 命令的时候，有时候会发现连接并没有第⼀时间被 kill 掉，仍旧在 processlist ⾥⾯能看到，但是显⽰的Command 为 Killed，⽽不是常见的 Query 或者是 Execute 等。例如：
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 |
+----+------+--------------------+--------+---------+------+--------------+---------------------------------+
原因分析
遇事不决先翻官⽅⽂档，这⾥摘取部分官⽅⽂档的内容：
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.
官⽅⽂档第⼀段就很明确的说清楚了 kill 的作⽤机制：会给连接的线程设置⼀个线程级别的 kill 标记，等到下⼀次“标记检测”的时候才会⽣效。这也意味着如果下⼀次“标记检测”迟迟没有发⽣，那么就有可能会出现问题描述中的现象。
官⽅⽂档中列举了不少的场景，这⾥根据官⽅的描述列举⼏个⽐较常见的问题场景：
select 语句中进⾏ order by，group by 的时候，如果服务器 CPU 资源⽐较紧张，那么读取/获取⼀批数据的时间会变长，从⽽影响下⼀次“标记检测”的时间。
对⼤量数据进⾏ DML 操作的时候，kill 这⼀类 SQL 语句会触发事务回滚（InnoDB引擎），虽然语句被 kill 掉了，但是回滚操作也会⾮常久。陈秋莳承认与张靓颖恋情
kill alter 操作时，如果服务器的负载⽐较⾼，那么操作⼀批数据的时间会变长，从⽽影响下⼀次“标记检测”的时间。
李贞贤八卦其实参考 kill 的作⽤机制，做⼀个归纳性的描述的话，那么：任何阻塞/减慢 SQL 语句正常执⾏的⾏为，都会导致下⼀次“标记检测”推迟、⽆法发⽣，最终都会导致 kill 操作的失败。
模拟⼀下
这⾥借⽤⼀个参数innodb_thread_concurrency来模拟阻塞 SQL 语句正常执⾏的场景：
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.
参照官⽅⽂档的描述，这个参数设置得⽐较低的时候，超过数量限制的 InnoDB 查询会被阻塞。因此在本次模拟中，这个参数被设置了⼀个⾮常低的值。
mysql> show variables like '%innodb_thread_concurrency%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 1 |怎么申请邮箱
+---------------------------+-------+
1 row in set (0.00 sec)
然后开两个数据库连接（Session 1 和 Session 2），分别执⾏select sleep(3600) from sbtest.sbtest1语句，然后在第三个连接上 kill 掉 Session 2 的查询：
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>
可以看到，kill 命令执⾏之后，Session 2 的连接马上就断开了，但是 Session 2 发起的查询仍旧残留在 MySQL 中。当然，如果是因为innodb_thread_concurrency这个参数导致了类似的问题的话，直接使⽤set global的命令调⾼上限，或者直接设置为 0 就可以解决，这个参数的变更是实时对所有连接⽣效的。
总结⼀下
MySQL 的 kill 操作并不是想象中的直接强⾏终⽌数据库连接，只是发送了⼀个终⽌的信号，如果 SQL ⾃⾝的执⾏效率过慢，或者受到其他的因素影响（服务器负载⾼，触发⼤量数据回滚）的话，那么这个 kill 的操作很有可能并不能及时终⽌这些问题查询，反⽽可能会因为程序侧连接被断开之后触发重连，产⽣更多的低效查询，进⼀步拖垮数据库。
以上就是MySQL kill不掉线程的原因的详细内容，更多关于MySQL kill线程的资料请关注其它相关⽂章！
发布评论