SHOW FULL PROCESSLIST を使った MySQL のプロファイリング
KLab Advent Calendar 2011 「DSAS for Social を支える技術」の3日目は、 引き続き MySQL 周りのチューニングノウハウとして、すぐに役立つ プロファイリング方法を紹介します。
DBサーバーの負荷が高い時、 slow_log を見て問題になっている重いクエリを 発見するのが一般的かもしれませんが、 slow_log に一切ログが残らないのに 負荷が高い状況や、むしろ負荷が高すぎてごく一般的でどう考えても遅いはずが ないクエリ ("SET NAMES utf8" や "BEGIN") すら slow_log に大量に乗ってしまう場合が あります。
slow_log 以外で問題になっているクエリを見つける方法として、 "SHOW FULL PROCESSLIST" コマンドがあります。これを数回〜数十回叩いてみて、 よく出ているクエリは、遅かったり量が多かったりする「チューニング候補」に なります。ですが、この方法も手動でやるのは一苦労です。接続数が数百あって コマンドの出力結果が画面に収まらないものを数回叩いて多そうなクエリを探す なんて、人間の仕事ではありません。
そこで、 SHOW FULL PROCESSLIST を秒間1回叩いて、似ているクエリをまとめて 累計でカウントし、カウントが多い順にソートして表示してくれるコマンドを作りました。
動作は、コードを見たまんまです。 SQLを真面目に解析するのではなく、単によくある構文で変数が入る部分を カットして同じ形のクエリを探しているだけなので、O/Rマッパーや アプリによっては クエリをカットする部分をカスタマイズする必要が あるかもしれません。
注意点として、 "SHOW FULL PROCESSLIST" は、 OPERATOR 権限が無いと 自分と同じユーザーのクエリしか表示しません。なので、 OPERATOR 権限がある ユーザーで実行するか、WebサーバーがDBに接続する時と同じユーザーを利用する 必要があります。
50行も無いシンプルなスクリプトですが、S-inしたイベントの負荷が 想像以上でこのままじゃ終電や週末がヤバイ！という状況を、数分で チューニングポイントを見つけて何度も救ってくれました。 もっと正確なツールがあるかもしれませんが、このツールは DBサーバーや Webサーバーにsshできなかったり、DBのオペレーター権限が無いような アプリエンジニアでもカジュアルに使えるのが利点です。
トラブルシューティングでは、状況を把握するための多面的なデータを取得する必要があります。 どれだけ頭が良くても、多面的なデータが無いなら、推測ではなく憶測しかできません。 データはこういった簡単なアイデアとツールで補強できるので、ぜひご活用ください。
