mysql性能测试及不同版本的比较

I. 引言

Oracle 公司发布了MySQL5.5版本,这也是该公司获得MySQL后,对MySQL的第一次升级。Oracle表示，按照内部的标准，在Linux 上，MySQL 5.5的读写速度比5.1版本提升了360%。在Windows Server机器上，这方面性能的提升超过了1500%。MySQL 5.5版本的吞吐量更高。如果真如Oracle所言,MySQL5.5相对于MySQL5.1在性能方面有全面的提升,我公司也会考虑将数据迁移至 MySQL5.5。为了证实MySQL5.5的性能,本人对MySQL5.1与MySQL5.5的常用功能的性能作了一次对比测试,测试根据MySQL引擎类型分两大部分:myisam和innodb。每一引擎的测试内容又分为三部分:
1、当连接数不断增加时,MySQL平均每秒处理事务(读写混合模式和只读模式)的能力。
2、当数据库表的行数不断增加时,MySQL平均每秒处理事务(读写混合模式和只读模式)的能力。
3、同样的数据,MySQL5.1和MySQL5.5文件所占的空间大小(此方案只测试引擎myisam,因为innodb的全部数据都存放在ibdata1中,所以没有办法作比较)。

我选用的测试工具是sysbench。SysBench是一个模块化的、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库 负载情况。从MySQL5.1开始,MySQL自带有测试工具mysqlslap,但我觉得该工具测试的内容有限,而且sysbench的OLTP基准测 试非常方便,所以最后还是选用了sysbench。Ubuntu用户可以apt方式安装（apt-get install sysbench）。sysbench的OLTP测试过程分三个阶段:prepare、run、cleanup。prepare主要是为测试新建所需要的 数据库及相关的表;run就是实际的测试执行过程;cleanup是在测试完毕后,清理测试中所产生的数据库表。具体内容可参考手册 http://sysbench.sourceforge.net/docs/

II. 实验方法

1、第一部分   Myisam

1.1、方案一

当数据库连接数不断增加时(从100~1000,每次递增200),MySQL平均每秒处理事务(读写混合模式和只读模式)的能力。该测试方案中, 表的大小不变,始终是100万行;表的类型是myisam;测试过程是否完成以处理的事务数来计算,即MySQL完成了10万次的事务数(读或写)后,测 试完成。

1.1.1、混合模式

测试OLTP混合模式的命令是:
sysbench –test=oltp –mysql-user=root –mysql-password=mysql51 –mysql-socket=/var/run/mysqld/mysqld.sock –oltp-table-size=1000000 –mysql-db=test –oltp-table-name=sbtest –max-requests=100000 –num-threads=100 –mysql-table-engine=myisam prepare/run/cleanup

命令解释:
–test=oltp                                          测试类型
–mysql-user=root                            mysql用户名
–mysql-password=mysql51           mysql密码
–oltp-table-size=1000000             表的行数
–mysql-db=test                                 数据库名
–oltp-table-name=sbtest　　 　   表名
–max-requests=100000　　　     事务请求数
–num-threads=100　　　　　      线程数
prepare/run/cleanup                         命令需要执行三次,按阶段分别使用prepare、run、cleanup
其它的都比较好理解了

分别测试MySQl5.1和MySQL5.5后,得到的数据如下:

Number of threadsr/w requests(per sec) Mysql 5.1r/w requests(per sec) Mysql 5.51004771.26515.742005098.486514.514004418.926757.336004006.655299.548003487.415845.8510003422.375462.42

根据上面的数据,可得出如下曲线图:

说明:横轴为线程数,纵轴为每秒处理的事务数(读或写)

 1.1.2、只读模式

测试OLTP只读模式的命令是:

sysbench –test=oltp –mysql-user=root –mysql-password=mysql51 –mysql-socket=/var/run/mysqld/mysqld.sock –oltp-table-size=1000000 –mysql-db=test –oltp-table-name=sbtest –max-requests=100000 –num-threads=100 –oltp-read-only=on –mysql-table-engine=myisam　prepare/run/cleanup

分别测试MySQl5.1和MySQL5.5后,得到的数据如下:

Number of threadsr/w requests(per sec) Mysql 5.1r/w requests(per sec) Mysql 5.510014912.8118714.2520013073.6222722.4840014845.5722618.9660013870.1421697.8680012917.8919754.85100011402.6219496.8

根据上面的数据,可得出如下曲线图: 

说明:横轴为线程数,纵轴为每秒处理的事务数(只读)

1.2、方案二

当数据库中表的行数不断增加时(从50万~1000万,每次递增50万行),MySQL平均每秒处理事务(读写混合模式和只读模式)的能力。该测试 方案中,表的连接数始终为100;表的类型是myisam;测试过程是否完成以时间来计算,即测试时间到达５分钟时,测试结束.

1.2.1、混合模式

测试OLTP混合模式的命令是:
sysbench –test=oltp –mysql-user=root –mysql-password=123456 –mysql-socket=/tmp/mysql.sock –mysql-db=test –oltp-table-name=sbtest –max-requests=0 –num-threads=100 –mysql-table-engine=myisam –max-time=300 –myisam-max-rows=50000000 –oltp-table-size=N prepare/run/cleanup

命令解释:
–max-requests=0                           为０时表示无限制
–myisam-max-rows=50000000       默认为1000000,当超过该值时,就需要此选项,这里我指定的是5000万
–oltp-table-size=N　　　　　　　Ｎ即是50万~1000万

分别测试MySQl5.1和MySQL5.5后,得到的数据如下:

Number of rows (万)r/w requests(per sec) Mysql 5.1r/w requests(per sec) Mysql 5.5507248.316935.991006015.687701.451504663.416769.952002901.066713.562502404.657271.793001978.256205.683501512.076182.494001254.045691.14501065.023461.29500945.092944.72550888.483178.15600835.422634.09650740.392221.93700709.151672.35750672.711745.81800621.91594.1850614.981416.37900576.321247.26950550.021120.331000548.061035.12

根据上面的数据,可得出如下曲线图: 

说明:横轴为线程数,纵轴为每秒处理的事务数(读或写)

1.2.2、只读模式

测试OLTP只读模式的命令:
sysbench –test=oltp –mysql-user=root –mysql-password=123456 –mysql-socket=/tmp/mysql.sock –mysql-db=test –oltp-table-name=sbtest –max-requests=0 –num-threads=100 –mysql-table-engine=myisam –max-time=300 –myisam-max-rows=50000000 –oltp-read-only=on –oltp-table-size=N prepare/run/cleanup

分别测试MySQl5.1和MySQL5.5后,得到的数据如下:

Number of rows (万)r/w requests(per sec) Mysql 5.1r/w requests(per sec) Mysql 5.55015422.622717.9810015233.2422312.315015163.6822243.8220015112.4322242.8725013104.7222237.283009286.7222251.353507210.9822298.254005639.1822199.744502300.5722281.945001873.4522220.535501363.0522203.556001185.7822051.84650962.8820580.28700828.8616689.95750731.0714566.8800702.0411627.92850681.169849.85900605.717945.56950588.666698.211000580.315417.98

根据上面的数据,可得出如下曲线图 

说明:横轴为线程数,纵轴为每秒处理的事务数(只读)

1.3、方案三

在MySQL5.1和MySQL5.5中,分别用sysbench新建同样的表,对比表文件.MYD的大小.
分别测试MySQl5.1和MySQL5.5后,得到的数据如下

Number of rows (万)文件大小 MB (Mysql5.1)文件大小 MB (Mysql5.5)10531950262911005241815002618902100052361803

根据上面的数据,可得出如下曲线图:

2、第二部分   Innodb

2.1、方案一

当数据库连接数不断增加时(从100~1000,每次递增200),MySQL平均每秒处理事务(读写混合模式和只读模式)的能力。该测试方案中, 表的大小不变,始终是100万行;表的类型是innodb;测试过程是否完成以处理的事务数来计算,即MySQL完成了2万次的事务数(读或写)后,测试 完成。

2.1.1、混合模式

测试OLTP混合模式的命令是:
sysbench –test=oltp –mysql-user=root –mysql-password=mysql51 –mysql-socket=/var/run/mysqld/mysqld.sock –oltp-table-size=1000000 –mysql-db=test –oltp-table-name=sbtest –max-requests=20000 –num-threads=100 –mysql-table-engine=innodb prepare/run/cleanup

分别测试MySQl5.1和MySQL5.5后,得到的数据如下:

Number of threadsr/w requests(per sec) Mysql 5.1r/w requests(per sec) Mysql 5.5100731.851216.12200649.561297.62400432.341147.26600407.5816.75800326.05486.831000297.57418.05

根据上面的数据,可得出如下曲线图: 

说明:横轴为线程数,纵轴为每秒处理的事务数(读或写)

2.1.2、只读模式

测试OLTP只读模式的命令是:
sysbench –test=oltp –mysql-user=root –mysql-password=mysql51 –mysql-socket=/var/run/mysqld/mysqld.sock –oltp-table-size=1000000 –mysql-db=test –oltp-table-name=sbtest –max-requests=20000 –oltp-read-only=on –num-threads=100 –mysql-table-engine=innodb prepare/run/cleanup

分别测试MySQl5.1和MySQL5.5后,得到的数据如下: 

说明:横轴为线程数,纵轴为每秒处理的事务数(只读)

2.2、方案二

当数据库中表的行数不断增加时(从50万~1000万,每次递增50万行),MySQL平均每秒处理事务(读写混合模式和只读模式)的能力。该测试 方案中,表的连接数始终为100;表的类型是innodb;测试过程是否完成以时间来计算,即测试时间到达５分钟时,测试结束.

2.2.1、混合模式

测试OLTP混合模式的命令是:
sysbench –test=oltp –mysql-user=root –mysql-password=mysql51 –mysql-socket=/var/run/mysqld/mysqld.sock –mysql-db=test –oltp-table-name=sbtest –max-requests=0 –num-threads=100 –mysql-table-engine=innodb –max-time=300 –oltp-table-size=N prepare/run/cleanup

分别测试MySQl5.1和MySQL5.5后,得到的数据如下:

Number of rows (万)r/w requests(per sec) Mysql 5.1r/w requests(per sec) Mysql 5.550866.411485.84100818.521303.91150703.721164.28200544.29936.65250522.88866.37300455.28837.77350422.07711.55400342.61617.66450336.05616.33500289.81565.17550293.27548.32600320.02498.74650308.91499.73700276.83479.6750286.23450.11800279.21440.16850248.61413.68900259.8410.63950247.67416.961000231.69364.98

根据上面的数据,可得出如下曲线图: 

说明:横轴为线程数,纵轴为每秒处理的事务数(读或写)

2.2.2、只读模式

测试OLTP只读模式的命令:
sysbench –test=oltp –mysql-user=root –mysql-password=mysql51 –mysql-socket=/var/run/mysqld/mysqld.sock –mysql-db=test –oltp-table-name=sbtest –max-requests=0 –num-threads=100 –mysql-table-engine=innodb –max-time=300 –oltp-read-only=on –oltp-table-size=N prepare/run/cleanup

分别测试MySQl5.1和MySQL5.5后,得到的数据如下:

Number of rows (万)r/w requests(per sec) Mysql 5.1r/w requests(per sec) Mysql 5.5508729.1811159.771009569.339721.621508984.129306.62008993.069980.922506650.218837.13006117.238606.53505727.398412.574004990.967998.114504607.387335.225004289.457161.115503904.026595.446003830.846933.056503364.566126.117003781.075929.77503401.855479.378003282.735170.238503528.076109.629003378.064730.449503745.024634.8710002724.025724.55

根据上面的数据,可得出如下曲线图: 

说明:横轴为线程数,纵轴为每秒处理的事务数(读或写)

III. 结果

在关于引擎myisam的测试中,当连接数不断增加时,MySQL5.5的读写性能较MySQL5.1提升了25%~70%;当测试数据表的行数逐 渐增加时,MySQL5.5较MySQL5.1的读写性能提升非常明显,在OLTP混合模式中,最高提升了309%,而在OLTP只读模式中,提升了 46.5%~1761%。

在关于引擎innodb的测试中,当连接数不断增加时,OLTP混合模式MySQL5.5读写性能提升了49%~165%,OLTP只读模式提升了0~86.3%;当测试数据表的行数逐渐增加时,MySQL5.5的读写性能提升了57%~80%。

IV. 总结

对于Oracle声称MySQL5.5较MySQL5.1在读写性能上提升了360%,我的测试数据中,只有在引擎myisam的只读模式下,确实 达到了甚至超过了360%;但在其它情况下测试,均没有达到360%的提升率。但根据测试结果可以发现,MySQL5.5较MySQL5.1在读写性能上 确实都有较大提升。可以考虑在进一步熟悉及测试MySQL5.5后逐步升级当前MySQL版本为MySQL5.5。

V. 讨论

浅谈MySQL存储引擎选择 InnoDB还是MyISAM

如果你需要事务处理或是外键，那么InnoDB 可能是比较好的方式。如果你需要全文索引，那么通常来说 MyISAM是好的选择，因为这是系统内建的，然而，我们其实并不会经常地去测试两百万行记录。所以，就算是慢一点，我们可以通过使用Sphinx从 InnoDB中获得全文索引。

数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的 在小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些 事，InnoDB只需要几分钟。

您操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如： COUNT() 在 MyISAM 表中会非常快，而在InnoDB 表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts 语句在MyISAM下会快一些，但是updates 在InnoDB 下会更快一些——尤其在并发量大的时候。

所以，到底你检使用哪一个呢?根据经验来看，如果是一些小型的应用或项目，那么MyISAM 也许会更适合。当然，在大型的环境下使用MyISAM 也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用InnoDB方 式。但需要记住InnoDB 的表需要更多的内存和存储，转换100GB 的MyISAM 表到InnoDB 表可能会让你有非常坏的体验