MySQLのスロークエリログ機能を使うと、指定した秒数以上要したクエリが記録されるので、チューニングに役立ちます。
MySQL 5.1からは、秒数指定で1秒未満の値を設定できるようになりました。

my.cnfに以下の設定を追加します。

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 0.5  // 0.5秒以上のクエリを記録する場合

また、オンラインで（MySQLサーバーを再起動せずに）設定を変更することもできます。

sql> set GLOBAL slow_query_log = 1;
sql> set GLOBAL slow_query_log_fileg = '/var/lib/mysql/slow.log';
sql> set GLOBAL long_query_time = 0.5;

さらに、MySQL 5.1では、スロークエリログ（と一般クエリログ）をファイルではなくDBテーブルに記録できるようになりました。

my.cnfに以下の設定を追加します。

log_output = TABLE

こちらも、オンラインで設定変更が可能です。

sql> set GLOBAL log_output='TABLE';

これで、スロークエリログがmysql.slow_log というDBテーブルに記録されるようになります。
スロークエリログをDBテーブルに記録する利点としては、
「遅いクエリをSQL文で検索・集計できる」
ことのほか、CSVストレージエンジンが使用されるため、サーバー上のデータファイル(/var/lib/mysql/mysql/slow_log.CSV)がCSV形式となることがあげられます。
CSV形式なので、ローカルPCにダウンロードして、Excelなどの表計算ソフトで参照できるようになります。

ということで、
「MySQLスロークエリログを定期的にローテートしつつCSV形式でメールに添付して定期的に送信する」
という簡単なシェルスクリプトを作成してみました。
（あまりキレイではありませんが）

#!/bin/bash
#	slow_query_log.sh
#
MAILTO=xxx@example.com
LOGFILE=/var/lib/mysql/mysql/slow_log_backup.CSV
WORKDIR=/tmp
MYSQL_USER=root
MYSQL_PASSWORD=<PASSWORD>

export LANG=C
DATESTR=`date +"%Y%m%d"`

# 1. スロークエリログテーブルをローテート
mysql -Bse "CREATE TABLE IF NOT EXISTS mysql.slow_log2 LIKE mysql.slow_log;" \
-u ${MYSQL_USER} -p${MYSQL_PASSWORD}
mysql -Bse "RENAME TABLE mysql.slow_log TO mysql.slow_log_backup, mysql.slow_log2 TO mysql.slow_log;" \
-u ${MYSQL_USER} -p${MYSQL_PASSWORD}

# 2. ローテート後のCSVファイルをzip圧縮
cp -p ${LOGFILE} ${WORKDIR}/slow_log-${DATESTR}.csv
cd ${WORKDIR}/
zip slow_log-${DATESTR}.zip slow_log-${DATESTR}.csv

# 3. zipファイルをメールに添付して送信
echo "MySQL Slow Query Log" |\
mutt -s "MySQL Slow Query Log(${DATESTR})" \
-a ${WORKDIR}/slow_log-${DATESTR}.zip -- ${MAILTO}

# 4. テンポラリファイルを削除
rm -f ${WORKDIR}/slow_log*

# 5. ローテート後のテーブルをDROP
mysql -Bse "DROP TABLE mysql.slow_log_backup;" \
-u ${MYSQL_USER} -p${MYSQL_PASSWORD}

# EOF

シェルスクリプトに管理者権限のパスワードを記載しているので、読み書き権限をrootユーザーのみに付与します。

　# chown root.root /path/to/slow_query_log.sh
　# chmod 700 /path/to/slow_query_log.sh

また、zip圧縮したCSVファイルをメールに添付して送信するために、muttコマンドを使用するので、必要に応じてインストールします。
以下はCentOSの場合。

　# yum install mutt

定期的にこのスクリプトを実行するようcronエントリーを追加します。
以下は、毎週日曜9時に実行する場合。

　# crontab -e

0 9 * * 0 /path/to/slow_query_log.sh 1>> /path/to/slow_query_log.log 2>&1

DBがボトルネックになっていそうな場合、サーバーのCPU、メモリをどんなに増やしても、クエリが不適切であれば、アプリケーションの性能はほとんど上がりません。
スロークエリログで、遅いクエリや極端に呼び出し回数の多いクエリを見つけましょう。

（参考）
・5.2.1 一般クエリーログおよびスロークエリーログの出力先の選択
https://dev.mysql.com/doc/refman/5.6/ja/log-destinations.html

・MySQL 5.1のスロークエリログ
http://nippondanji.blogspot.jp/2009/01/mysql-51.html

・MySQLでクエリチューニングことはじめ
http://tech.aainc.co.jp/archives/4634

今回は、スロークエリログをそのままメールで送信する形としましたが、mysqldumpslowコマンドを使うと、集計して見やすい形で出力してくれるので、その出力をメールに添付して送信するのもよい方法だと思います。
mysqldumpslowコマンドの説明は、以下などが参考になります。

・MySQL 5.1のmysqldumpslowで快速チューニング
http://d.hatena.ne.jp/sh2/20090414

• 0
• 4
• 1