[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slowquery.log
long_query_time = 5
log_queries_not_using_indexes = ON
# log_throttle_queries_not_using_indexes = 60 # 限制無索引查詢記錄頻率
[mysqld]
slow_query_log = 1
slow_query_log_file = E:\mysql\log\mysqlslowquery.log
long_query_time = 5
參數說明:
long_query_time:超過此秒數記錄,預設 10 秒,建議 1-5 秒log_queries_not_using_indexes:記錄未使用索引的查詢,易產生大量日誌,建議配合 log_throttle_queries_not_using_indexes 限流# 顯示前 20 筆最耗時查詢
mysqldumpslow -s t -t 20 /var/lib/mysql/slowquery.log
# 顯示前 20 筆執行次數最多查詢
mysqldumpslow -s c -t 20 /var/lib/mysql/slowquery.log
# 含 left join 的前 10 筆最慢查詢
mysqldumpslow -t 10 -s t -g "left join" /var/lib/mysql/slowquery.log
排序參數: t(時間), c(次數), l(鎖定時間), r(返回行數);前綴 a 表示反序。
wget https://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
tar zxvf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL
make && make install
# 缺少模組時
cpan DBI
cpan DBD::mysql
-lt slow:指定慢查詢日誌類型-sf "+SELECT,UPDATE":只分析 SELECT/UPDATE-sort t_sum:按總耗時排序(預設)-top 10:顯示前 10 筆-db dbname:指定資料庫# 耗時最久的 10 條 SELECT
mysqlsla -lt slow -sf "+SELECT" -top 10 slow.log > slow_top10_time.log
# 執行次數最多的 100 條 SELECT/UPDATE
mysqlsla -lt slow -sf "+SELECT,UPDATE" -top 100 -sort c_sum -db ultraxsmutf8 slow.log > slow_top100_count.log
mysql> FLUSH LOGS;
# 或動態切換
SET GLOBAL slow_query_log = OFF;
SET GLOBAL slow_query_log = ON;
# 截斷保留檔案 inode,避免 MySQL 寫入失敗
> /var/lib/mysql/slowquery.log
# 或
cp /dev/null /var/lib/mysql/slowquery.log
Can't locate DBI.pm → cpan DBIUse of uninitialized value ... at mysqlsla line 2962 → 移除 -sort t_sum(預設即為時間排序)long_query_time = 1-2,配合 log_throttle_queries_not_using_indexesEXPLAIN 分析 Top N 慢查詢,優化索引或 SQLSlow_queries 狀態變數,設定告警閾值