MySQL5.5.8和5.0.77間運算效能的比較

前言
前一陣子和Oracle MySQL大中華區的業務負責人 – Sott Chen討論製做一個MySQL資庫設備(DB appliance)的可行性，希望能做出一套經過最佳化、即插即用(plug & play)的資料庫設備(database appliance)，這套設備在經過驗證的情况下(例如，特定數量之下的同時連線數、資料量和SQL指令類型)可確保能達成一定的反應時間和吞吐量。這樣的設備需要的技術應該有：
1.多核芯的x86硬體
2.經過調校的Linux或Unix作業系統
3.經過調校的MySQL資料庫
為了驗證這個想法的可行性，先從我最容易做的地方開始－證明經過調校的新版MySQL資料庫的效能比一般Linux附帶的MySQL社群版資料庫效能更佳。

測試環境
1.基礎設備，我請Oracle Linux部門的Hans Qing幫忙找到一台HP x86伺服器，並在這台機裝上Oracle Enterprise Linux，這台機器的配備如下：
CPU: 4x Intel(R) Xeon(R) CPU E5520 @ 2.27GHz, 16 Cores
Memory: 12GB
Hard Disk:300GB
OS: Oracle Enterprise Linux 5.5.8 x86_64

測試方法
基本上這是一個壓力測試，我的測試據本是對一個有一億行的table做讀寫兼具的OLTP作業。
我用Sysbench當作測試工具，Sysbench和MySQL都在同一台x86的PC Server上，所以網路的遲滯影響可降到最低。
Sysbench的指令如下：
1.nohup sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000000 --mysql-user=root --mysql-password=welcome1 --mysql-host=127.0.0.1 --mysql-port=3306 prepare
上述指令，Sysbench會在MySQL資料庫建一個sbtest table，並且插入一億行到該table中(詳情請參閱Sysbench的手冊　http://sysbench.sourceforge.net/docs/ ) ，第一個指令－nohup是要Linux在背景執行隨後的sysbench指令，終端機可在執行sysbench時接受下一個的指令。
2.nohup sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000000 --num-threads= --mysql-user=root --mysql-password=welcome1 --mysql-host=127.0.0.1 --mysql-port=3306 run
上述指令是用Sysbench當成MySQL Client的程式對MySQL下達SQL指令，MySQL會將執行結果報的告存於nohup的記錄檔中－nohup.out，我們在執行上述指令時只需更動 的值就可模擬當併行連線數增加時MySQL之負荷狀況及效能的變化

MySQL的調整和設定
由於Innodb為本測試所用的主要storage engine，我的調整主要針對innodb的相關參數做設定，MySQL 5.0.77的參數檔(my.cnf)的內容如下：

max-connections=4000
innodb_buffer_pool_size=6G
key_buffer_size=512M
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb_log_buffer_size=4M

主要的調整有：
innodb_buffer_size設為6G，這台x86 PC Server有12G 的記憶體，因為Sysbench也用同一台機器，設得較為保守，如果整台機器只有MySQL在用應可設為總記憶體的75% (在這台機器上應為8G)。
innodb_flush_method設為O_DIRECT，少了重複的Buffer對DML會有一定的幫助。

MySQL 5.5.8的option file (my.cnf)調整如下：
max-connections=4000
innodb_buffer_pool_size=6G
key_buffer_size=512M
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb_log_buffer_size=4M
innodb_file_format=Barracuda
innodb_change_buffering=all
innodb_thread_concurrency=33
innodb_io_capacity=400
innodb_buffer_pool_instances=3
innodb_fast_shutdown=0
innodb_purge_threads=1

innodb_file_format設為新的格式－Barracuda，如此可完全發揮InnoDB 1.1的效能。
innodb_change_buffering設為all，在更改或刪除資料時，若該table有非主鍵的index，可加速效能。
innodb_thread_concurrency設為33，為核芯數的兩倍再加一。
innodb_io_capacity設為400，以增加在大量更新資料時flush dirty buffer到硬碟的效能。
innodb_buffer_pool_instances設為3，以增加同時做DML的容量。
innodb_fast_shutdown設為0，以開啟多個rollback segment，增加DML的容量和效能。
innodb_purge_threads設為1，以在main thread之外再開一個purge thread，以改善5.1版以前main thread的忙碌程度。

測試結果
本測試由10個threads逐次增加到1020個threads(模擬1020個同時連線)，其結果如下兩圖所示，藍色線為經調校的MySQL 5.5.8的結果，橘線為經調校的MySQL 5.0.77的結果，黃線為未調校(完全用預設的參數) 的MySQL 5.0.77。圖一為每秒交易量，圖二為每秒的讀寫量。這兩種吞吐量的衡量標準的結果基本上是一致的。經調校的5.5.8比經調校的5.0.77效能高7%至60%，5.5.8比未經調校的5.0.77效能高113%到11%。5.5.8最佳吞吐量出現在208個threads，5.0.77則出現在96個threads。

圖一 - 每秒的交易量

另外，未經調校的MySQL 5.0.77的效能在threads數超過368時比經調校的MySQL 5.0.77高，可能的原因為InnoDB Buffer Pool在調校的MySQL設為6GB，而Sysbench的table(sbtest)一個row為250 Bytes，一億個row為25GB，當SQL指令取用範圍近乎均分散在整個table(Sysbench的測試特性)，而且同時連線使用數多達一定數量時，MySQL會有大量的資料自磁碟載入，這個增加的負荷超會過InnoDB Buffer Pool所帶來的記憶體緩存效益。

圖二 - 每秒的讀寫量

進一步改善的可能性
由於時間的限制－這台x86 PC Server的借用期間只有一個月，同時在測時期間又參加在北京的Oracle OpenWorld 和大中華區的MySQL road show等雜事纏身，能用的時間實在不多，没有機會測作業系統層的調整對MySQL效能的影響，Table大小對效能的影響也没測到。
另外還有一些對效能有影響的MySQL參數在這次測試中也没用上，包括：
Innodb_commit_concurrency
InnoDB_log_file_size
InnoDB_flush_logs_at_trx_commit
InnoDB_file_io_threads
Innodb_max_dirty_pages_pct
row_format 設為compress
transaction isolation level的比較
等都值得一試，希望下次能有更寛裕的時間都能測到。

結論
新推出的MySQL5.5在效能、擴充性和可用度等各方面和以前的版本比起來均有長足的進步，尤其對最重要的儲存引擎做了相當多的改良，使得MySQL更能發揮新進的硬體和作業系統在多個processor、大容量記憶體的優勢，DBA們能用的工具也更多了，相對對的也使得MySQL的複雜度有些許的增加。本次的測試驗證了MySQL的進步，和Oracle對MySQL使用者的承諾。