为什么MySQL show processlist查看所有线程时有killed状态线程

MySQL中通过kill命令杀掉线程时这个线程并没有立即消失,SHOW PROCESSLIST命令返回线程被标记为killed状态。为什么MySQL不能立即终止一个线程?因为MySQL一般会需要一段时间来执行结束线程前的扫尾工作(多数情况下就是执行回滚操作或删除临时表)!

你是否在MySQL中通过kill命令杀掉过一个query线程,但是这个线程并没有立即消失而是会再运行一段时间?或者你是否注意到通过SHOW PROCESSLIST命令发现有些线程被标记为了killed状态却存在了很长一段时间而没有真正死掉?本文主要解释和分析为什么MySQL有时候不能立即终止一个query查询,以及是否存在一种方法来立即执行kill命令?

1. 关于MySQL线程和连接

MySQL对于每一个客户端连接都产生一个独立的线程。当MySQL收到一个query时,这个query会被收到该query的连接所对应的线程所处理。如图所示,通过运行SHOW PROCESSLIST命令可以查看哪些线程正在运行,并可以使用kill命令终止任何一个出现在表中的线程。

mysql show processlist

2. 当执行kill命令时到底发生了什么

kill命令完成的语法格式为:kill [QUERY | CONNECTION] thread_id,thread_id指的就是SHOW PROCESSLIST命令返回的结果中第一列的值。可选参数指定是否只执行query还是结束整个连接。默认选择是结束整个连接,即kill CONNECTION thread_id。

执行kill命令的本质其实就是在指定的线程上设置特定的标志,所以kill操作结果并不会与对应的请求同步完成。在大多数情况下需要花一定的时间来真正结束一个query或线程。在sql语句执行的各个阶段都会检查标志,当kill命令恰好发生时到底需要怎么做取决于线程实际上是在做什么。例如:

  1. 修改一个表的结构时,在从原始表中读取一行数据并写入一个新的临时表中时,该标志会被检查。在这种情况下如果发现了因为kill命令而出现的标志位,那么临时表会被删除,而原始的表结构不会被改变;
  2. UPDATE和DELETE每一行时,这种额外的标志位检查也会执行。当kill命令请求结束一个UPDATE或DELETE操作时,若对应的操作已经对数据进行了修改,那么MySQL必须进行回滚。
  3. 执行SELECT操作时也会在读出数据后进行标志位检查。

当然,上面只是最基本的3个例子,不同的场景下会有不同的系统行为。在特定的场景下,有些操作甚至是不可以被kill掉的。比如从INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_INDEX表中读取数据时就不能执行kill命令。因为在这种时候执行kill,数据库会有严重的性能问题:query基本上停滞在锁状态而不会重新进入检查标志位的时机。

3. 线程长时间处在killed状态,这意味着什么?

这实际上有两种情况:当执行的是kill thread_id时,在SHOW PROCESSLIST的返回结果中对应线程会显示killed状态(因为默认是kill CONNECTION);而当执行kill QUERY thread_id时并不会杀掉整个数据库连接,而只是会结束这个连接下的一个正在执行的query,所以这种情况下SHOW PROCESSLIST命令返回的状态是query end。

当你看到下图所示结果时,这到底意味着什么?

show process list of mysql

这可能就是个bug,但是在大多数情况下这意味着数据库正在为结束某个任务而执行某些内部的扫尾工作。

当一个线程长时间处于killed状态或者query end状态时,最大的因为可能就是数据库正在等待事务回滚完成。当数以亿计的数据修改必须回滚时,这种等待往往要花费非常非常长的时间。

怎么进行验证?我们可以通过执行sql命令SHOW ENGINE INNODB STATUS。这个命令可以打印出是否正在执行某个回滚操作的具体信息,如下图所示:

mysql show engine innodb status

当一个线程被标记未killed或者query end状态,且InnoDB引擎的状态也报告出该线程正在执行一个回滚操作,那就说明了被kill掉的这个线程还没法立即结束,还得等到回滚操作执行完后才能真正被kill掉。

那如果INNODB STATUS并没有打印出一个回滚操作呢?

这很有可能是这个线程正在从物理磁盘上移除一些临时的表。对应ALTER操作来说,它不得不丢弃掉非常大的临时表,这种操作在ext3或ext4文件系统上可能会相当慢,所以这也不得不让kill命令延后一段时间才能正在被执行。

有什么方法能看到是否数据库正在建立或删除一个临时表?

如图所示,对于Percona Server或MariaDB,你可以通过查看INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLE和INFORMATION_SCHEMA.TEMPORARY_TABLE的内容获得一些信息,但是这两个命令只适合通过CREATE TEMPORARY命令手工建立临时表的情况。

mysql global temprorary table

图中的SESSION_ID跟SHOW PROCESSLIST返回的ID是相同的含义,都唯一标识了一个线程ID。所以你可以通过这两个值来关联同一个线程。

对于其他类型的临时表,有时候会打印在SHOW PROCESSLIST的输出结果中,

mysql show full processlist

有时候可以通过lsof命令来检查哪些临时文件正在被mysqld所打开,如下图所示。图中红色标出的是文件的大小,这也可以帮助你认定数据库正在移除临时表。但是上述方法都不是很容易正确追踪一个被kill的线程是否正在操作临时文件。

4. 结论

当在MySQL中执行kill命令时,被kill线程大多数情况下并不会立即结束。真正的操作并不会同kill命令同步完成。相反,MySQL一般会需要一段时间来执行结束线程前的扫尾工作(多数情况下就是执行回滚操作或删除临时表)。除非重启MySQL,不然基本上是不可能是避免这种线程悬挂(hanging thread)的问题。