MySQL 效能調優神器 tuning-primer.sh 實戰解讀與參數優化指南

tuning-primer.sh 是 Matthew Montgomery 開發的 MySQL 效能診斷腳本，能自動採集關鍵狀態變數、計算健康度指標、輸出彩色分級建議。本文詳細解讀實戰報告各區塊含義，並提供參數調優決策樹，助您從「看得懂報告」進化到「會調優參數」。

安裝與執行

wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh
chmod +x tuning-primer.sh
./tuning-primer.sh

前提： MySQL 運行 ≥ 48 小時（確保狀態變數具代表性），具備 PROCESS、SUPER 權限帳號。

報告色碼與等級含義

實戰報告深度解讀（節選關鍵區塊）

1. SLOW QUERIES — 慢查詢未開啟

The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 9589 out of 88956228 that take longer than 10.000000 sec. to complete

診斷： 慢查詢日誌未開啟，但估算有 9.5 萬筆查詢超過 10 秒。
處方： 立即開啟慢查詢日誌，降低 long_query_time 至 2-5 秒，啟用 log_queries_not_using_indexes。

2. BINARY UPDATE LOG — 過期清理缺失

The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually

風險： Binlog 無限增長佔滿磁碟。
處方： expire_logs_days = 7（配合備份週期），或啟用 binlog_expire_logs_seconds (MySQL 8.0+)。

3. MEMORY USAGE — 記憶體配置嚴重超限

Max Memory Ever Allocated : 11.87 G
Configured Max Per-thread Buffers : 10.38 G
Configured Max Global Buffers : 6.10 G
Configured Max Memory Limit : 16.48 G
Physical Memory : 8.00 G

Max memory limit exceeds 90% of physical memory

根因： 理論最大記憶體 (Global + Per-thread × Max Connections) = 16.48 GB，遠超實體 8 GB。
處方：

1. 大幅降低 max_connections（如 151 → 100）或升級實體記憶體
2. 削減 Per-thread Buffer：sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size、thread_stack
3. 精準設定 innodb_buffer_pool_size ≈ 實體記憶體 60-70%（扣除系統、線程、其他緩衝）

4. KEY BUFFER — MyISAM 索引快取過大

Current MyISAM index space = 8 K
Current key_buffer_size = 2.00 G
Key cache miss rate is 1 : 585673
Key buffer free ratio = 0 %
Your key_buffer_size seems to be too high. Perhaps you can use these resources elsewhere

診斷： 實際 MyISAM 索引僅 8 KB，卻分配 2 GB，命中率極低（1:58萬），空閒率 0%。
處方： key_buffer_size = 64M（或根據實際索引大小 + 20% 預留），釋放記憶體給 InnoDB Buffer Pool。

5. JOINS — 非索引 Join 與 Buffer 偏小

Current join_buffer_size = 132.00 K
You have had 1 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Note! This script will still suggest raising the join_buffer_size when ANY joins not using indexes are found.

診斷： 存在非索引 Join，腳本建議加大 join_buffer_size，但治本在於加索引，而非加 Buffer。
處方： 開啟 log_queries_not_using_indexes，定位非索引 Join SQL，補齊索引；join_buffer_size 維持 256K-1M 即可。

6. TABLE SCANS — 循序掃描比例過高

Current read_buffer_size = 2 M
Current table scan ratio = 4650 : 1
You have a high ratio of sequential access requests to SELECTs. You may benefit from raising read_buffer_size and/or improving your use of indexes.

診斷： 每 4650 次 SELECT 就有一次全表掃描。
處方： 優先補索引消除全表掃描；read_buffer_size 可調至 4-8M 緩解剩餘掃描壓力。

參數調優決策樹（核心參數版）

實體記憶體 8GB
├─ 系統保留: 1 GB
├─ InnoDB Buffer Pool: 4.5 GB (≈ 60%)
├─ 線程私有 (max_connections=200):
│   ├─ sort_buffer_size: 2 MB
│   ├─ join_buffer_size: 512 KB
│   ├─ read_buffer_size: 2 MB
│   ├─ read_rnd_buffer_size: 1 MB
│   └─ thread_stack: 256 KB
│   小計: 200 × 5.75 MB ≈ 1.15 GB
├─ MyISAM Key Buffer: 64 MB (實際索引極小)
├─ Query Cache: 0 (禁用)
├─ Binlog Cache: 32 MB
└─ 其他/預留: ~1.2 GB
總計 ≈ 7.9 GB < 8 GB ✓

持續優化閉環

1. 基線建立： 穩定運行 1 週後執行 tuning-primer，保存報告
2. 單參數變更： 每次調整 1-2 參數，觀察 24-48 小時
3. 關鍵指標追蹤：
   • 記憶體：Max_used_connections、Threads_connected、OOM 風險
   • I/O：Innodb_buffer_pool_reads、Innodb_log_waits、Created_tmp_disk_tables
   • 鎖：Table_locks_waited、Innodb_row_lock_waits、Innodb_row_lock_time_avg
   • 快取：Key_read_requests / Key_reads、Qcache_hits / Qcache_inserts
4. 版本控管： 參數變更納入 GitOps，附帶調優單、測試報告、回滾方案

從「看報告」到「懂根因、會決策、建閉環」，tuning-primer 是入門利器，但真正的調優功力在於對 MySQL 內核機制的深度理解與實戰驗證循環。