MySQL のスロークエリをメールで受け取る
Posted on
MySQL のパフォーマンスを維持するにはスロークエリのチェックが欠かせませんが、毎日チェックするとなると面倒です。対象のサーバが増えてくるとなおさら面倒になるので、スロークエリをメールで送るようにしてみました。
MySQL 5.5 系を使っていますが、5.1 系でもおそらく同じ手順でいけると思います。
スロークエリを設定する
まずスロークエリを記録するように設定します。
mysql> SET GLOBAL slow_query_log = 1;
mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/slow_query.log";
mysql> SET GLOBAL long_query_time = 1;
my.cnf に書いて MySQL を再起動するか、無停止で設定したい場合は SET GLOBAL でシステム変数を変更します。 SET GLOBAL で変更する場合は root 権限が必要です。
long_query_time でスロークエリの秒数を指定します。デフォルトは 10 秒で、0 秒にするとすべてのクエリが記録されます。 slow_query_log_file で指定するログファイルは mysql ユーザーが書き込める権限を与えます。
SET GLOBAL で設定した場合は次のセッションから有効になるので、ログインしなおして有効になったかどうか確認します。
mysql> SHOW VARIABLES LIKE 'slow%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/slow_query.log |
+---------------------+-------------------------------+
mysql> SHOW VARIABLES LIKE 'long%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
mysqldumpslow でスロークエリを集計する
mysqldumpslow は MySQL に付属しているスクリプトで、スロークエリログを見やすく集計してくれます。 s オプションでソートタイプを指定できますが、今回はデフォルトの at (平均実行時間の長い順) でソートします。
一連の流れをシェルスクリプトで書きます。 MAIL_TO, HOST_NAME, LOG_FILE はそれぞれの環境に合わせて読み替えてください。
#!/bin/bash
MAIL_TO="hoge@example.com"
HOST_NAME="example.com"
LOG_FILE="/var/log/mysql/slow_query.log"
/usr/bin/mysqldumpslow $LOG_FILE | mail -s "mysqldumpslow for "$HOST_NAME $MAIL_TO
mv $LOG_FILE $LOG_FILE.`date +%Y%m%d`
/usr/bin/mysqladmin flush-logs
mysqladmin flush-logs は新しいログファイルに強制的に変更するコマンドですが、MySQL の root ユーザーでないと実行できません。パスワードを設定している場合は引数で渡す必要がありますが、シェルスクリプト内に書くのはあまりよろしくないので、my.cnf の mysqladmin セクションにユーザー名とパスワードを追記しています。
[mysqladmin]
user = user
password = password
あとは /usr/local/bin/slow-query などに置いて cron で定期実行させます。
$ sudo crontab -u mysql -l
0 4 * * * /usr/local/bin/slow-query 2>/dev/null
これで毎朝 4 時にスロークエリの集計結果がメールで送られてきます。
追記
汎用的に使えるシェルスクリプトを GitHub に公開しました。