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)
通常、Info 列に表示されるクエリは１００文字を越えると省略されますが、FULL オプションを指定すると、省略せずクエリの全文が表示される
その際、表示が崩れるようであれば\Gオプションでクエリ結果を垂直表示すると見やすくなる
mysql> SHOW FULL PROCESSLIST \G;
対象のクエリを強制終了させる
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_concat(ID) | +------------------+ | 1,2,3,4,5 | +------------------+
GROUP_CONTACT関数を使うことで複数レコードをカンマ区切りで1行にまとめられるが、MySQL内のkillコマンドでは複数IDを受け付けてくれてくれないため、
一旦MySQLから抜けてmysqladminコマンドで実行する必要がある
mysqladmin kill 1,2,3,4,5 -h localhost -u foo
KILL QUERYとKILL CONNECTIONの違い
MySQLでクエリを強制終了させる場合、KILL QUERYとKILL CONNECTIONのコマンドがるのですが、基本的にはKILL CONNECTIONコマンドを実行するのが今回の運用では適切っぽいですね。
その理由としてはKILL QUERYコマンドではステートメントは終了されるが、トランザクションは終了されないので、接続自体が残ってしまうからのようです。
そんなわけで
DBに強くなりたいと心から願っている今日このごろです。。。
