Help us understand the problem. What is going on with this article?

MySQLのスロークエリのログを出力する設定

前提環境

  • MySQL 8.0

スロークエリログを出力する設定

my.cnf
slow_query_log
long_query_time=1
log_queries_not_using_indexes
  • slow_query_log … スロークエリログ出力を有効にする
  • long_query_time … 指定した秒以上かかったクエリ(デフォルトは10)
  • log_queries_not_using_indexes … インデックスを利用していないクエリを出力する

反映確認

※hostname … サーバに設定されているホスト名

mysql > SHOW VARIABLES LIKE 'slow_query%';
+---------------------+------------------------------------+
| Variable_name       | Value                              |
+---------------------+------------------------------------+
| slow_query_log_file | /var/lib/mysql/[hostname]-slow.log |
+---------------------+------------------------------------+
1 row in set (0.01 sec)

スロークエリログ出力確認

※hostname … サーバに設定されているホスト名

cat /var/lib/mysql/[hostname]-slow.log
/usr/sbin/mysqld, Version: 8.0.16 (MySQL Community Server - GPL). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2019-12-01T17:09:57.232128Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.002923  Lock_time: 0.000386 Rows_sent: 2  Rows_examined: 575
SET timestamp=1575220197;
SELECT * FROM performance_schema.variables_info WHERE VARIABLE_NAME LIKE 'slow_query%';

Query_time … クエリの実行にかかった時間
Lock_time … ロックした時間
Rows_sent … クエリ実行後に返却した行の数
rows_examined … クエリ実行で読み込んだ行数

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした