[PR] あなたが Kindle で読みたいその本、Kindle に対応したら Twitter でお知らせします。

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 に公開しました。