MySQL で Sleep 状態のコネクションを一気に解放する方法

はじめに

max_connections = 25000 に設定してある状態で、Sleep 状態のコネクション（プロセス）が 25000 に張り付いたまま解放されないという非常に困る経験をしました。

kill コマンドで 1 個ずつコネクションを消していくことは可能ですが、大きいサービスでアクセスが集中している状況だと、次から次へとコネクションが貼られるので、全く解決に至りません。

そんな状況で、一気にコネクションを解放する方法を探して、実際にやってみて問題を解決することができたので、ここに書いておきます。

補足
「コネクション = プロセス」として話を進めます。

方法

Sequel Pro を用意する

ターミナルを使っても良いのですが、今回 Sequel Pro を使うととても楽に出来たので、Sequel Pro を使った場合の方法を書いています。（ターミナルを使う場合は以下の参考のリンクを参考にしてください）

プロセスの状況を確認する

プロセスの状況を確認し、解放してはいけないプロセスがないかを確認しておきます。

show processlist;

concat を使って複数の kill コマンドを作る

以下のコマンドを流すと複数の kill コマンドが出力されます。
limit と user は適宜変更してください。
本番で動いているサービスなら、limit は最初 100 とかにして、とにかく慎重にやった方が良いです。

select concat('KILL ',id,';') from information_schema.processlist where user='root' limit 5000;

出力された kill コマンドを実行する

出力された複数の kill コマンドを全選択コピーし、ペーストし、Sequel Pro の「すべてのクエリを実行」で実行すると、5000 件の kill コマンドが一気に流れてくれます。

KILL 23718469;
KILL 23721780;
KILL 23713641;
KILL 23747333;
KILL 23728007;
KILL 23719119;
KILL 23758587;
KILL 23764518;
KILL 23721484;
KILL 23760318;
KILL 23597168;
KILL 23740040;
.
.
.
KILL 23727601;

プロセスの状況を確認する

最後に再度プロセスの状況を確認して、減っていれば成功です。

show processlist;

参考

https://www.percona.com/blog/2009/05/21/mass-killing-of-mysql-connections/
http://libitte.hatenablog.jp/entry/20141202/1417452564