Mysql慢日志

本文介紹Mysql慢日志相關知識

1.先檢視目前日志輸出方式

show variables like '%log_output%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
           

log_output 參數設定日志檔案的輸出，可選值為 TABLE, FILE ,NONE; "TABLE" 意思為設定日志分别記錄到 mysql 庫的 general_log 和 slow_log 表中; "FILE" 意思為記錄日志到作業系統的檔案中， "NONE" 意思為取消日志記錄。

set global log_output='FILE,TABLE';

2.檢視慢日志是否開啟

show variables like '%slow%';

+---------------------+------------------------------------+
| Variable_name       | Value                              |
+---------------------+------------------------------------+
| log_slow_queries    | ON                                 |
| slow_launch_time    | 2                                  |
| slow_query_log      | ON                                 |
| slow_query_log_file | /ssddata2/mysql/3306/logs/slow.log |
+---------------------+------------------------------------+
           

3.設定開啟慢日志

set global log_slow_queries = on;

4.查詢沒有index的查詢記錄開關

show global variables like '%indexes%';

+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes          | OFF   |
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
           

第一個參數 表示是否開啟記錄沒有index的查詢，第二個

參數用來做日志記錄的流量控制，一分鐘可以記錄多少條，預設0是表示不限制。

5.修改慢日志存儲路徑

set global slow_query_log_file = '/ssddata2/mysql/3306/logs/slow.log'；

6.設定慢日志記錄時間

show variables like "%long%"

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
           

set global long_query_time = 5;

slow_launch_time的設定跟慢查詢日志的查詢閥值設定不同，表示了thread create的一個閥值，如果thread create的時間超過了這個值，這變量slow_launch_time的值加1.

而設定Long_query_time表示超過多少秒的查詢就寫入日志，預設的是10s,設定為0的話表示記錄所有的查詢。

7.管理型SQL可以通過--log-slow-admin-statements開啟記錄管理型慢SQL

a. The query must either not be an administrative statement, or --log-slow-adminstatements must have been specified.

b. The query must have taken at least long_query_time seconds, or log_queries_not_using_indexes must be enabled and the query used no indexes for row lookups.

c. The query must have examined at least min_examined_row_limit rows.

d. The query must not be suppressed according to the log_throttle_queries_not_using_indexes setting.