Linux 高負載下 MySQL 徹底優化實戰：關鍵參數調校與狀態監控

背景

1GB 記憶體伺服器隨線上用戶增長頻繁死機/卡頓。MySQL 具伸縮性演算法，透過參數調校可在有限記憶體發揮最大效能。

配置檔選擇

/usr/local/mysql/share/mysql/ 下有 my-huge.cnf、my-medium.cnf、my-large.cnf、my-small.cnf。複製適合者至 /etc/my.cnf 修改。

三大核心參數：key_buffer_size、query_cache_size、table_cache。

1. key_buffer_size (MyISAM 專用)

• 控制索引緩衝區大小，決定索引讀取速度
• 建議初始 16MB，大型站點需調大
• 監控指標：Key_read_requests / Key_reads 比率應 ≥ 1:100 (1:1000 更佳)
  SHOW STATUS LIKE 'key_read%';
• 估算法：所有表索引總大小之和 (如 125MB) 作為參考下限

2. query_cache_size (4.0.1+)

• 快取 SELECT 結果，相同查詢 (區分大小寫) 直接回傳，最高提升 238% 效能
• 關鍵狀態值：
  Qcache_inserts、Qcache_hits、Qcache_lowmem_prunes、Qcache_free_blocks、Qcache_total_blocks
• 判讀：
  Lowmem_prunes 大 → 快取不足需調大
  Hits 大 → 重複查詢多，快取有效
  Hits 小 → 重複率低，快取反而拖慢效能，可關閉
  Free_blocks 大 → 碎片多
• 範例設定：query_cache_size=32M; query_cache_type=1

3. table_cache

• 表快取大小，打開表放入快取加速後續存取
• 監控 Open_tables / Opened_tables：
  若 Open_tables == table_cache 且 Opened_tables 持續增長 → 需調大
• 1GB 記憶體建議 128-256，過大導致檔案描述符不足

其他重要參數

sort_buffer_size = 1M
max_connections = 120
wait_timeout = 120
back_log = 100
read_buffer_size = 1M
thread_cache = 32
interactive_timeout = 120
thread_concurrency = 4  -- CPU核心數 x2
skip-innodb  -- 不需 InnoDB 可關閉節省資源

二進位日誌與慢查詢日誌

• 不需時間點恢復/複寫可關閉 log-bin (前加 # 註解)
• 啟用慢查詢日誌：log_slow_queries, long_query_time=10, log-queries-not-using-indexes