MySQL のスロークエリログを有効化して遅いSQLを特定する

本番環境で「なんか遅い」と感じたとき、原因特定の第一歩はスロークエリログです。一定時間以上かかったクエリを自動的に記録してくれるため、ボトルネックの発見に役立ちます。

スロークエリログの有効化

MySQL の設定ファイル（my.cnf または my.ini）に以下を追加します。

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

long_query_time は閾値で、この例では1秒以上かかったクエリが記録されます。開発環境では 0.1（100ミリ秒）など短めに設定すると、より多くのクエリを捕捉できます。

設定変更後は MySQL を再起動するか、動的に変更することも可能です。

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

現在の設定を確認する

有効になっているかどうかは以下で確認できます。

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

ログの中身を読む

スロークエリログには、クエリごとに以下の情報が記録されます。

実際のログはこのような形式になります。

# Time: 2024-01-15T10:23:45.123456Z
# User@Host: app_user[app_user] @ [192.168.1.100]
# Query_time: 3.456789  Lock_time: 0.000123  Rows_sent: 1  Rows_examined: 1500000
SET timestamp=1705314225;
SELECT * FROM orders WHERE status = 'pending';

この例では、150万行を走査して1行だけ返しています。明らかにインデックスが効いていません。

mysqldumpslow で集計する

ログが大量にある場合、付属ツールの mysqldumpslow で集計すると便利です。

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

似たようなクエリはパラメータが N や S に抽象化されるため、パターンとして把握しやすくなります。

インデックス未使用クエリも記録する

閾値以下でもインデックスを使っていないクエリを記録したい場合は、以下を追加します。

log_queries_not_using_indexes = 1

ただし、これを有効にするとログが急増することがあります。一時的な調査目的で使い、常時有効にするのは避けたほうがよいでしょう。

運用上の注意点

スロークエリログはディスク容量を消費し続けます。ログローテーションを設定しておかないと、ディスクフルで障害につながることもあります。logrotate などと組み合わせて定期的にローテーションする仕組みを整えておきましょう。

この流れを習慣化すれば、パフォーマンス問題を早期に発見できます。