MySQL 效能優化深度實戰：key_buffer_size 核心參數調優與 mysqladmin 監控技巧

MySQL 效能優化深度實戰：key_buffer_size 核心參數調優與 mysqladmin 監控技巧

核心參數：key_buffer_size

對 **MyISAM** 引擎影響最大的參數（對 InnoDB 無效）。控制索引塊緩衝區大小，所有執行緒共享。適當調大可減少磁碟 I/O，提升索引讀取速度。

如何判斷設定是否合理？

1. 關鍵狀態指標

mysql> SHOW GLOBAL STATUS LIKE 'key_read%';
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| Key_read_requests | 27813678764  -- 從緩存讀取索引請求次數
| Key_reads         | 6798830    -- 從磁碟讀取索引請求次數 (未命中)
+-------------------+-----------+

2. 未命中率計算

key_cache_miss_rate = Key_reads / Key_read_requests * 100%
-- 範例: 6798830 / 27813678764 ≈ 0.0244%

• < 0.1% (1/1000) 為優
• 0.1% ~ 1% 可接受
• > 1% 需調大 key_buffer_size

3. 單位時間未命中量 (更具參考價值)

累計值掩蓋時間維度，建議用 mysqladmin 取樣：

mysqladmin -u root -p -r -i 10 extended-status | grep Key_reads
# -r 相對值(差值)  -i 10 秒間隔
# 輸出範例：每 10 秒約 500 次 Key_reads → 每秒 ~50 次磁碟讀取

若磁碟 IOPS 能承受，此值可接受；若磁碟壓力大，需調優。

4. 緩衝區塊使用率

mysql> SHOW GLOBAL STATUS LIKE 'key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 0     -- 未使用塊數
| Key_blocks_used   | 50000 -- 曾用最大塊數
+-------------------+-------+

理想：Key_blocks_used / (Key_blocks_used + Key_blocks_unused) ≈ 80%
若 Key_blocks_unused = 0，說明緩衝區已滿，需調大或檢查是否過度索引。

mysqladmin 監控實戰技巧

1. 核心參數

• extended-status (可簡寫 ext 或 e)：等同 SHOW GLOBAL STATUS
• -r / --relative：顯示差值 (相對值)，而非累計值
• -i N / --sleep=N：每 N 秒刷新一次

2. 實戰命令範例

# 核心 QPS/查詢類型監控 (每 10 秒取樣)
mysqladmin -u root -p -r -i 10 ext | grep -E "Questions|Queries|Com_select|Com_insert|Com_update|Com_delete|Innodb_rows_"

# 輸出範例
| Com_select  | 1234 |
| Com_insert  |  200 |
| Com_update  |  150 |
| Com_delete  |   50 |
| Questions   | 2721 |  -- 包含所有指令(含 SHOW 等)
| Queries     | 2500 |  -- 僅計數查詢指令

3. 進階組合

搭配 awk 計算 QPS、緩存命中率等衍生指標，可寫入監控腳本對接 Grafana/Prometheus。

完整調優案例

某 MyISAM 為主的 4GB 記憶體伺服器：

• 原 key_buffer_size = 512M
• 觀測 Key_blocks_unused = 0，緩衝區滿載
• 調整為 key_buffer_size = 1G (物理記憶體 25%)
• 調整後 key_cache_miss_rate 從 0.15% 降至 0.03%，查詢延遲明顯下降

避坑指南

• 盲目調大： 過大導致 OS Swap，反而拖垮效能。MyISAM 建議不超過物理記憶體 25~30%。
• 忽略引擎差異： InnoDB 使用 innodb_buffer_pool_size，切勿混淆。
• 單一指標決策： 必須結合「未命中率」「單位時間磁碟讀取」「塊使用率」綜合判斷。

掌握 key_buffer_size 科學調優方法與 mysqladmin 即時監控技巧，讓 MyISAM 工作負載跑出最佳效能。