服務器級參數
1. 表快取
SHOW STATUS LIKE 'open%tables';
Open_tables:目前開啟的表數。
Opened_tables:歷史累計開啟數。若持續快速增長,說明 table_cache 過小。
- 建議:
table_cache = 5000(視表總數調整)。
2. 執行緒快取
SHOW STATUS LIKE 'threads%';
Threads_created:建立執行緒總數。快速增長時增大 thread_cache_size(如 40)。
3. 關鍵字緩衝區
SHOW STATUS LIKE '%key_read%';
Key_reads / Key_read_requests = 未命中率。若每 1000 請求 > 1 次磁碟讀取,增大 key_buffer_size(如 384M)。
4. 臨時表
SHOW STATUS LIKE 'created_tmp%';
Created_tmp_disk_tables / Created_tmp_tables = 磁碟臨時表比率。調大 tmp_table_size 與 max_heap_table_size(兩者需同步設定)。
會話級參數(乘以連接數 = 總記憶體,謹慎設定)
1. 排序緩衝區
SHOW STATUS LIKE 'sort%';
Sort_merge_passes 大表示磁碟排序頻繁,增大 sort_buffer_size(如 4M)。
2. 讀取緩衝區
SHOW STATUS LIKE 'com_select';
SHOW STATUS LIKE 'handler_read_rnd_next';
- 表掃描率 =
Handler_read_rnd_next / Com_select。超過 4000 時增大 read_buffer_size(如 4M)。超過 8M 則需 SQL 層面優化。
調優原則
- 先觀察狀態變數,找出瓶頸。
- 逐項微調,避免一次改太多導致難以定位效果。
- 總記憶體分配不超過物理記憶體 70-80%(留給 OS 與其他程序)。
- 定期重新評估,隨業務增長調整。