MySQLのゾンビプロセスの話

エビデンス的なものを貼り付けてもイマイチ伝わらないので、文字通り雑記レベルで。

複数セッション用意してタイミングに依存した実験の結果を伝える時、難しいよね、ってことで、ダラっとした解説になってしまいます。

MySQLのサーバーに重いクエリを投げつけたりしたとき、
クライアント自身を強制終了（mysqlクライアントを「kill -9」とか）すると、サーバー側では処理が続きます。

「SHOW FULL PROCESSLIST」を見るとわかります。

再接続してクエリを投げると、別セッションなのでSHOW FULL PROCESSLIST上、もう１つ増えたところで実行されているのが見えます。

再接続して再実行すると、PROCESSLISTに表示される件数は増える。。。

ゾンビ化したクエリは、要求された処理が終わるまで走り続けて、終わっても返す相手（クライアント）がいないので、そのまま消えていきます。まさに無駄骨。

mysqlクライアントごとkillではなく「キャンセル」にすると、しばらく残るケースはありますが、そのうち消えます。
Ctrl+Cでキャンセルを投げると、SHOW FULL PROCESSLISTにはunauthenticated userなプロセスが一時的に増えて見えることがあり、
クエリキャンセルを投げたしたクライアントに
ERROR 1317 (70100): Query execution was interrupted
が戻ってきた後、Sleepになる。unauthenticated userだったプロセスは消えます。

再実行すると、PROCESSLISTに表示される件数は増えません。あくまで同じクライアントプロセスを再利用するからです。

リトライ実装に気をつけて

言っておきたいのはコッチ。DB屋側ではどうにもならず、アプリ側の実装、設定に依存する話。

主にユーザーエクスペリエンスのことを考えて、タイムアウトを設定すると思います。
クエリの実行時間が長かったりすると、ネットワーク的な異常などを疑って内部でリトライ走らせたり、クライアントまで返して「もっと条件絞って！」と促したり。
DBサーバーからは返事が来ていないので、クライアント側で実行時間をTimerで図って、タイムアウト判定することになります。

しかし、上記の通り、そのリトライにあたって「ぶった切る」ようなタイムアウト実装をすると、前述の通り処理が残るのでDB側には負荷かけっぱなしの状態になります。
何度もリトライを繰り返すと、いわゆる「ゾンビ」だらけ、しかもみんな「クエリ実行中」という、とんでもなく重い状態になります。

認識している接続数（アプリケーション・サーバーに設定しているDB接続数上限の設定など）と、SHOW FULL PROCESSLISTから見える接続数／実行数に大きな乖離が出ていたら、このパターンを疑って良いと思います。
DBサーバーのロードアベレージが、接続数よりも異常に高い数字を示している、なんてケースでも、これに陥っている可能性があります。

判別がつくのであれば、ゾンビ化しているユーザーと同じユーザー、または、別のユーザーであれば「PROCESS」か「SUPER」権限のついてるユーザーMySQLにログインして、MySQLのkillコマンド（AWSならmysql.rds_kill）でゾンビを潰し込んでいけば良いです。
しかし、現実的には、識別できない、増え続けて追いつかない、そこまで手を回せないケースも多いと思います。
なので、できる限りこうしたゾンビを生み出さないような設定、実装をしましょう。

また、この件に限らず、原因の切り分けがしやすいように、DBユーザーを同じ権限しか使わなくても目的別で細かめに分けておくと良いと思います。
クライアントのIPアドレスはSHOW FULL PROCESSLISTでわかりますが、同じサーバーから別目的で色々な目的の処理を投げている場合、判別の手がかりがSQLそのものしかなくなってしまうので、「このSQL投げてるやつ誰だよー」からになってしまいますね。

JDBCであれば、setQueryTimeoutを、MySQL5.7以降は、「MAX_EXECUTION_TIME」パラメータもあるので、これらを上手く使って下さい。昔書いた記事を貼っておきます。
atsuizo.hatenadiary.jp

こっち処理はタイムアウト短くしたいけど、あっち処理は短いと困る、とかあるので、一律で設定するの難しいし、処理に合わせて柔軟に変えていく実装を入れるのもそれはそれで結構大変だよね。。。