[mysql] 追蹤與紀錄執行過久的 SQL

Mysql 5.7 有以下兩個設定可以協助你排查 SQL 執行的狀況以及相關指標,由於這類設定可能會影響效能上的表現,預設會是關閉的,以下簡介如何開啟設定以及相關的設定檔。

  1. General Query Log:
    記錄下所有執行的 SQL query 供排查,開啟後通常會伴隨大量的寫入因此不建議在生產環境開啟設定,範例輸出像是以下:
/usr/local/opt/[email protected]/bin/mysqld, Version: 5.7.35-log (Homebrew). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2022-08-06T01:24:01.134626Z         2 Query     select * from users
2022-08-06T01:24:15.940952Z         2 Query     SELECT `event_time`, `user_host`, `thread_id`, `server_id`, `command_type`, `argument` FROM `general_log` ORDER BY `event_time` LIMIT 0,1000

開啟 general_log

我們可以透過以下 SQL 讀取/設定目前的 gerenal_logs 設定

show variables like '%log%' -- 查詢 general_logs / log_output / general_log_file 目前設定值;

要注意的有以下三個值

  1. general_log:ON / OFF 開啟 general_logs 與否
  2. general_log_file:寫入指定目的地路徑的檔案
  3. log_output:FILE / TABLE 寫入目的地為檔案 或資料表( mysql.general_log
  • 透過 SQL 更改 runtime 設定
    設定會立即生效不需要重啟 mysql service,但重啟 mysql 後需要重新設定
set global general_log='ON' -- 開啟 general_log 設定;
set global general_log_file='/usr/local/var/mysql/general_logs.log' -- 設定 general_log 的檔案位置(注意寫入檔案的權限必須給予 mysql user);
set global log_output='FILE' -- 寫入至檔案;
  • 透過 my.cnf 設定
    重啟後 mysql 預設會依照以下路徑的先後讀取設定檔(mysql -h 可以看到此提示)
    • /etc/my.cnf
    • /etc/mysql/my.cnf
    • /usr/local/etc/my.cnf
    • ~/.my.cnf
[mysqld]
....
general_log=1
general_log_file='/usr/local/var/mysql/general_logs.log'
log_output=FILE

  1. Slow Query Log:
    對比於 general_log 提供更好的排查方便性,可以紀錄執行超過指定的時間 SQL query 並記錄下來,範例輸出如下:
# Time: 2022-08-06T13:27:00.284584Z
# [email protected]: root[root] @ localhost [127.0.0.1]  Id:     5
# Query_time: 10.004269  Lock_time: 0.000279 Rows_sent: 44  Rows_examined: 44
SET timestamp=1659792420;
SHOW FULL COLUMNS FROM `articles` FROM `blog`;

開啟 slow_query_log

我們可以透過以下 SQL 讀取/設定目前的 slow_query_log 設定

show variables like '%log%' -- 查詢 slow_query_log / log_output / slow_query_log_file 目前設定值;
show variables like 'long_query_time' -- 查詢 long_query_time 目前設定值;

要注意的有以下三個值

  1. slow_query_log:ON / OFF 開啟 general_logs 與否
  2. slow_query_log_file:寫入指定目的地路徑的檔案
  3. log_output:FILE / TABLE 寫入目的地為檔案 或資料表( mysql.slow_log
  4. long_query_time:執行時間超過幾秒的 SQL 視為 slow_query
  • 透過 SQL 更改 runtime 設定
    設定會立即生效不需要重啟 mysql service,但重啟 mysql 後需要重新設定
set global slow_query_log='ON' -- 開啟 slow_query_log 設定;
set global slow_query_log_file='/usr/local/var/mysql/slow_query.log' -- 設定 slow_query_log 的檔案位置(注意寫入檔案的權限必須給予 mysql user);
set global log_output='FILE' -- 寫入至檔案;
set global long_query_time=10 -- 執行超過 10 秒視為 slow query;
  • 透過 my.cnf 設定
    重啟後 mysql 會自動讀取設定,設定路徑如 general_log
[mysqld]
....
slow_query_log=1
slow_query_log_file='/usr/local/var/mysql/slow_query.log'
log_output=FILE
long_query_time=10

參考資料:

Leave a Reply