問(wèn)題描述

您是否曾經(jīng)試圖殺死一個(gè)查詢，但它并沒(méi)有消失，而是在運(yùn)行的查詢中停留了很長(zhǎng)一段時(shí)間？或者你注意到一些被標(biāo)記為被殺死的線程不時(shí)出現(xiàn)并且實(shí)際上并沒(méi)有死亡。這些僵尸線程是什么？為什么 MySQL 有時(shí)似乎無(wú)法快速終止查詢？本章對(duì)此問(wèn)題進(jìn)行剖析。

Threads and connections

MySQL 為每個(gè)客戶端連接使用單獨(dú)的線程。發(fā)送到 MySQL 的查詢由先前與查詢的連接關(guān)聯(lián)的線程處理。任何擁有足夠權(quán)限的人都可以通過(guò)運(yùn)行SHOW PROCESSLIST命令查看當(dāng)前活動(dòng)線程的列表以及一些其他詳細(xì)信息，該命令返回一個(gè)類似表的視圖，每一行都是個(gè)連接。具體如下：

任何正在運(yùn)行的查詢或列表中的任何現(xiàn)有連接都可以使用KILL命令進(jìn)行終止。

命令語(yǔ)法：

KILL [QUERY | CONNECTION] <thread_id>

其中thread_id是來(lái)自進(jìn)程列表輸出的第一列的值?？蛇x參數(shù)是只運(yùn)行的查詢還是終止整個(gè)會(huì)話。它默認(rèn)為后者，因此不需要指定CONNECTION 。

運(yùn)行該命令實(shí)際上并沒(méi)有做任何事情，除了在所選線程內(nèi)設(shè)置一個(gè)特殊標(biāo)志。因此 kill 操作不會(huì)與相應(yīng)的 request 同步發(fā)生。在許多情況下，線程或查詢需要一些時(shí)間才能停止。在語(yǔ)句執(zhí)行的各個(gè)階段檢查標(biāo)志。它何時(shí)準(zhǔn)確發(fā)生或發(fā)生頻率如何，取決于線程實(shí)際正在執(zhí)行的工作。

例如：

• 在ALTER TABLE期間，當(dāng) MySQL 將數(shù)據(jù)重寫(xiě)到新的臨時(shí)表中時(shí)，在從原始表中讀取每個(gè)行塊之前檢查它。在此過(guò)程中，臨時(shí)表被刪除，原始結(jié)構(gòu)保持不變。
• 對(duì)于UPDATE和DELETE，在每個(gè)更新或刪除的行之后還有一個(gè)額外的檢查。如果查詢注意到請(qǐng)求終止之前，進(jìn)行了任何更改，則必須回滾它們。注意：如果表的存儲(chǔ)引擎不支持事務(wù)（例如MyISAM），則更改無(wú)法回滾，因此該操作將導(dǎo)致部分更新！
• SELECT在讀取一行行后也會(huì)檢查它。

當(dāng)然，這些只是基本示例，不同情況下，可能會(huì)有所不同。在某些情況下，某些查詢甚至可能無(wú)法終止。從
INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_INDEX表中讀取時(shí)發(fā)生過(guò)一次這種情況。數(shù)據(jù)庫(kù)當(dāng)時(shí)存在性能問(wèn)題，查詢執(zhí)行基本上停止在 InnoDB 深處的某個(gè)鎖上，并且它再也沒(méi)有回到可以再次檢查標(biāo)志值的點(diǎn)上。

線程長(zhǎng)時(shí)間處于終止?fàn)顟B(tài)。這是什么意思？

實(shí)際上，可以有兩種不同的情況。如果發(fā)出KILL <thread_id>，則列表中的進(jìn)程將被殺死。KILL QUERY <thread_id>不會(huì)終止連接，而是只停止連接中正在運(yùn)行的查詢，因此在這種情況下，可能會(huì)出現(xiàn)查詢結(jié)束文本。

如果你看到這樣的東西怎么辦？

它可能是錯(cuò)誤（例如錯(cuò)誤#52528），或者更可能是數(shù)據(jù)庫(kù)在內(nèi)部執(zhí)行一些工作，以便任務(wù)終止后進(jìn)行清理。

當(dāng)然，沒(méi)有簡(jiǎn)單的方法來(lái)確認(rèn)這是否是一個(gè)錯(cuò)誤。因此，為了弄清楚它，您應(yīng)該尋找證明它不是錯(cuò)誤的證據(jù)。您所看到的只是標(biāo)準(zhǔn)操作的效果，MySQL 在查詢或事務(wù)之后必須執(zhí)行該操作以進(jìn)行清理。

一個(gè)線程在被殺死或查詢結(jié)束的情況下停留更長(zhǎng)時(shí)間的最常見(jiàn)原因可能是等待InnoDB 表上的事務(wù)回滾。這有時(shí)可能需要很長(zhǎng)時(shí)間才能完成，尤其是當(dāng)必須刪除數(shù)十萬(wàn)或數(shù)百萬(wàn)的更改時(shí)。

如何驗(yàn)證？

檢查是SHOW ENGINE INNODB STATUS\G的輸出。如果當(dāng)前正在進(jìn)行回滾，會(huì)有如下簡(jiǎn)單的信息：

---TRANSACTION 0 10411, ACTIVE 28 sec, process no 15506, OS thread id 140732309711184 rollback 
mysql tables in use 1, locked 1 ROLLING BACK 7585 lock struct(s), heap size 751600, undo log entries 798854
 MySQL thread id 14 , query id 206 localhost root end 
update testdb.sometable set cc=sqrt(id)

該示例顯示了如果一個(gè)線程被標(biāo)記為kill或query end，并且 InnoDB 引擎狀態(tài)報(bào)告同一線程的回滾，請(qǐng)等待它結(jié)束。

如果不是這樣呢？

它可能與從磁盤中刪除一些臨時(shí)表有關(guān)。一個(gè)ALTER TABLE可能需要丟棄一個(gè)非常大的臨時(shí)表，而在某些文件系統(tǒng)（例如ext3或ext4）上刪除大文件可能相當(dāng)慢，因此可能需要幾秒鐘甚至更長(zhǎng)的時(shí)間。任何 DML 語(yǔ)句也可以創(chuàng)建臨時(shí)表，但通常不會(huì)那么大。

在最極端的情況下，刪除文件也不會(huì)超過(guò)十或二十秒，但在 I/O 負(fù)載較重的情況下，它可能會(huì)更長(zhǎng)，所以有什么方法可以查看是否有臨時(shí)表是否被創(chuàng)造？在Percona Server或MariaDB中，您檢查INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLE和INFORMATION_SCHEMA.TEMPORARY_TABLE的內(nèi)容。它僅適用于使用CREATE TEMPORARY TABLE語(yǔ)句手動(dòng)建立的臨時(shí)表，不適用于 MySQL 隱式創(chuàng)建的執(zhí)行復(fù)雜查詢時(shí)的臨時(shí)表。

SESSION_ID與進(jìn)程列表中的線程標(biāo)識(shí)符相同，因此您可以殺死任何連接到臨時(shí)表的線程。

其他類型的臨時(shí)表不容易追蹤。有時(shí)可以在SHOW PROCESSLIST輸出中發(fā)現(xiàn)有用信息：

有時(shí)您可以檢查實(shí)例打開(kāi)的臨時(shí)文件：

紅色標(biāo)記的值是文件的大小。這些可以幫助您定位正在刪除的臨時(shí)表。

結(jié)論

有時(shí)看到一個(gè)數(shù)據(jù)庫(kù)線程被殺死，但線程并沒(méi)有立即被終止，或者一個(gè)查詢似乎無(wú)法結(jié)束，這可能是正常的。因?yàn)閷?shí)際操作不會(huì)與 kill 命令同步執(zhí)行，之后 MySQL可能仍需要一些時(shí)間才能正確清理。不管真正的原因是什么，如果不重新啟動(dòng)數(shù)據(jù)庫(kù)，就不可能擺脫這種掛起的線程。也沒(méi)有辦法像Unix 系統(tǒng)中的kill -9 <pid>那樣立即強(qiáng)制執(zhí)行kill。