【Mysql开启慢查询日志】

开启慢查询日志

mysql> show variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/21b6199c2548-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.07 sec)

mysql> show variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/21b6199c2548-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

时间阈值设置（超过1秒就记录）

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

查看已经被记录的慢查询数量

mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.01 sec)

分析慢查询日志‌

使用内置工具 mysqldumpslow

root@21b6199c2548:/# mysqldumpslow /var/lib/mysql/21b6199c2548-slow.log

Reading mysql slow query log from /var/lib/mysql/21b6199c2548-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:


root@21b6199c2548:/# 

使用第三方工具 pt-query-digest（Percona Toolkit）：

root@21b6199c2548:/# pt-query-digest /var/log/mysql/mysql-slow.log