MySQL 效能優化之參數配置修改(innodb_buffer_pool_size)

網站架好以後，為了網站的訪問速度等問題，必須對某些服務器的配置做優化，其中資料庫的部分就變得非常重要，因為網站的訪問幾乎都會關係到資料庫的存取，因此資料庫的效能及利用率的好壞，直接影響網站的訪問速度。

資料庫的 buffer pool 大小

innodb_buffer_pool_size 這個參數設置，定義了 InnoDB 存儲引擎的表資料和索引資料的最大記憶體緩衝區大小。適當的增加這個參數的大小，可以有效的減少 InnoDB 類型的表的磁碟 I/O 。疑~ 這不就是我想要的嗎？ 能夠減少磁碟 I/O 意味著增加用戶訪問速度，再者因為 Amazon EC2 的免費方案，磁碟 I/O 每月的免費用量是 200萬次 I/O，若能降低 I/O 次數，也能比較好的控制不要超過這個免費的額度。

然而這個參數值要設多少呢？ 網上有很多給的建議是 "一個以 InnoDB 為主的專用資料庫伺服器上，可以把該參數設置為實體記憶體大小的 60%-70%"

如果你照著這個標準去設定的話，肯定會有問題，如果你是獨立的資料庫服務器，這樣設定沒甚麼問題。然而如果你的服務器上不只是資料庫服務，還有其他的各種服務，甚至你的記憶體大小都有限制(例如Amazon EC2 的免費方案記憶體大小只有 613 MB)。那麼就不可以按照記憶體的 60-70% 來設置這個參數，否則你的資料庫服務會很容易當機，因為記憶體會發生無法配置。

錯誤訊息如：

120716 7:09:09 InnoDB: Completed initialization of buffer pool

120716 7:09:09 InnoDB: Fatal error: cannot allocate memory for the buffer pool

120716 7:09:09 [ERROR] Plugin ‘InnoDB’ init function returned error.

120716 7:09:09 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

至於到底要設多少呢？ 這沒有一定的定論，要看實際你的服務器的配置以及你的網站的狀況。以我的例子，我是設定成 128 MB，但仍會出現記憶體無法配置的狀況，後來我把這個數值改成 64MB，狀況有好一些，但仍無法完全避免，不過狀況確有改善，也因此整個 MySQL 效能優化並不是單一事件可以調整改善，必須配合網站伺服器以及其他各方面一起觀察調配才能達到最佳效果。

更改 /etc/my.cnf 檔案，增加或修改innodb_buffer_pool_size = 64M

資料庫的 log 檔大小以及 log buffer 大小

其實像資料庫 log 檔大小以及 log buffer 大小也會影響系統效能，因為這個關係到磁碟 I/O，如果 buffer 小的話，容易造成磁碟 I/O ，寫入 log 檔的週期也會，頻繁的寫入同樣會增加磁碟 I/O 並降低系統效能。因此理論上說，innodb_log_file_size 的大小越大越好，不過若這個值太大，當 MySQL innodb 崩潰時，要重新恢復就必須花比較長的時間。

設定方式，更改 /etc/my.cnf 檔案，增加或修改

innodb_log_buffer_size = 8M

innodb_log_file_size = 64M