note: 設定 mySQL slow query 慢查詢Log

記錄 mySQL slow query 資料

以下資料，可在 /etc/my.conf 設定

slow_query_log     = 1      1=啟用記錄功能, 0=關閉記錄
long_query_time    = 0.3    當 SQL query 執行時間超過 0.3 秒 就會記錄
slow_query_log_file= "/tmp/mysql-log-slow-queries.log"

以下資料，可臨時於 mysql cli 中設定

set global slow_query_log = 1;
set global long_query_time = 0.3;
set global slow_query_log_file ='/tmp/mysql-log-slow-queries.log';

#若要記錄所有 沒用到 index ，可設定：
set global log_queries_not_using_indexes = 1;

記錄的內容，類似： (wordpress)

# Time: 211027 20:28:46
# User@Host: he_user[he_user] @ localhost []  Id: 883482
# Query_time: 0.314484  Lock_time: 0.000040 Rows_sent: 403  Rows_examined: 435
use hem_db;
SET timestamp=1635337726;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
# User@Host: he_user[he_user] @ localhost []  Id: 883478
# Query_time: 0.310598  Lock_time: 0.000040 Rows_sent: 403  Rows_examined: 435
SET timestamp=1635337726;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
# Time: 211027 20:28:47
# User@Host: he_user[he_user] @ localhost []  Id: 883478
# Query_time: 0.425679  Lock_time: 0.000050 Rows_sent: 1  Rows_examined: 51424
SET timestamp=1635337727;
SELECT count(DISTINCT(ip)) AS pageview
       FROM wp_statpress
       WHERE date = '20211027' AND spider='' and feed='';
# Time: 211027 20:29:03
# User@Host: he_user[he_user] @ localhost []  Id: 883481
# Query_time: 16.403992  Lock_time: 0.000046 Rows_sent: 1  Rows_examined: 2443802
use hem_db;
SET timestamp=1635337743;
SELECT count(DISTINCT(ip)) AS pageview
       FROM wp_statpress WHERE spider='' AND feed='';

如果要記錄所有執行過的命令：

set global general_log_file='/tmp/all_sql.txt';
set global general_log=1;