比較全面的MySQL最佳化參考（上篇）

標籤：

轉自：http://imysql.com/2015/05/24/mysql-optimization-reference-1.shtml

本文整理了一些MySQL的通用最佳化方法，做個簡單的總結分享，旨在協助那些沒有專職MySQL DBA的企業做好基本的最佳化工作，至於具體的SQL最佳化，大部分通過加適當的索引即可達到效果，更複雜的就需要具體分析了，可以參考本站的一些最佳化案例或者聯絡我，下方有我的連絡方式。這是上篇。

1、硬體層相關最佳化1.1、CPU相關

在伺服器的BIOS設定中，可調整下面的幾個配置，目的是發揮CPU最大效能，或者避免經典的NUMA問題：

1、選擇Performance Per Watt Optimized(DAPC)模式，發揮CPU最大效能，跑DB這種通常需要高運算量的服務就不要考慮節電了；2、關閉C1E和C States等選項，目的也是為了提升CPU效率；3、Memory Frequency（記憶體頻率）選擇Maximum Performance（最佳效能）；4、記憶體設定菜單中，啟用Node Interleaving，避免NUMA問題；

1.2、磁碟I/O相關

下面幾個是按照IOPS效能提升的幅度排序，對於磁碟I/O可最佳化的一些措施：

1、使用SSD或者PCIe SSD裝置，至少獲得數百倍甚至萬倍的IOPS提升；
2、購置陣列卡同時配備CACHE及BBU模組，可明顯提升IOPS（主要是指機械盤，SSD或PCIe SSD除外。同時需要定期檢查CACHE及BBU模組的健康情況，
   確保意外時不至於遺失資料）；
3、有陣列卡時，設定陣列寫策略為WB，甚至FORCE WB（若有雙電保護，或對資料安全性要求不是特別高的話），嚴禁使用WT策略。並且關閉陣列預讀策略，
   基本上是雞肋，用處不大；
4、儘可能選用RAID-10，而非RAID-5；
5、使用機械盤的話，儘可能選擇高轉速的，例如選用15KRPM，而不是7.2KRPM的盤，不差幾個錢的；

2、系統層相關最佳化2.1、檔案系統層最佳化

在檔案系統層，下面幾個措施可明顯提升IOPS效能：

1、使用deadline/noop這兩種I/O調度器，千萬別用cfq（它不適合跑DB類服務）；
2、使用xfs檔案系統，千萬別用ext3；ext4勉強可用，但業務量很大的話，則一定要用xfs；3、檔案系統mount參數中增加：noatime, nodiratime, nobarrier幾個選項（nobarrier是xfs檔案系統特有的）；

2.2、其他核心參數最佳化

針對關鍵核心參數設定合適的值，目的是為了減少swap的傾向，並且讓記憶體和磁碟I/O不會出現大幅波動，導致瞬間波峰負載：

1、將vm.swappiness設定為5-10左右即可，甚至設定為0（RHEL 7以上則謹慎設定為0，除非你允許OOM kill發生），以降低使用SWAP的機會；
2、將vm.dirty_background_ratio設定為5-10，將vm.dirty_ratio設定為它的兩倍左右，以確保能持續將髒資料重新整理到磁碟，避免瞬間I/O寫，
   產生嚴重等待（和MySQL中的innodb_max_dirty_pages_pct類似）；
3、將net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都設定為1，減少TIME_WAIT，提高TCP效率；
4、至於網傳的read_ahead_kb、nr_requests這兩個參數，我經過測試後，發現對讀寫混合為主的OLTP環境影響並不大（應該是對讀敏感的情境更有效果），
   不過沒準是我測試方法有問題，可自行斟酌是否調整；

 

比較全面的MySQL最佳化參考（上篇）