MySQLのkillがスレッドをkillできない理由

背景

日常の使用において、MySQL で個別または大量の接続が蓄積されることが時々あります。このような場合、通常は、kill コマンドを使用してこれらの蓄積された接続を強制的に強制終了し、接続数とデータベースサーバーの CPU リソースをできるだけ早く解放することを検討します。

問題の説明

実際に kill コマンドを使用すると、接続はすぐには終了せず、プロセスリストにはまだ表示されますが、表示されるコマンドは一般的なクエリや実行ではなく、Killed になります。例えば：

mysql> プロセスリストを表示します。
+----+------+--------------------+---------+--------+------+--------------+--------------------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+----+------+--------------------+---------+--------+------+--------------+--------------------------------+
| 31 | root | 192.168.1.10:50410 | sbtest | クエリ | 0 | 開始 | プロセスリストを表示 |
| 32 | root | 192.168.1.10:50412 | sbtest | クエリ | 62 | ユーザー sleep | select sleep(3600) from sbtest1 |
| 35 | root | 192.168.1.10:51252 | sbtest | 強制終了 | 47 | データ送信中 | select sleep(100) from sbtest1 |
| 36 | root | 192.168.1.10:51304 | sbtest | クエリ | 20 | データを送信中 | select sleep(3600) from sbtest1 |
+----+------+--------------------+---------+--------+------+--------------+--------------------------------+

原因分析

疑問がある場合は、まず公式ドキュメントを参照してください。以下は公式ドキュメントからの抜粋です。

KILL を使用すると、スレッド固有の kill フラグがスレッドに設定されます。ほとんどの場合、kill フラグは特定の間隔でのみチェックされるため、スレッドが終了するまでに多少時間がかかることがあります。SELECT 操作中、ORDER BY および GROUP BY ループでは、行のブロックを読み取った後にフラグがチェックされます。kill フラグが設定されている場合、ステートメントは中止されます。
テーブルのコピーを作成する ALTER TABLE 操作では、元のテーブルからコピーされた行がいくつか読み取られるたびに、定期的に kill フラグがチェックされます。kill フラグが設定されている場合、ステートメントは中止され、一時テーブルは削除されます。
KILL ステートメントは確認を待たずに戻りますが、kill フラグチェックによって、かなり短い時間内に操作が中止されます。必要なクリーンアップを実行するために操作を中止するのにも、ある程度の時間がかかります。
UPDATE または DELETE 操作中、各ブロックの読み取り後、および各行の更新または削除後に、kill フラグがチェックされます。kill フラグが設定されている場合、ステートメントは中止されます。トランザクションを使用していない場合、変更はロールバックされません。
GET_LOCK() は中止され、NULL を返します。
スレッドがテーブルロックハンドラー (状態: ロック) 内にある場合、テーブルロックはすぐに中止されます。
スレッドが書き込み呼び出しで空きディスク領域を待機している場合、書き込みは「ディスクがいっぱいです」というエラーメッセージとともに中止されます。

公式ドキュメントの最初の段落には、kill のメカニズムが明確に記載されています。接続されたスレッドに対してスレッドレベルの kill マークが設定され、次の「マーク検出」まで有効になりません。これは、次の「マーク検出」が間に合わなかった場合、問題で説明した現象が発生する可能性があることも意味します。

公式ドキュメントには、いくつかのシナリオがリストされています。公式の説明に基づいた、よくある問題のシナリオをいくつか示します。

SELECT ステートメントで order by および group by を実行するときに、サーバーの CPU リソースが不足していると、データのバッチの読み取り/取得にかかる時間が長くなり、次の「マーク検出」の時間に影響します。
大量のデータに対して DML 操作を実行する場合、kill などの SQL ステートメントはトランザクションのロールバックをトリガーします (InnoDB エンジン)。ステートメントは強制終了されますが、ロールバック操作には非常に長い時間がかかります。
キル変更操作中にサーバーの負荷が高い場合、データのバッチ操作にかかる時間が長くなり、次の「マーク検出」の時間に影響します。
実際、kill のメカニズムを参照して帰納的に説明すると、SQL ステートメントの通常の実行をブロック/遅くする動作があると、次の「マーク検出」が延期されるか実行できなくなり、最終的に kill 操作が失敗することになります。

シミュレーションする

ここでは、パラメータ innodb_thread_concurrency を使用して、SQL ステートメントの通常の実行をブロックするシナリオをシミュレートします。

InnoDB 内で許可されるスレッドの最大数を定義します。値 0 (デフォルト) は、無制限の同時実行 (制限なし) として解釈されます。この変数は、高同時実行システムでのパフォーマンスチューニングを目的としています。

公式ドキュメントによると、このパラメータが低い値に設定されている場合、制限を超える InnoDB クエリはブロックされます。したがって、このシミュレーションでは、このパラメータは非常に低い値に設定されました。

mysql> '%innodb_thread_concurrency%' のような変数を表示します。
+---------------------------+-------+
| 変数名 | 値 |
+---------------------------+-------+
| innodb_thread_concurrency | 1 |
+---------------------------+-------+
セット内の 1 行 (0.00 秒)

次に、2 つのデータベース接続 (セッション 1 とセッション 2) を開き、それぞれでselect sleep(3600) from sbtest.sbtest1ステートメントを実行してから、3 番目の接続でセッション 2 のクエリを強制終了します。

セッション 1:
mysql> sbtest.sbtest1 から sleep(3600) を選択します。

セッション2:
mysql> sbtest.sbtest1 から sleep(3600) を選択します。
エラー 2013 (HY000): クエリ中に MySQL サーバーへの接続が失われました
マイSQL>

セッション3:
mysql> プロセスリストを表示します。
+----+------+--------------------+-------+--------+------+--------------+-----------------------------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+----+------+--------------------+-------+--------+------+--------------+-----------------------------------------+
| 44 | root | 172.16.64.10:39290 | NULL | クエリ | 17 | ユーザー sleep | sbtest.sbtest1 から sleep(3600) を選択 |
| 45 | ルート | 172.16.64.10:39292 | NULL | クエリ | 0 | 開始 | プロセスリストを表示 |
| 46 | root | 172.16.64.10:39294 | NULL | クエリ | 5 | データを送信中 | select sleep(3600) from sbtest.sbtest1 |
+----+------+--------------------+-------+--------+------+--------------+-----------------------------------------+
セット内の 3 行 (0.00 秒)

mysql> 46 を強制終了します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> プロセスリストを表示します。
+----+------+--------------------+-------+--------+------+--------------+-----------------------------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+----+------+--------------------+-------+--------+------+--------------+-----------------------------------------+
| 44 | root | 172.16.64.10:39290 | NULL | クエリ | 26 | ユーザー sleep | sbtest.sbtest1 から sleep(3600) を選択 |
| 45 | ルート | 172.16.64.10:39292 | NULL | クエリ | 0 | 開始 | プロセスリストを表示 |
| 46 | root | 172.16.64.10:39294 | NULL | 強制終了 | 14 | データを送信中 | sbtest.sbtest1 から sleep(3600) を選択 |
+----+------+--------------------+-------+--------+------+--------------+-----------------------------------------+
セット内の 3 行 (0.00 秒)

マイSQL>

ご覧のとおり、kill コマンドが実行されると、セッション 2 の接続はすぐに切断されますが、セッション 2 によって開始されたクエリは MySQL に残っています。もちろん、 innodb_thread_concurrencyパラメータによって同様の問題が発生している場合は、 set globalコマンドを使用して上限値を直接増やすか、直接 0 に設定して解決できます。このパラメータの変更は、すべての接続に対してリアルタイムで有効になります。

総括する

MySQL の kill 操作は、想像どおりにデータベース接続を直接強制的に終了するわけではありません。終了信号を送信するだけです。SQL 自体の実行効率が遅すぎる場合、または他の要因 (サーバーの負荷が高い、大量のデータロールバックがトリガーされる) の影響を受けている場合は、この kill 操作では、問題のあるクエリを時間内に終了できない可能性があります。それどころか、プログラム側の接続が切断された後に再接続がトリガーされ、クエリの効率がさらに低下し、データベースのパフォーマンスがさらに低下する可能性があります。

上記は、MySQL kill がスレッドを強制終了できない理由の詳細です。MySQL kill スレッドの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: