當我們被人僱來監測 MySQL 效能時，人們希望我們能夠檢視一下 MySQL 配置然後給出一些提高建議。許多人在事後都非常驚訝，因為我們建議他們僅僅改動幾個設定，即使是這裡有好幾百個配置項。這篇文章的目的在於給你一份非常重要的配置項清單。
我們曾在幾年前在部落格網站裡給出了這樣的建議，但是 MySQL 的世界變化實在太快了！
寫在開始前…
即使是經驗老道的人也會犯錯，會引起很多麻煩。所以在盲目的運用這些推薦之前，請記住下面的內容：
一次只改變一個設定！這是測試改變是否有益的唯一方法。
大多數配置能在執行時使用 SET GLOBAL 改變。這是非常便捷的方法它能使你在出問題後快速撤銷變更。但是，要永久生效你需要在配置檔案裡做出改動。
一個變更即使重啟了 MySQL 也沒起作用？請確定你使用了正確的配置檔案。請確定你把配置放在了正確的區域內 (所有這篇文章提到的配置都屬於 [mysqld])
站群伺服器在改動一個配置後啟不來了：請確定你使用了正確的單位。例如，innodb_buffer_pool_size 的單位是 MB 而 max_connection 是沒有單位的。
不要在一個配置檔案裡出現重複的配置項。如果你想追蹤改動，請使用版本控制。
不要用天真的計算方法，例如” 現在我的站群伺服器的記憶體是之前的 2 倍，所以我得把所有數值都改成之前的 2 倍 “。
基本配置
你需要經常察看以下 3 個配置項。不然，可能很快就會出問題。
innodb_buffer_pool_size: 這是你安裝完 InnoDB 後第一個應該設定的選項。緩衝池是資料和索引 WordPress 加速快取的地方：這個值越大越好，這能保證你在大多數的讀取操作時使用的是記憶體而不是硬碟。典型的值是 5-6GB(8GB 記憶體)，20-25GB(32GB 記憶體)，100-120GB(128GB 記憶體) 。
innodb_log_file_size：這 是 redo 日誌的大小。 redo 日誌被用於確保寫操作快速而可靠並且在崩潰時恢復。一直到 MySQL 5.1，它都難於調整，因為一方面你想讓它更大來提高效能，另一方面你想讓它更小來使得崩潰後更快恢復。幸運的是從 MySQL 5.5 之後，崩潰恢復的效能的到了很大提升，這樣你就可以同時擁有較高的寫入效能和崩潰恢復效能了。一直到 MySQL 5.5，redo 日誌的總尺寸被限定在 4GB(預設可以有 2 個 log 檔案) 。這在 MySQL 5.6 裡被提高。
一開始就把 innodb_log_file_size 設定成 512M(這樣有 1GB 的 redo 日誌) 會使你有充裕的寫操作空間。如果你知道你的應用程式需要頻繁的寫入資料並且你使用的時 MySQL 5.6，你可以一開始就把它這是成 4G 。
max_connections: 如果你經常看到 ‘Too many connections’ 錯誤，是因為 max_connections 的值太低了。這非常常見因為應用程式沒有正確的關閉資料庫連線，你需要比預設的 151 連線數更大的值。 max_connection 值被設高了 (例如 1000 或更高) 之後一個主要缺陷是當站群伺服器執行 1000 個或更高的活動事務時會變 的沒有響應。在應用程式裡使用連線池或者在 MySQL 裡使用程式池有助於解決這一問題。
InnoDB 配置
從 MySQL 5.5 版本開始，InnoDB 就是預設的儲存引擎並且它比任何其他儲存引擎的使用都要多得多。那也是為什麼它需要小心配置的原因。
innodb_file_per_table： 這項設定告知 InnoDB 是否需要將所有表的資料和索引存放在共享表空間裡（innodb_file_per_table = OFF）或者為每張表的資料單獨放在一個.ibd 檔案（innodb_file_per_table = ON）。每張表一個檔案允許你在 drop 、 truncate 或者 rebuild 表時回收磁碟空間。這對於一些高階特性也是有必要的，比如資料壓縮。但是它 不會帶來任何效能收益。你不想讓每張表一個檔案的主要場景是：有非常多的表（比如 10k+）。
MySQL 5.6 中，這個屬性預設值是 ON，因此大部分情況下你什麼都不需要做。對於之前的版本你必需在載入資料之前將這個屬性設定為 ON，因為它只對新建立的表有影響。
innodb_flush_log_at_trx_commit： 預設值為 1，表示 InnoDB 完全支援 ACID 特性。當你的主要關注點是資料安全的時候這個值是最合適的，比如在一個主節點上。但是對於磁碟（讀寫）速度 較慢的系統，它會帶來很巨大的開銷，因為每次將改變 flush 到 redo 日誌都需要額外的 fsyncs 。將它的值設定為 2 會導致不太可靠（reliable）因為提交的事務僅僅每秒才 flush 一次到 redo 日誌，但對於一些場景是可以接受的，比如對於主節點的備份節點這個值是可以接受 的。如果值為 0 速度就更快了，但在系統崩潰時可能丟失一些資料：只適用於備份節點。
innodb_flush_method: 這項配置決定了資料和日誌寫入硬碟的方式。一般來說，如果你有硬體 RAID 控制器，並且其獨立 WordPress 加速快取採用 write-back 機制，並有著電池斷電保護，那 麼應該設定配置為 O_DIRECT；否則，大多數情況下應將其設為 fdatasync（預設值）。 sysbench 是一個可以幫助你決定這個選項的好工 具。
innodb_log_buffer_size: 這項配置決定了為尚未執行的事務分配的 WordPress 加速快取。其預設值（1MB）一般來說已經夠用了，但是如果你的事務中包含有二進位制大物件或者大文字欄位的話，這點 WordPress 加速快取 很快就會被填滿並觸發額外的 I/O 操作。看看 Innodb_log_waits 狀態變數，如果它不是 0，增加 innodb_log_buffer_size 。
其他設定
query_cache_size: query cache（查詢 WordPress 加速快取）是一個眾所周知的瓶頸，甚至在併發並不多的時候也是如此。 最佳選項是將其從一開始就停用，設定 query_cache_size = 0（現在 MySQL 5.6 的預設值）並利用其他方法加速查詢：最佳化索引、增加複製分散負載或者啟用額外的 WordPress 加速快取（比如 memcache 或 redis）。如果你已經為你的應用啟 用了 query cache 並且還沒有發現任何問題，query cache 可能對你有用。這是如果你想停用它，那就得小心了。
log_bin：如 果你想讓資料庫站群伺服器充當主節點的備份節點，那麼開啟二進位制日誌是必須的。如果這麼做了之後，還別忘了設定 server_id 為一個唯一的值。就算只有一 個站群伺服器，如果你想做基於時間點的資料恢復，這（開啟二進位制日誌）也是很有用的：從你最近的備份中恢復（全量備份），並應用二進位制日誌中的修改（增量備 份）。二進位制日誌一旦建立就將永久儲存。所以如果你不想讓磁碟空間耗盡，你可以用 PURGE BINARY LOGS
來清除舊檔案，或者設定 expire_logs_days 來指定過多少天日誌將被自動清除。
記錄二進位制日誌不是沒有開銷的，所以如果你在一個非主節點的複製節點上不需要它的話，那麼建議關閉這個選項。
skip_name_resolve：當客戶端連線資料庫站群伺服器時，站群伺服器會進行 WordPress 主機名解析，並且當 DNS 很慢時，建立連線也會很慢。因此建議在啟動站群伺服器時關閉 skip_name_resolve 選項而不進行 DNS 查詢。唯一的侷限是之後 GRANT 語句中只能使用 IP 地址了，因此在新增這項設定到一個已有系統中必須格外小心。
總結
當然還有其他的設定可以起作用，取決於你的負載或硬體：在慢記憶體和快磁碟、高併發和寫密集型負載情況下，你將需要特殊的調整。然而這裡的目標是使得你可 以快速地獲得一個穩健的 MySQL 配置，而不用花費太多時間在調整一些無關緊要的 MySQL 設定或讀檔案找出哪些設定對你來說很重要上。