みなさんは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構文を流してみます。
processlist_idの88番と99番が強制終了されていることが確認できました。
InnoDBを利用している場合は、COMMITする前にKILL構文を実施すると、そのクエリに対してROLLBACKが実施しされます。
複数のクエリをまとめて強制終了する場合は、KILL <process_id>
のprocesslist_idをカンマ区切りで記述すれば可能になります。
mysqladminコマンドを使ってクエリを強制終了する
mysqladminコマンドを使ってKILL構文を実施することも可能です。その場合はmysqladimin processlist
で現在の一覧を取得した後、mysqladmin kill <processlist_id>
で実施します。これはKILL CONNECTION
と同じ事になります。
pt-killを使ってみる
PERCONA社が公開しているPercona Toolkitの中に、pt-killというものがあります。
こちらは特定の条件をもとにクエリを強制終了したい時などに便利な多彩なオプションが用意されています。こちらを利用すれば、1分以上かかっているクエリを全て強制終了したり、同じクエリの中で最も古いクエリのみを残してクエリを強制終了するといったことが可能になります。
ここでいくつかオプションを紹介します。
--print/--kill/--kill-query
--kill
の場合はKILL <processlist_id>
を実行します。--kill-query
とすることでKILL QUERY構文に変更することができます。また--print
とすると、実際にクエリの強制終了は実行せずに、対象のクエリのみを抽出することができます。
--busy-time
整数値を超えたクエリを強制終了します。下記例では、クエリタイムが2秒以上のクエリを強制終了します。
--match-command
SHOW PROCECSSLIST
を実施した際の、Commandカラムの種類(QueryやSleepなど)を指定します。
--victims
oldest、all、all-but-oldestの3つがあります。oldestは条件の中から最も古いクエリを強制終了します。allは条件に合う全てのクエリ、all-but-oldestは条件に合う最も古いクエリ以外を強制終了します。
--match-info
正規表現に合った全てのクエリを強制終了します。たとえば、全てのSELECT文を強制終了したい場合は下記のように記述します。
--interval
killを実行する間隔を指定します。デフォルトは30秒になります。
--group-by
SHOW PROCESSLIST
の結果をgroup-byします。group-byされた結果をもとに、--query-count
などの条件をつけてクエリを絞り込むことができます。
クエリ強制終了する時に気をつけたいこと
クエリを強制終了する際に、対象のクエリがInnoDBのテーブルに対して実行されている場合はロールバックされますが、MyISAMである場合は注意が必要です。更新途中のクエリが強制終了されると、レプリケーションを組んでいる場合はマスターとスレーブ間でデータの整合性が取れなくなってしまいます。
まとめ
今回は、クエリを強制終了する方法としてKILL構文とpt-killというツールを紹介しました。
クエリの強制終了は極力実施したくはないですが、もしデッドロックなどでロック開放待ち状態になったり、クエリがハングした時には実施する必要があるのでいざという時に備えて使えるようになっておきましょう。