2011年1月12日 星期三

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客戶端的程序对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用户的承诺。

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使用者的承諾。