第61回　クエリを強制終了する

みなさんはMySQLを運用していくうえで、クエリが詰まったり、長期実行されているクエリを強制終了した経験はありませんか？今回はクエリが詰まった際にどのように対処するのかクエリを強制終了するやり方を紹介したいと思います。

KILL構文

MySQLでは、KILL構文を使って現在実行しているクエリを強制終了させることができます。

KILL構文は、KILL CONNECTION <processlist_id>、またはKILL QUERY <processlist_id>で実施することができます。この両者の違いは

KILL CONNECTIONは接続を終了する
KILL QUERYは実行中のステートメントは終了するが接続自体は残る

となっています。

KILL QUERYではステートメントは終了されますがトランザクションは終了しません。そこで原則KILL CONNECTIONを利用するようにしましょう。

KILL <processlist_id>と入力した場合はKILL CONNECTIONと同じ構文となります。processlist idはSHOW PROCESSLISTを実行するか、INFORMATION_SCHEMAのPROCESSLISTテーブルで確認することができます。例としてSELECT SLEEP(100)を10件流した状態でSHOW PROCESSLISTを実行してKILL構文を流してみます。

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   |    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   |
+----+------+-----------+------+---------+------+------------+--------------------+
11 rows in set (0.00 sec)

mysql> KILL QUERY 88;
sleep (100)
1
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
ERROR 2006 (HY000) at line 1: MySQL server has gone away
ERROR 2006 (HY000) at line 1: MySQL server has gone away

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

processlist_idの88番と99番が強制終了されていることが確認できました。

InnoDBを利用している場合は、COMMITする前にKILL構文を実施すると、そのクエリに対してROLLBACKが実施しされます。

複数のクエリをまとめて強制終了する場合は、KILL <process_id>のprocesslist_idをカンマ区切りで記述すれば可能になります。

mysqladminコマンドを使ってクエリを強制終了する

mysqladminコマンドを使ってKILL構文を実施することも可能です。その場合はmysqladimin processlistで現在の一覧を取得した後、mysqladmin kill <processlist_id>で実施します。これはKILL CONNECTIONと同じ事になります。

$ /usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf -u root -p processlist
Enter password:
+-----+------+-----------+----+---------+------+------------+--------------------+
| Id  | User | Host      | db | Command | Time | State      | Info               |
+-----+------+-----------+----+---------+------+------------+--------------------+
| 135 | root | localhost |    | Query   | 9    | User sleep | select sleep (100) |
| 136 | root | localhost |    | Query   | 9    | User sleep | select sleep (100) |
| 137 | root | localhost |    | Query   | 9    | User sleep | select sleep (100) |
| 138 | root | localhost |    | Query   | 0    | starting   | show processlist   |
+-----+------+-----------+----+---------+------+------------+--------------------+
$ /usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf --defaults-file=/etc/my.cnf -u root -p kill 136
Enter password:

pt-killを使ってみる

PERCONA社が公開しているPercona Toolkitの中に、pt-killというものがあります。

こちらは特定の条件をもとにクエリを強制終了したい時などに便利な多彩なオプションが用意されています。こちらを利用すれば、1分以上かかっているクエリを全て強制終了したり、同じクエリの中で最も古いクエリのみを残してクエリを強制終了するといったことが可能になります。

ここでいくつかオプションを紹介します。

--print/--kill/--kill-query

--killの場合はKILL <processlist_id>を実行します。--kill-queryとすることでKILL QUERY構文に変更することができます。また--printとすると、実際にクエリの強制終了は実行せずに、対象のクエリのみを抽出することができます。

--busy-time

整数値を超えたクエリを強制終了します。下記例では、クエリタイムが2秒以上のクエリを強制終了します。

pt-kill --defaults-file=/etc/my.cnf -u root -p '*****' --kill --busy-time 2 --victims=all

--match-command

SHOW PROCECSSLISTを実施した際の、Commandカラムの種類（QueryやSleepなど）を指定します。

--victims

oldest、all、all-but-oldestの3つがあります。oldestは条件の中から最も古いクエリを強制終了します。allは条件に合う全てのクエリ、all-but-oldestは条件に合う最も古いクエリ以外を強制終了します。

--match-info

正規表現に合った全てのクエリを強制終了します。たとえば、全てのSELECT文を強制終了したい場合は下記のように記述します。

pt-kill --defaults-file=/etc/my.cnf -u root -p '****' --kill --victims all --match-info "^(select|SELECT)"

--interval

killを実行する間隔を指定します。デフォルトは30秒になります。

--group-by

SHOW PROCESSLISTの結果をgroup-byします。group-byされた結果をもとに、--query-countなどの条件をつけてクエリを絞り込むことができます。

$ pt-kill --defaults-file=/etc/my.cnf -u root -p '****' --print --interval=2 --group-by=Info --busy-time=3 --victims=all --query-count 3

# 2017-12-19T11:37:16 KILL 543 (Query 3 sec) select sleep (100)
# 2017-12-19T11:37:16 KILL 542 (Query 3 sec) select sleep (100)
# 2017-12-19T11:37:16 KILL 541 (Query 3 sec) select sleep (100)
# 2017-12-19T11:37:16 KILL 540 (Query 3 sec) select sleep (100)
# 2017-12-19T11:37:16 KILL 539 (Query 3 sec) select sleep (100)
# 2017-12-19T11:37:16 KILL 538 (Query 3 sec) select sleep (100)
# 2017-12-19T11:37:16 KILL 537 (Query 3 sec) select sleep (100)
# 2017-12-19T11:37:16 KILL 536 (Query 3 sec) select sleep (100)

クエリ強制終了する時に気をつけたいこと

クエリを強制終了する際に、対象のクエリがInnoDBのテーブルに対して実行されている場合はロールバックされますが、MyISAMである場合は注意が必要です。更新途中のクエリが強制終了されると、レプリケーションを組んでいる場合はマスターとスレーブ間でデータの整合性が取れなくなってしまいます。

まとめ

今回は、クエリを強制終了する方法としてKILL構文とpt-killというツールを紹介しました。

クエリの強制終了は極力実施したくはないですが、もしデッドロックなどでロック開放待ち状態になったり、クエリがハングした時には実施する必要があるのでいざという時に備えて使えるようになっておきましょう。