優化設定檔前，先了解 MySQL InnoDB 運作機制
MySQL 官方文件其實有一份文件在說明 MySQL 如何透過設定檔調整效能 (針對 5.5 版)，就是「Optimizing InnoDB Configuration Variables」這一篇文章，其中讓我了解很多 MySQL 的運作機制，以下是我的閱讀心得，若不備周全還請見諒。MySQL 有很多設計其實我看過也不是很懂，希望能拋磚引玉，期待更多的 MySQL 高手提供更正確完整地知識。如果您正在搜尋現成的 my.cfg 設定檔，那這篇文章肯定幫不上忙。
Configuring the Memory Allocator for InnoDB
一開始建議就是使用 InnoDB Engine，之後所有的效能改善與參數調整都是以 InnoDB Engine 為出發。MySQL InnoDB Engine 實做了一套基於作業系統之上的記憶體管理架構 (InnoDB-specific Memory Allocator)，對於多核心 CPU 的幻境下可以有最好的效能，特別是在高工作量的環境下，好讓資料庫需要大量記憶體操作時 (像是 Join) 可以更快的配置記憶體，不需要經過作業系統的高成本記憶體配置流程。但現在作業系統對於多核心記憶體管理已經改善許多，因此 MySQL 預設是使用系統的記憶體配置機制。我們可以透過調整 innodb_use_sys_malloc = ON 這個方式來切換我們需要的記憶體配置方式，當我們不使用系統的記憶體配置機制時，同時 innodb_additional_mem_pool_size 這個用來配置記憶體容量的參數也會一併失去作用。
Configuring InnoDB Change Buffering
我覺得 MySQL 中的 Buffer Pool 是效能處理的核心，Buffer Pool 會把硬碟資料讀到記憶體進行處理，將低 Disk I/O 的時間成本。當 MySQL 需要處理 INSERT, UPDATE, DELETE 查詢時，剛好發現需要處理的 Page 並未讀進 Buffer Pool，這時 MySQL 並不會立即從磁碟讀取 Page 到 Buffer Pool 中，因為 I/O 是效能的殺手。而這時 MySQL 會把更新記錄寫到 Cache Buffer，寫到記憶體當然快多了。然而當 Buffer Pool Thread 有空的時候才會載入真正需要更新的 Page 到 Buffer Pool，然後從 Buffer Cache 合併變更到 Buffer Pool，最後閒到發慌的時候才會 Flush Page 到磁碟上。
因此，Buffer Cache 只有當資料 Pages 不在 Buffer Pool 時，Change Buffering 才會有比較大的效能提升作用，可以減少 INSERT, UPDATE, 與 DELETE 處理時，花在 Disk I/O 的等待時間。
我們可以透過 innodb_change_buffering 參數來設定需要進 Cache Buffer 的動作，預設是全部 (all) 變更都會進行 Cache。文件提到有 none, inserts, deletes, changes, purges, all 這些設定，可以依據你的業務需求調整。
Adaptive Hash Index
Adaptive Hash Index 簡稱 AHI，是 MySQL 很重要的一個特性，會自動替 B-Tree 索引建立加速走訪的 Hash 索引，可以提高索引 Insert 與 Search 的速度 (Multiple Concurrent Joins)。MySQL 預設的 Index 採用 B-Tree 演算法，當 B-Tree 深度過高時，容易造成走訪速度緩慢。當 Table 被判斷為滿足進化條件之後，會將預設的 B-Tree Search Path 進行優化。由於 AHI 是自動偵測與自動索引維護 (流程很複雜，我看完就洗洗睡了～)，開了不一定好，如果命中率低，只是多耗費 CPU 進行計算而已。如有要更改設定，可以透過以下參數進行設定（預設是打開），建議透過 SHOW ENGINE INNODB STATUS 命令觀察 AHI 的運作狀態，來決定開或關。
innodb_adaptive_hash_index = ON
Configuring Thread Concurrency for InnoDB
網路很多教學都建議設為 CPU 核心數的兩倍!? 但我覺得其實不要超過核心數比較適合，如果機器是給 DB 專門使用，還是比較建議設定為與 CPU 核心數相同即可，不然連線數多的時候只會造成多餘的 Context Switch。透過 thread_concurrency 參數來設定你想要配置的 Thread 數量。
thread_concurrency = 12
Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)
Read-ahead 是 MySQL Buffer Pool 的預載機制，原理很簡單，就是在預先配置的記憶體 (即 Buffer Pool) 預先非同步載入磁碟中的分頁檔，而不是要用的時候才讀取，降低 I/O 讀取時間造成的效能問題。預載的概念似乎很簡單，但是 MySQL 要決定哪些片段 (Page Block) 應該預載可就麻煩多了，MySQL 提供 Linear 與 Random 兩種預載模式。
先解釋一下 Page 的群組機制，MySQL 會將數個 Pages 組成一個 Extent (實際分組的方式我不清楚，渴望高手能說明)，Extent 有點像是把 Pages 分組，好讓預載演算法可以工作。
以 Linear 模式來說，MySQL 會統計 Extent 被讀進 Buffer Pool 的 Page 數量，當這個數量超過 (或等於) innodb_read_ahead_threshold 設定值時 (預設為 56, 範圍是 0~64)，預載管理程式會已非同步的方式將整個 Extent 中的 Pages 全部讀進 Buffer Pool。如果接下來剛好需要操作到同一個 Extent 鄰近的 Page，而這些 Page 也剛好已非同步的方式預先讀進 Buffer Pool，那就可以直接使用，大幅減少 Disk I/O 時間。
另一種 Random 模式我不是很能夠理解，判斷邏輯是當在 Buffer Pool 發現有連續 13 個 Pages 來至同一個 Extent，MySQL 就會將整個 Extent 中的其他 Pages 讀進 Buffer Pool，Random 模式我實在無法理解有什麼好處！？後來的版本這個 Random 機制已經被移除了。
透過 SHOW ENGINE INNODB STATUS 可以觀察 Pages 被預載讀取的次數，過高的次數可能會浪費 Disk I/O 而吃掉了性能。可以觀察以下兩的統計值來調整適合的策略：
Innodb_buffer_pool_read_ahead 表示預載進入 Buffer Pool 的 Pages 數量
Innodb_buffer_pool_read_ahead_evicted 表示進到 Buffer Pool 後沒有被使用之後被踢出 Buffer Pool 的 Pages 數
Configuring the InnoDB Master Thread I/O Rate
為了讓效能更好，MySQL InnoDB Engine 有很多背景運作的機制，為了不讓背景程式過度佔用太多的 Disk I/O，MySQL 有一個設定值 innodb_io_capacity 可以來限制背景程序的 I/O 數 (官方說明)，如下：
innodb_io_capacity = 200
InnoDB Master Thread 會將 Buffer Pool 中的資料 (Dirty Pages) 寫回 Disk, innodb_io_capacity 這個參數定義了系統每秒可以處理的 Disk I/O 數量，用意是避免這個機制佔用了太多 Disk I/O 而影響到真正執行 Query 需要的資源。這個算是不容易調整的參數，如果您的 MySQL 伺服器是使用 SSD 那麼建議可以把這個值調高一些，讓 Buffer Pool 背景處理可以更快速。
Configuring InnoDB Buffer Pool Flushing
MySQL 雖然是在 Buffer Pool 中處理資料可以獲得高速效能 (因為是記憶體)，然而將 Buffer Pool 中已經變更的資料寫回磁碟還是必要的動作，總不能因為忽然當機的時候造成資料遺失吧，這個將 Buffer Pool 回寫到 Disk Page 的動作成為「Buffer Pool Flushing」。當 Buffer Pool 中的 Page 有發生變更時，就會變成 Dirty Page，MySQL 會在適當的時機由 Master Thread 將 Dirty Page 刷新到 Disk Page 中，但也不是持續回寫全部的 Dirty Page，我們可以透過調整 innodb_max_dirty_pages_pct (預設 75) 來控制 Dirty Pages 存在 Buffer Pool 中的比例，比較明顯的就是關閉 MySQL 時，這些 Buffer Pool 中 Dirty Page 會一次回寫到 Disk，當我們的 Buffer Pool 很大時，就需要一些時間來處理。 innodb_max_dirty_pages_pct 設定太低會造成 Master Thread 過度頻繁的刷新 Dirty Page，太高的話也會容易發生 Buffer Pool 被 Dirty Pages 佔滿，導致有新的 Page 要進來時必須立即回寫 Dirty Pages 到磁碟，也會影響效能。聽起來是不是很複雜呢？
Configuring Spin Lock Polling
MySQL 支援多核心的 CPU 工作環境，雖然多核心，但是資料 Lock 還是必須要妥善管理與控制。Spin 是一種「鎖」運作機制，當其中一個 Thread 取得鎖，其他的 Thread 必須等待 (Sleep) 一段時間，然後看看鎖是否被釋放，這個機制稱為 Spin Lock Polling。這個等待時間就是透過 innodb_spin_wait_delay 進行設定。比較短的 Spin Time 當然可以比較快讓其他 Thread 取得鎖，但是輪詢的頻率過高會耗費多餘的 CPU 運算，透過實測來調整參數比較有意義。官方預設值為 6，要調整這個值可以透過以下參數來設定：
innodb_spin_wait_delay = 6
Making the Buffer Pool Scan Resistant
防止你的 Buffer Pool 被污染，這裡的污染指的是因為演算法的關係，讓 Buffer Pool 讀進了太多不常使用的 (或只存取一次) Pages，造成真正需要進 Buffer Pool 常常存取的 Pages 被踢出。Buffer Pool 的管理可以說是 MySQL InnoDB 的運作效能核心，詳細的運作原理篇幅很長，可以參考我之前寫的另一篇「MySQL 效能優化 - 淺談 MySQL Buffer Pool 分頁管理機制」文章，這裡有介紹 MySQL 主要的 LRU 機制與 Buffer Pool 的運作原理，這裡就不多做介紹了。
Configuring Redo Log
MySql 處理 Transaction/Commit 時，會將 Redo log 寫到 Disk，在 MySql 的資料目錄中 (/var/lib/mysql)，可以看到 ib_logfile* 這樣的 Redo log 檔案，Transaction Log 會以循環的方式存進檔案中。Redo log 的作用是當 MySQL Crash 時，重新啟動 MySQL 可以讀取 Redo log 進行修復，這是 Database 一個很重要的機制 (官方說明)。如果你的業務有很多的 INSERT, UPDATE, DELETE，那麼也會常常有很多資料寫進 Redo Log，頻繁的檔案循環會影響效能。我們可以透過 innodb_log_file_size 指定 log 檔案的大小，預設是 5M。比較大的 innodb_log_file_size 可以減少 Buffer Pool 檢查與 Flush 的次數，相對會比較快，但是需要 Crash Recovery 時，就需要花上更多的時間來完成。另外 innodb_log_files_in_group 可以用設定 log 檔案的數量，有效的進行分割，預設為 2。
那麼加大 innodb_log_buffer_size 可以減少 Disk I/O 也明顯提昇效率，但也不是無限制的提高，既然 Redo Log 是 Disk I/O 那就容易造成效能問題，這時候就需要 Buffer 來加速，MySql 可以透過以下兩個與 Redo log 相關的參數進行調校。
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size 顧名思義就是要寫進 Disk 前的 Buffer，設太大沒有意義，萬一 Buffer 還沒回寫 Disk 發生 Crash 就慘了。另一個重要的參數就是 innodb_flush_log_at_trx_commit，可以設定為 0, 1, 2 三種模式，預設值為 1。
設定 0 表示每一秒會將 Log Buffer 中的資料寫進 Log File，並且立即刷新，衰的話 Crash 可能會造成一秒的資料遺失。
設定 1 表示發生 Commit 時就立即將 Log Buffer 寫進 Log File，並且立即刷新磁碟，可以保證 ACID。
設定 2 表示發生 Commit 時就立即將 Log Buffer 寫進 Log File，但不會刷新磁碟 (MySQL 預設每一秒會刷一次)，所以還是機會遺失資料。
看起來設定為 0 是最刺激的，我倒沒在產品上線環境中這樣設定過，特別的使用環境可能才會需要。如果您的 MySQL 有做 Replication 那麼官方建議不要更動 innodb_flush_log_at_trx_commit 的設定，保持在 1 會比較好，避免不同步的情況產生。
Configuring Multiple Buffer Pool Instances
MySQL InnoDB Buffer Pool 利用記憶體讀寫高速的特性提升效能，越大的 Buffer Pool 可以放進更多資料，速度也就越快。我們可以藉由調整 innodb_buffer_pool_size 設定值來加大 Buffer Pool Size，記憶體如果足夠的話可以設個幾 GB 來使用。有了很大的 Buffer Pool 不一定能夠解決問題，由於我們現在的環境都是 Multi-thread，但同時只能有一個 Thread 對 Buffer Pool 存取 (鎖)，這樣的設計其實很不利於多連線情況。MySQL 可以啟用多重 Buffer Pools 的機制來削減鎖競爭的問題，透過調整 64)，MySQL 會隨機讓連線使用不同的 Buffer Pool，減少排隊等待 Buffer Pool 的情況。
感想
其實無論是那種 RDBMS，都有設計很多效能優化機制與演算法，基本能利用的資源逃不了 CPU, 記憶體, 磁碟這三個元素。不同的業務需求可以透過 MySQL 細部的設定獲得更好的效能，沒有一定的解法。由於 MySQL 機制與演算法都會交互影響，善用 SHOW ENGINE INNODB STATUS 搭配系統資源監視，歸納出比較適合的設定是比較科學的作法。資料庫真的是博大精深啊！今天颱風天，終於把這篇文章完成了，呼～下次見～