Tocyukiのブログ

ギターと柔術とプログラミングが好き!

MySQLで時間のかかっているクエリを強制終了させる

DB触ってない勢なのでDB周りの運用を覚えていかないとやばい〜、というわけで最近ちょくちょく発生しているMySQLで時間のかかっているSELECT文を強制終了させるというオペレーションの備忘録として残しておきます。

この対応が最高にイケていないというのは重々承知の上ですが、ちょっと特殊な環境のサーバー&アプリで移行も控えているということで人力で頑張っている感じです!

MySQLでクエリの一覧を表示させる

まずは現在実行されているクエリの一覧をSHOW PROCESSLISTコマンドで表示させる

mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+------------+--------------------+
| Id | User | Host      | db   | Command | Time | State      | Info               |
+----+------+-----------+------+---------+------+------------+--------------------+
| 88 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 89 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 90 | root | localhost | NULL | Query   |   96 | User sleep | select sleep (100) |
| 91 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 92 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 93 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 94 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 95 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 96 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 97 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 98 | root | localhost | NULL | Query   |    0 | starting   | SHOW PROCESSLIST   |
+----+------+-----------+------+---------+------+------------+--------------------+
11 rows in set (0.00 sec)

対象のクエリを強制終了させる

KILL CONNECTION <processlist id>コマンドで対象のクエリを強制終了させる

mysql> KILL CONNECTION 90;
Query OK, 0 rows affected (0.00 sec)

再度クエリの一覧を確認する

id 90のクエリが消えているのが確認できました!

mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+------------+--------------------+
| Id | User | Host      | db   | Command | Time | State      | Info               |
+----+------+-----------+------+---------+------+------------+--------------------+
| 88 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 89 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 91 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 92 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 93 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 94 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 95 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 96 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 97 | root | localhost | NULL | Query   |    6 | User sleep | select sleep (100) |
| 98 | root | localhost | NULL | Query   |    0 | starting   | SHOW PROCESSLIST   |
+----+------+-----------+------+---------+------+------------+--------------------+
10 rows in set (0.00 sec)

複数のクエリを一発でKILLしたい場合

以下のクエリでプロセスIDを抽出可能(60秒以上かかっている処理を抜き出したい場合)

mysql> SELECT GROUP_CONCAT(ID) FROM information_schema.PROCESSLIST WHERE TIME > 59;

GROUP_CONTACT関数を使うことで複数レコードをカンマ区切りで1行にまとめられる

KILL QUERYKILL CONNECTIONの違い

MySQLでクエリを強制終了させる場合、KILL QUERYKILL CONNECTIONのコマンドがるのですが、基本的にはKILL CONNECTIONコマンドを実行するのが今回の運用では適切っぽいですね。

その理由としてはKILL QUERYコマンドではステートメントは終了されるが、トランザクションは終了されないので、接続自体が残ってしまうからのようです。

そんなわけで

DBに強くなりたいと心から願っている今日このごろです。。。

参考情報

dev.mysql.com