[MySQL8.0]実行時間が遅いクエリをログに出力する設定方法

2021年05月03日2023年10月10日

データベース MySQL

データベースはメインでMySQLを使うことが多いですが、DBがとにかく遅い。

DBはその特性上、データを保存していくため時間がたつについてどんどん遅くなっていきます。

実行時間がどんどん遅くなっていくということは、一つ一つのSQLが遅くなっているわけで、遅いSQLを見つけてチューニングしてあげる必要があります。

今回MySQL8.0での遅いクエリを見つけ出す方法を紹介します。

遅いクエリをログに出力する

MySQLのmy.cnfファイルにログ出力用の設定を追記ます。

/etc/my.cnf

[mysqld]
～省略～
slow_query_log
long_query_time=1

slow_query_log : クエリ出力を有効化する
long_query_time=1 : 実行時間が〇以上かかったものだけを出力（1だと1秒以上）

※ MySQL5.xと設定方法が異なるため注意が必要です。

追記が完了したらMySQLを再起動します。

$ systemctl restart mysqld

ログ出力先を確認

遅いクエリのログはデフォルトだと下記に出力されます。

/var/lib/mysql/{host名}-slow.log

MySQLにログインして出力先を確認することもできます。

mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| slow_query_log      | ON                                    |
| slow_query_log_file | /var/lib/mysql/{host名}-slow.log      |
+---------------------+---------------------------------------+
2 rows in set (0.01 sec)

出力ログの見方

ログの見方はとても簡単です。

/var/lib/mysql/{host名}-slow.log

# Time: 2021-05-03T06:56:30.689310Z
# User@Host: wluser[wluser] @ localhost []  Id:    17
# Query_time: 1.187056  Lock_time: 0.022949 Rows_sent: 0  Rows_examined: 0
use wifi_land_db;
SET timestamp=1620024989;
SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = '50f6973c433c49c7939696475d8ba5c9' AND wp_posts.post_type = 'oembed_cache' AND ((wp_posts.post_status = 'publish'))  ORDER BY wp_posts.post_date DESC;

Time : 実行日時
Query_time : 実行時間
Lock_time : ロックした時間
Rows_sent : クエリ実行で取得した行数
Rows_examined : クエリ実行で読み込んだ行数

何度も出力されるクエリは実行が遅い証拠ですのでEXPLAINを頭に付けてIndexが有効になっている確認する必要があります。またはSQL自体を見直してあげる必要があります。

確認後は、ログが出っぱなしにならないようにログ出力を無効化することも忘れずにおこなってください。