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用户的承诺。