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 threads	r/w requests(per sec) Mysql 5.1	r/w requests(per sec) Mysql 5.5
100	4771.2	6515.74
200	5098.48	6514.51
400	4418.92	6757.33
600	4006.65	5299.54
800	3487.41	5845.85
1000	3422.37	5462.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 threads	r/w requests(per sec) Mysql 5.1	r/w requests(per sec) Mysql 5.5
100	14912.81	18714.25
200	13073.62	22722.48
400	14845.57	22618.96
600	13870.14	21697.86
800	12917.89	19754.85
1000	11402.62	19496.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.1	r/w requests(per sec) Mysql 5.5
50	7248.31	6935.99
100	6015.68	7701.45
150	4663.41	6769.95
200	2901.06	6713.56
250	2404.65	7271.79
300	1978.25	6205.68
350	1512.07	6182.49
400	1254.04	5691.1
450	1065.02	3461.29
500	945.09	2944.72
550	888.48	3178.15
600	835.42	2634.09
650	740.39	2221.93
700	709.15	1672.35
750	672.71	1745.81
800	621.9	1594.1
850	614.98	1416.37
900	576.32	1247.26
950	550.02	1120.33
1000	548.06	1035.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.1	r/w requests(per sec) Mysql 5.5
50	15422.6	22717.98
100	15233.24	22312.3
150	15163.68	22243.82
200	15112.43	22242.87
250	13104.72	22237.28
300	9286.72	22251.35
350	7210.98	22298.25
400	5639.18	22199.74
450	2300.57	22281.94
500	1873.45	22220.53
550	1363.05	22203.55
600	1185.78	22051.84
650	962.88	20580.28
700	828.86	16689.95
750	731.07	14566.8
800	702.04	11627.92
850	681.16	9849.85
900	605.71	7945.56
950	588.66	6698.21
1000	580.31	5417.98

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

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

1.3、方案三

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

Number of rows (万)	文件大小 MB (Mysql5.1)	文件大小 MB (Mysql5.5)
10	53	19
50	262	91
100	524	181
500	2618	902
1000	5236	1803

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

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 threads	r/w requests(per sec) Mysql 5.1	r/w requests(per sec) Mysql 5.5
100	731.85	1216.12
200	649.56	1297.62
400	432.34	1147.26
600	407.5	816.75
800	326.05	486.83
1000	297.57	418.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.1	r/w requests(per sec) Mysql 5.5
50	866.41	1485.84
100	818.52	1303.91
150	703.72	1164.28
200	544.29	936.65
250	522.88	866.37
300	455.28	837.77
350	422.07	711.55
400	342.61	617.66
450	336.05	616.33
500	289.81	565.17
550	293.27	548.32
600	320.02	498.74
650	308.91	499.73
700	276.83	479.6
750	286.23	450.11
800	279.21	440.16
850	248.61	413.68
900	259.8	410.63
950	247.67	416.96
1000	231.69	364.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.1	r/w requests(per sec) Mysql 5.5
50	8729.18	11159.77
100	9569.33	9721.62
150	8984.12	9306.6
200	8993.06	9980.92
250	6650.21	8837.1
300	6117.23	8606.5
350	5727.39	8412.57
400	4990.96	7998.11
450	4607.38	7335.22
500	4289.45	7161.11
550	3904.02	6595.44
600	3830.84	6933.05
650	3364.56	6126.11
700	3781.07	5929.7
750	3401.85	5479.37
800	3282.73	5170.23
850	3528.07	6109.62
900	3378.06	4730.44
950	3745.02	4634.87
1000	2724.02	5724.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 表可能会让你有非常坏的体验。