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

3 則留言:

  1. 感谢你的分享。
    不过我觉得你测的数据集太大了,基本上在测磁盘的性能。MySQL5.5在性能上的提升可能和IO上的时间相比,就体现得不是很明显了。

    回覆刪除
  2. 谢谢您的回应,很好的见解.的确,表的数据量超过innodb buffer pool size,如果行数在二千万以下,测得的结果应该更漂亮.希望有机会也能测数据量的变动对效能的影响,同时收集操作系统层的数据.

    回覆刪除
  3. hello,我是大陆的同行,方便加下我msn啊,互相交流下(okyejr#163.com, #自行替换),谢谢

    回覆刪除