MYSQL模拟真实环境下的性能

 

1               模拟真实环境下的性能

再次强调的是，前面的测试数据都是性能测试，不能作为实际环境的性能数据，这些数据表现的应该是理想环境下的极限性能，其只能作为参考。

既然测试用例不完整，我们就考虑模拟一下真实的环境。

1.1         压力测试

先准备检测一下MySQL在压力情况下的性能变化情况，测试的版本为MysQL4.0 gcc静态版本，使用的配置参数和原来相同。

我先在数据库中插入10000000条记录，然后我在另外一台机器机器上启动1组压力进程对数据库进行查询或者修改操作，测试的进程组包括10，50，100个读的进程，10，50，100个读写进程（读写各一半），这些进程的操作间隔时间为10000微秒（0.01秒执行一次）或者1000微妙（0.001秒执行一次）。其中0.01秒执行一次间隔的压力相对较轻，0.001秒执行一次间隔压力就比较重了，一般用mysqladmin processlist检查，大部分进程的状态都是执行状态。

同时在这台机机器（本机上）测试查询1000000条记录（总记录数的1/10）所耗费的时间。同时为了比较，使用vmstat记录相关的CPU占用率的情况。分别记录压力进程运行后，测试进程运行前后的CPU空闲率。

MyISAM 引擎的测试结果如下：

                                                                                                                                                   表1 MyISAM引擎的压力测试

测试项目	压力进程的操作间隔为0.01秒				压力进程的操作间隔为0.001秒
	运行前CPU空闲(%)	运行后CPU空闲(%)	耗时(s)	处理速度(条记录/s)	运行前CPU空闲(%)	运行前CPU空闲(%)	耗时(s)	处理速度(条记录/s)
没有压力进程	100	69	258	3875.97	100	69	258	3875.97
10个读取压力进程	97	69	282	3546.10	85	57	318	4048.58
50个读取压力进程	88	59	316	3164.56	35	21	700	3333.33
100个读取压力进程	73	45	379	2638.52	22	10	2093	2283.11
10个读写压力进程(5读+5写)	94	67	247	3144.65	69	51	361	2770.08
50个读写压力进程(25读+25写)	80	54	300	1428.57	25	16	4206	237.76
100个读写压力进程(50读+50写)	59	37	438	477.78	27	25	大约需要200000	0.50

对于MyISAM引擎，最后一组测试我没能作完，实在是太慢了，在6个小时后我检查发现查询才进行到12477，我几乎崩溃，估计要让他跑完要5-6天，只好作罢。

比较的图表：

 

 

          

                                                                                                                                         图2 MyISAM引擎的压力测试

通过比较，我们可以看出，在只有读取操作的压力下，MyISAM引擎表现还不错，在压力增加的情况下性能没有出现陡降。而对于读写的混合压力测试情况下，MyISAM引擎的表现急转直下，特别是最后的100个读写0.001s间隔压力进程测试情况下，速度已经不可接受。这是检查MySQL的状态计数变量Table_locks_waited数值非常大，而且远远大于Table_locks_immediate。这表示系统操作中，等待锁的操作非常多。

由于MyISAM引擎采用表锁，所以在读写压力测试下，由于写操作锁会阻塞所有的读取锁，而且优先基本大于读取锁，所以此时读取操作必须等待。而且由于最后的压力测试写操作频度很高，所以造成了几乎1秒1条的读取速度。而且值得注意的是此时CPU性能压力并不大，系统的能力并没有得以发挥。

为了横向比较MyISAM引擎和InnoDB引擎在不同压力的表现，我也测试了InnoDB在压力情况下的表现。

                                                                                                                                                   表2 InnoDB引擎的压力测试

测试项目	压力进程的操作间隔为0.01秒				压力进程的操作间隔为0.001秒
	运行前CPU空闲(%)	运行后CPU空闲(%)	耗时(s)	处理速度(条记录/s)	运行前CPU空闲(%)	运行前CPU空闲(%)	耗时(s)	处理速度(条记录/s)
没有压力进程	100	69	278	3597.12	100	69	278	3597.12
10个读取压力进程	97	68	281	3558.72	94	65	387	4504.50
50个读取压力进程	86	58	312	3205.13	76	48	634	3690.04
100个读取压力进程	73	46	326	2583.98	54	28	892	2832.86
10个读写压力进程(5读+5写)	83	55	222	3067.48	72	45	276	3623.19
50个读写压力进程(25读+25写)	40	20	271	1577.29	4	2	1243	804.51
100个读写压力进程(50读+50写)	24	9	353	1121.08	3	1	4011	249.31

比较的图表：

 

                                                                                                                                                   图3 InnoDB引擎的压力测试

可以看出InnoDB引擎在压力测试面前表现得不错，基本上是随着压力的增加，速度呈现线性的下降（注意第2步测试的进程是50个为第一步的5倍）。

为了方便比较，我也给出InnoDB和MySQL的两个比较图表，必须指出的是，这样的比较意义有一定的局限性。因为并不能保证每个连接对于两种引擎的压力都一致。

 

                      

                                                                                                         图4 MyISAM和InnoDB的读取压力测试比较

 

          

                                                                                                                     图5 MyISAM和InnoDB的读写压力测试比较

从对比图表可以看出[注]，InnoDB引擎可以更好的承担查询压力。

MyISAM 为了避免在大压力的环境下性能下降，可能必须将表分割的更细。

 

如果你够心细应该可以发现在加入0.01秒读写压力测试后，得到的性能反而提升了。？而且MyISAM引擎和InnoDB引擎的数据都有提升？这个现象非常有趣。为了验证不是干扰，我重新测试了一次InnoDB在这个情况下的表现，发现测试结果没有错误。我猜测这还是和查询的Cache有关，InnoDB将查询结果放入Cache可能是在提交操作时，而在在提交时发现同时有更改语句的情况下，InnoDB引擎没有将查询语句的结果放入Cache。由于减少了Cache的成本，而且相对而言此时的更改操作压力很轻，所以这样反而加快了查询的速度。

 

1.2         多表访问环境模拟

前面测试的方式都是使用单张表进行测试，在多张表的情况下，MySQL的表现如何？我打算这样模拟，采用10张表，每张表存放5000000条记录，在另外一台机器上，每个进程都查询访问一个表，查询频率为0.005秒一次，查询记录为随机数选取，同时在本机上进行插入，查询，更改，删除1000000条记录的操作。

对于MyISAM引擎，同时由于增加了表的数量，调整了一些相关的参数，

set-variable    = key_buffer_size=512M

set-variable    = query_cache_size=128M

对于InnoDB引擎，仍然使用原来的配置，同时由于测试机器发生了更改[注]，为了对比的公正性，我重新测试了没有压力（没有程序同时访问）情况下的对比数据。

                                                                                                                                                       表3 多表访问的环境模拟

参数配置	插入100000条记录耗时	处理速度(条记录/s)	查询100000条记录耗时	处理速度(条记录/s)	修改100000条记录耗时	处理速度(条记录/s)	删除100000条记录耗时	处理速度(条记录/s)
MyISAM引擎,	197	5076.14	333	3003.00	173	5780.35	175	5714.29
MyISAM引擎,模拟多表环境	209	4784.69	3366	297.09	566	1766.78	507	1972.39
InnoDB引擎,	221	4524.89	425	2352.94	255	3921.57	234	4273.50
InnoDB引擎, 模拟多表环境	520	1923.08	1853	539.67	677	1477.10	629	1589.83

对比图表为

 

                                                                                                                                                       图6 模拟多表访问的测试

 

在非模拟情况下，MyISAM 引擎有少量优势，但是在模拟环境下查询记录情况,MyISAM引擎的表现远慢于InnoDB的表现，对于这个我感觉有点奇怪，因为此时不应该有锁排队的情况发生。？

在压力情况下，MyISAM引擎的插入语句性能看似不错，但是这是由于在我的测试用例中，MyISAM引擎不用加入写锁锁定表。（见前InnoDB和MyISAM引擎的对比测试章节）

由于所有的操作都是顺序操作，而且没有加入写操作，感觉测试比较倾向于MyISAM引擎[注]。

 

测试的机器也是为380G3，但是内核为2.4.21，初步看来2.6内核的性能更好一些。

对这个模拟感觉不是太理想，模拟正式环境和测试极限速度这两者可能就有矛盾。

 

1.3         网络访问速度

前面的测试基本都是基于本机操作访问数据库的操作，但是更多的应用是基于网络访问。所以也业考虑测试一下网络环境下MySQL访问速度。

基于网络的访问分为2种，一种是利用长链接，一种是短链接，就是每次访问都重新进行一次链接。

测试环境，数据库使用MyISAM引擎，使用Cache。网络带宽为100Mbps。

由于对于1个服务器，可以循环使用的SOCKET端口是有限，所以我只执行了20000次操作进行比较，由于测试的数据较少，对于这些数据比较差距就可以了。

比较项目	本地操作		网络操作 使用长链接		网络操作 使用短链接		网络操作(STMT) 使用长链接
	耗时(s)	处理速度（条/s）	耗时(s)	处理速度（条/s）	耗时(s)	处理速度（条/s）	耗时(s)	处理速度（条/s）
插入20000条记录	4	5000.00	13	1538.46	27	740.74	8	2500.00
查询20000条记录	4	5000.00	21	952.38	34	588.24	15	1333.33
改写20000条记录	4	5000.00	13	1538.46	29	689.66	8	2500.00
删除20000条记录	3	6666.67	13	1538.46	36	555.56	7	2857.14

比较图表如下：

 

                        

                                                                                                                            图7 网络环境性能下的测试

对于网络的访问，网络长链接的访问速度是本地执行速度的3-4倍（降低了60%），网络短链接的访问速度是本地执行速度的7-8倍（降低了85%）。[注]

注意这个速度只是单链接下的极限速度，所以这并不代表MySQL在网络环境下的处理速度（但个人认为极限速度不可能超过2倍），但是我们还是看出，MySQL在网络环境下速度有相当的下降[注]。而使用网络短链接要付出连接和断开连接的成本。

STMT的操作方式（参看STMT的章节）由于不用重新传递分析SQL，在网络环境也表现了出了优于其他方式的性能。

 

由于测试的数据较少，可能影响到测试的时间准确程度。

对于长链接的网络访问速度有如此的下降，我开始很怀疑，我查询了我原来对Oracle作过的一组入库测试数据，在同样的100M网络环境环境下速度只降低了30%左右。但我重新测试了1000000条的数据，但得到的结果还是如此。（当然也可能是IDC内部的网络过于繁忙）

基于MySQL在网络环境中的表现差强人意，如果硬件条件允许，在作DB服务器设计时，将DB服务器和MySQL放在同一个机器下可能是一个更好的选择。

1.4         小结

如何模拟真实的环境一直是我比较头大的问题，不管我如何模拟，好像感觉仍然只能专注到某个方面从而有相对性，而且模拟现实和测试极限性能本身可能就相互矛盾。这些测试数据更多的应该是一种参考，使你了解系统地性能极限和性能瓶颈[注]。如果你能有更好的方法模拟真实环境，请通知我。

 

通过前面的测试，个人感觉对于类似我测试环境的系统（机器+DB+表），个人感觉真实环境的最高本地处理速度可以接近2500条左右（包括读写），网络操作的最高速度可以接近1500条。