MySQLのスロークエリログをmysqldumpslowで解析してファイルに書き出し、ログをローテーションする

MySQLのチューニングに当たってスロークエリログを書き出すことは良くあります。設定によっては放っておくと膨大なログになってしまうため、適度なタイミング（日毎とか週毎とか）でログローテーションしてやります。logrotateを使うのが常套手段。

スロークエリログの解析にはmysqldumpslowを使用します。mysqldumpslowを使用するとログを解析した上で、オプションに従って並べ替えて出力してくれます。ログ全体のまとめになるので、定期的これを実行すれば、どれくらいの頻度でスロークエリが発生しているかを定量的に計ることが出来ます。

…というわけで、この2つを組み合わせてみました。

/etc/logrotate.d/mysql

/var/log/mysql-slow.log {
# create 600 mysql mysql
notifempty
daily
rotate 5
missingok
prerotate
EXT=`/bin/date +%Y%m%d`
/usr/bin/mysqldumpslow -s t /var/log/mysql-slow.log > /var/log/mysql/mysql-slow.${EXT}.log
endscript
postrotate
# just if mysqld is really running
if test -x /usr/bin/mysqladmin && \
/usr/bin/mysqladmin ping &>/dev/null
then
/usr/bin/mysqladmin flush-logs
fi
endscript
}

ポイント1：postrotateを使って古いログを削除

ローテーション後にこの処理を行わないとログローテーションが完了しないっぽいので。

MySQL5.1のlog出力とlogrotate設定 | ijo.cc

ポイント2：prerotateを使ってmysqldumpslow→日付入りでファイル出力

ローテーション前にやらないと解析対象のログがない事態になるのでprerotateを使う。

変数に現在日時を保存する
クエリ実行に掛かった総時間で並べ替え
日付入りのファイルを作成して書き出し

slowlogを毎日メールで送る – スコトプリゴニエフスク通信

その他参考

logrotateのテスト

# logrotate -dv /etc/logrotate.d/mysql

mysqldumpslowのオプション

# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

--verbose    verbose
--debug      debug
--help       write this text to standard output

-v           verbose
-d           debug
-s ORDER     what to sort by (t, at, l, al, r, ar etc), 'at' is default
-r           reverse the sort order (largest last instead of first)
-t NUM       just show the top n queries
-a           don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   grep: only consider stmts that include this string
-h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           don't subtract lock time from total time

並べ替えるには「-s」オプションを付けた上でソート順を指定。