概述#

本章探討如何為 MySQL 伺服器建立良好的配置檔。正確的配置並非從研究配置選項開始，而是從理解 MySQL 的內部運作開始。先理解內部行為，再據此決定配置方式，最後比對現有配置並修正差異。

重點： 最重要的兩個配置選項是 innodb_buffer_pool_size 和 innodb_log_file_size。絕大多數真實世界的配置問題，只要正確設定這兩個參數就能解決。

MySQL 有大量可調整的設定，但你不應該全部去改。通常只需正確設定基本參數（真正重要的只有少數幾個），然後把時間花在 schema 優化、索引設計和查詢調校上更有價值。

MySQL 配置的運作方式#

配置檔位置與基本規則#

MySQL 從命令列參數和配置檔取得設定資訊。在 Unix 系統上，配置檔通常位於 /etc/my.cnf 或 /etc/mysql/my.cnf。

確認配置檔實際位置：

$ which mysqld
/usr/sbin/mysqld
$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

配置檔以區段（section）劃分，每個區段以方括號中的名稱開頭。MySQL 伺服器通常讀取 [mysqld] 區段，用戶端程式讀取 [client] 區段。

語法、作用域與動態性#

• 配置設定使用全小寫，單字間以底線或破折號分隔（兩者等效，建議統一風格）
• 全域作用域（Global scope）：如 query_cache_size
• 會話作用域（Session scope）：如 sort_buffer_size，有全域預設值但可逐連線設定
• 每查詢配置：如 join_buffer_size，每個 join 可能分配一個 buffer

動態修改變數的方式：

SET sort_buffer_size = <value>;
SET GLOBAL sort_buffer_size = <value>;
SET @@session.sort_buffer_size := <value>;
SET @@global.sort_buffer_size := <value>;

注意： 動態設定在 MySQL 關閉後會遺失。修改全域值不會影響已存在的連線，只影響新連線。使用 SET 指令時不能用 1M 這種後綴，必須寫完整數值如 1048576。

動態設定變數的副作用#

動態修改配置可能產生非預期的副作用：

建議： 不要全域提高 per-connection 設定的值。像 sort_buffer_size 這類參數應保持小值，只在特定查詢需要時才臨時提高：

SET @@session.sort_buffer_size := <value>;
-- 執行查詢...
SET @@session.sort_buffer_size := DEFAULT;

入門建議#

• 設定值過大比過小更危險——可能耗盡記憶體導致 swap 或 crash
• 必須搭配監控系統驗證配置變更的效果
• 先優化查詢和 schema（如加索引），再調整配置
• 目標是「夠好」而非「完美」——追求完美配置完全不切實際
• 建議將配置檔放入版本控制

不該做的事#

以下做法都是有害的：

• 不要使用「比率調優」（Tuning by Ratio）：例如「key cache 命中率應高於某個百分比」這類規則完全錯誤。命中率取決於工作負載，與 cache 大小無關。Oracle DBA 早已放棄比率調優，MySQL DBA 也應該跟進
• 不要使用調優腳本（Tuning Scripts）：網路上流行的調優腳本將錯誤實踐編碼化，傳播給成千上萬的人
• 不要相信記憶體消耗公式：MySQL crash 時印出的那個公式來自遠古時代，不可靠也無用
• 不要盲目信任網路上的配置建議：很多部落格和論壇的建議品質參差不齊

建立配置檔#

不存在「一體適用」的最佳配置檔。以下是書中建議的最小範例配置：

[mysqld]
# GENERAL
datadir                  = /var/lib/mysql
socket                   = /var/lib/mysql/mysql.sock
pid_file                 = /var/lib/mysql/mysql.pid
user                     = mysql
port                     = 3306
storage_engine           = InnoDB

# INNODB
innodb_buffer_pool_size  = <value>
innodb_log_file_size     = <value>
innodb_file_per_table    = 1
innodb_flush_method      = O_DIRECT

# MyISAM
key_buffer_size          = <value>

# LOGGING
log_error                = /var/lib/mysql/mysql-error.log
log_slow_queries         = /var/lib/mysql/mysql-slow.log

# OTHER
tmp_table_size           = 32M
max_heap_table_size      = 32M
query_cache_type         = 0
query_cache_size         = 0
max_connections          = <value>
thread_cache_size        = <value>
table_cache_size         = <value>
open_files_limit         = 65535

[client]
socket                   = /var/lib/mysql/mysql.sock
port                     = 3306

重點說明：

• storage_engine = InnoDB：大多數情況下 InnoDB 是最佳選擇
• open_files_limit = 65535：在 Linux 上開啟檔案描述符很便宜，設大一點避免 “too many open files” 錯誤
• query_cache 關閉：query cache 在多數場景下弊大於利
• socket 和 PID file 明確指定：避免依賴編譯預設值導致的潛在 bug

檢視伺服器狀態變數#

使用以下命令觀察狀態變數隨時間的增量變化：

$ mysqladmin extended-status -ri60

配置記憶體使用#

記憶體配置步驟#

1. 確定 MySQL 可使用的記憶體絕對上限
2. 估算 per-connection 記憶體需求（排序緩衝區、臨時表等）
3. 預留作業系統所需記憶體（建議 2 GB 或總記憶體的 5%，取較大值）
4. 剩餘記憶體分配給 MySQL 的各種快取

InnoDB Buffer Pool#

InnoDB buffer pool 是最重要的記憶體配置。它不只快取索引，還包含：

• 列資料（row data）
• 自適應雜湊索引（adaptive hash index）
• Insert buffer
• 鎖資訊（locks）
• 其他內部結構

設定 buffer pool 大小的建議流程：

1. 從伺服器總記憶體開始
2. 減去作業系統和其他程式的需求
3. 減去 MySQL 自身的 per-query 記憶體需求
4. 減去 InnoDB log files 大小（讓 OS 能快取它們）
5. 減去其他 MySQL 快取（MyISAM key cache、query cache 等）
6. 除以 1.05（InnoDB 管理 buffer pool 的額外開銷約 5%）
7. 向下取整

flowchart TD
    A["伺服器總記憶體"] --> B["減去 OS 需求"]
    B --> C["減去 MySQL per-query 緩衝"]
    C --> D["減去 InnoDB Log Files"]
    D --> E["減去其他 MySQL 快取"]
    E --> F["除以 1.05（管理開銷）"]
    F --> G["向下取整 → innodb_buffer_pool_size"]

範例： 192 GB 記憶體的伺服器，專用於 MySQL/InnoDB，log files 共 4 GB：扣除約 10 GB（OS + MySQL 自身需求）和 4 GB（log files），結果約 177 GB，向下取整為約 168 GB——這已佔總記憶體的 87.5%，遠超「75% 法則」。

重點： 寧可設小不設大。Buffer pool 設小 20% 可能只影響幾個百分點的效能；設大 20% 則可能導致 swap、磁碟 thrashing 甚至 OOM crash。

大型 buffer pool 的挑戰：

• 關閉和暖機時間長——可用 innodb_max_dirty_pages_pct 在關閉前降低 dirty pages
• 暖機加速：Percona Server 提供 buffer pool reload 功能（MySQL 5.6 也引入類似功能），可將暖機時間從數小時縮短至幾分鐘

MyISAM Key Cache#

MyISAM 只快取索引（資料由作業系統快取）。

• key_buffer_size 不應超過索引總大小，也不應超過預留給 OS 快取的 25%~50%
• 即使不使用 MyISAM 表，仍需設定少量（如 32M），因為 MySQL 內部操作（如 GROUP BY 的臨時表）會用到 MyISAM
• 可建立多個命名 key buffer，突破單一 buffer 4 GB 限制：

key_buffer_1.key_buffer_size = 1G
key_buffer_2.key_buffer_size = 1G

使用 cache misses/sec 而非命中率： 例如硬碟能做 100 random reads/sec，5 次 miss/sec 不會造成 I/O bound，但 80 次/sec 就會有問題。

$ mysqladmin extended-status -r -i 10 | grep Key_reads

Thread Cache#

thread_cache_size 控制快取中可保留的閒置 thread 數量，避免頻繁建立新 thread。

• 監控 Threads_connected 的波動範圍，將 cache 設為峰值與低谷的差值
• 每個快取中的 thread 約使用 256 KB 記憶體
• 目標：讓 Threads_created 的增長速度低於每秒 10 個，最好低於 1 個

Table Cache#

• MySQL 5.1 分為 table_open_cache 和 table_definition_cache 兩部分
• 起始值建議為 max_connections 的 10 倍，但不超過 10,000
• 監控 Opened_tables 的增長速度判斷是否夠大
• 設太大可能反而變慢——cache 查找演算法效率不高
• 若工作負載不可快取（如數萬張表均勻使用），cache miss 比「昂貴的 cache 查找 + cache miss」更好

InnoDB Data Dictionary#

InnoDB 維護自己的 per-table cache（data dictionary），無法配置大小且不會過期。

• 每張表約佔 4 KB 或更多
• 在擁有數千張表的情況下，可能導致記憶體看似「洩漏」
• 首次開啟表時計算統計資訊（statistics）非常耗費 I/O
• 關閉 innodb_stats_on_metadata 避免查詢 INFORMATION_SCHEMA 時觸發昂貴的統計更新

配置 MySQL 的 I/O 行為#

InnoDB Transaction Log#

InnoDB 使用 transaction log（redo log）將隨機磁碟 I/O 轉換為循序 I/O：

• 交易提交時只需將變更寫入 log（循序寫入），而非直接 flush buffer pool 到磁碟（隨機寫入）
• Log 採循環寫入方式，寫滿後從頭開始覆寫
• 背景 thread 負責將變更從 buffer pool 寫回資料檔

flowchart TD
    TX["交易提交"] --> LOG["Redo Log（循序寫入，快速）"]
    LOG --> ACK["回傳提交成功"]
    LOG -.->|"背景執行緒"| BG["Buffer Pool → Data Files（隨機寫入）"]

    style LOG fill:#bfb,stroke:#333
    style BG fill:#fbb,stroke:#333

Log file 大小設定：

• 預設 2 個 5 MB 檔案（共 10 MB）——遠遠不夠，高效能工作負載需要數百 MB 甚至 GB 級別
• 太小：頻繁 checkpoint，寫入查詢可能 stall
• 太大：crash recovery 時間變長（但新版 MySQL 已大幅改善）
• 經驗法則：讓總 log 大小能容納一小時的伺服器活動量

Log buffer（innodb_log_buffer_size）：

• 預設 1 MB，建議範圍 1~8 MB
• 大型交易或高記憶體伺服器可設為 32~128 MB 以減少 mutex 爭用
• Log buffer 在以下情況 flush：buffer 滿、交易提交、每秒一次（先到先 flush）

innodb_flush_log_at_trx_commit#

控制 log buffer 何時 flush 到磁碟，是效能與持久性的核心取捨：

重點： 「寫入 log file」只是從 InnoDB 記憶體移到 OS cache（仍在記憶體中）；「flush 到持久儲存」才是真正寫入磁碟。對高效能交易需求，建議保持設定 1 並搭配 RAID + 電池備援寫入快取（BBU write cache）。

innodb_flush_method#

控制 InnoDB 如何與檔案系統互動，同時影響 data files 和 log files：

建議： Unix 系統 + RAID BBU write cache → 使用 O_DIRECT。沒有 RAID BBU → 使用預設值或 O_DIRECT，取決於應用場景。

InnoDB Tablespace#

• 使用 innodb_data_file_path 指定 tablespace 檔案
• 強烈建議啟用 innodb_file_per_table：每張表一個 .ibd 檔案，方便管理和回收空間
• 即使啟用 file_per_table，仍需要主 tablespace 存放 undo logs 等系統資料
• 建議停用 autoextend 或設定合理上限，因為 tablespace 一旦增長就無法收縮

Undo log 與 tablespace 膨脹：

• 長時間未提交的交易 + REPEATABLE READ 會導致 InnoDB 無法清除舊 row versions
• 監控 SHOW INNODB STATUS 的 history list length
• 可用 innodb_max_purge_lag 限制未清除交易數量來節流寫入

Doublewrite Buffer#

Doublewrite buffer 防止 partial page write 導致的資料損毀：

• InnoDB 先將頁面寫入 doublewrite buffer（循序寫入），再寫入實際位置
• 效能影響約幾個百分點，換取原子性和持久性保證
• 在某些檔案系統（如 ZFS）上可停用，因為 ZFS 自身提供類似保護
• 在 replica 上可考慮停用（innodb_doublewrite = 0）

其他 I/O 配置#

sync_binlog：

• 預設 0（OS 決定何時 flush）
• 設為 1：每次交易都 sync binary log，確保 binary log 與交易資料一致（對 replication 和 point-in-time recovery 至關重要）
• 代價高昂——比 innodb_flush_log_at_trx_commit=1 更昂貴，因為每次寫入都增加 binary log 大小需要更新檔案系統 metadata

注意： 不要用 rm 刪除舊 binary logs，否則伺服器會混亂且無法自動清理。應使用 expire_logs_days 或 PURGE MASTER LOGS。

MyISAM I/O 配置#

• delay_key_write：延遲寫入 key buffer 直到表格關閉（OFF/ON/ALL）
• 好處：減少 I/O；壞處：crash 時索引損毀、關閉表格變慢
• myisam_recover：建議啟用 BACKUP,FORCE，自動檢查和修復 MyISAM 表格

配置 MySQL 並行處理#

InnoDB Concurrency 配置#

新版 MySQL 中，最好的做法通常是升級伺服器而非調整這些參數。

MyISAM Concurrency 配置#

• concurrent_insert：0=不允許、1=無空洞時允許（預設）、2=強制 append 到尾端
• low_priority_updates：給 SELECT 更高優先權，改善 MyISAM 的讀取並行性

工作負載導向的配置#

BLOB / TEXT 工作負載#

• BLOB/TEXT 值無法使用記憶體臨時表，一律寫到磁碟
• 緩解方式：
  • 使用 SUBSTRING() 轉換為 VARCHAR
  • 將 tmpdir 放在 tmpfs（記憶體檔案系統）
• InnoDB 對長列（BLOB/TEXT）的儲存：行內存 768 byte 前綴，超出部分使用外部儲存頁（16 KB/頁）
• 外部儲存的副作用：浪費空間、停用 adaptive hash index、全表掃描變慢
• 建議：考慮合併多個長列為單一 BLOB 列、使用 COMPRESS() 壓縮

Filesort 優化#

• max_length_for_sort_data：決定使用 single-pass 或 two-pass filesort 演算法
• max_sort_length：BLOB/TEXT 排序時使用的前綴長度
• VARCHAR 列的最大長度（非實際長度）計入 max_length_for_sort_data——這是建議 VARCHAR 不要設太大的原因之一

完成基本配置#

關鍵設定值的選擇#

安全與穩定性設定#

一般伺服器設定#

Replication 安全設定#

進階 InnoDB 設定#

總結#

配置 MySQL 的核心原則：

1. 從最小範例配置開始，設定基本選項
2. 加入安全與穩定性設定
3. 根據 InnoDB 新版本功能進行適當配置
4. 不要「調優」——不要使用比率、公式、調優腳本
5. 如果出現問題，從查詢的 response time 分析開始，而非從配置選項下手
6. 最重要的兩個參數：innodb_buffer_pool_size 和 innodb_log_file_size