MySQL 的調校 (軟硬體、版本、設定)

轉錄自 Gea-Suan Lin's Blog
http://blog.gslin.org/archives/2009/09/13/2088/

把一些關於 MySQL 的資料整理一下。

初期的 MySQL 隨便跑沒關係，備份的部份記得要把 binlog 也一起備份起來，用 gzip 壓過後 (不使用 bzip2 或是高壓縮率參數，是因為考量解壓縮速度；另外推薦用 Parallel gzip 壓縮，速度比較快) 再用 openssl 加密丟到 Amazon S3 上。

成長後，買獨立伺服器要一次買兩台跑 HA，每台分別是：

• CPU 要考量 SQL query 的方式，如果打算在 MySQL 做很多事情 (i.e. JOIN)，CPU 要選高階的；如果大多都是 simple query，則以 C/P 值高的 CPU 優先：兩顆四核心 CPU 算是現在比較划算的硬體。不管哪一種，選低電壓的，像是 Intel Xeon L5408 或是 Intel Xeon L5520，因為硬碟蠻熱的，要減少熱量以免伺服器容易當掉。
• 記憶體愈多愈好，64GB 算是還蠻基本的。
• 硬碟選轉速快的 15krpm SAS，挑大一點的硬碟 (以現在的市場就是 300GB) 省得以後空間不夠要搬動。最好是硬體 RAID1+0，依照應用決定單台 database 要多大，如果預定八顆的話可以買 2U 來塞。

軟體的部份：

• 一定要跑 Linux x86-64 版本，挑大的 distribution 以免遇到問題卻無法解決。我自己還蠻偏好用 Debian。不論是 Debian 還是其他 distribution，儘量跟穩定的 branch，遇到需要升級時的問題會比較少，像是 Debian Lenny。
• 如果要跑 DRBD，先在兩台上面設定好 Heartbeat + DRBD。如果是跑 MMM 的話就設定 MMM，比較需要注意的是 MMM 的版本，參考「MySQL MMM 的情況」。
• Filesystem 跑 XFS，很多人在上面跑很久了，經過時間考驗的 Filesystem，跑起 MySQL InnoDB 的效率還不錯。
• MySQL 跑 Percona 的 5.0 標準版本 (非 highperf 版)，穩定性還不錯。如果預期到資料量很大的時候會是 I/O bound，可以考慮 Percona 的 5.1 版本，並且開啟 InnoDB Plugin 壓縮的功能。
• 跑監控程式，把系統的狀態記錄下來。可以是 Munin、Cacti 或是 nagios，資料對於瓶頸分析很重要。

my.cnf 設定的部份要花不少功夫，除了一般常見的設定外 (這部份網路上很多文件)，有些在站台比較大時會發生的問題要注意：

• back_log 要開大，因為站台大的時候通常不會用 pconnect (每個 web server 都掛著 64 個連線，當有十台 web server 就佔用 640 個連線)，而是用 connect，在每次做完事情就斷線，配合 memcached 降低 MySQL 的需求。不過在量夠大的時候，還是會遇到預設的 back_log 不夠。Smugmug 的 CEO 在「Great things afoot in the MySQL community」有提到吃過這個值的虧。
• max_allowed_packet 設大一點，避免比較大的 INSERT 或是 UPDATE 造成錯誤。通常這是設計上的問題，應該要避免在 MySQL 裡放 blob 資料，不過偶而還是會需要…
• max_connect_errors 設 4294967295，可以避免當 client (像是 php) 發生太多錯誤時被 block 住。
• innodb_adaptive_checkpoint 要打開，可以避免在 flush dirty pages 的時候產生 slow query。MySQL 官方的版本沒有這個參數，而這個參數也是為什麼要用 Percona 版本之一。效果可以參考「Adaptive checkpointing」這篇文章。