MySQL的查询Cache测试

 

3         MySQL的查询Cache测试

我曾经对MySQL的Cache作了非常大的期望。因为提高数据库的性能的最好方法就是使用大规模的Cache，但是通过查询相关资料我了解到MySQL的查询Cache有很多的限制。

3.1         Without Cache测试

可以看到,MySQL从4.0后查询速度有所下降,所以我们也测试了一下不使用Cache的数据。我测试了4.0gcc编译版本和4.1gcc编译版本在在使用Cache和不使用Cache的情况下的速度。

关闭查询Cache的方法为调整参数 query_cache_size为0 或者设置 query_cache_type为0 [注]。

                                                                                                                                   表6 是否使用cache的环境下的测试对比

数据库	4.0gcc with cache 耗时（s）	4.0gcc without cache 耗时（s）	4.1gcc with cache 耗时（s）	4.1gcc without cache 耗时（s）
插入1000000条记录	169	169	176	178
查询1000000条记录	282	200	306	219
改写1000000条记录	165	164	174	172
删除1000000条记录	172	171	179	181
插入5000000条记录	876	866	898	905
查询5000000条记录	1472	995	1595	1096
改写5000000条记录	816	832	859	875
删除5000000条记录	958	875	976	942
插入10000000条记录	1758	1743	1817	1878
查询10000000条记录	3001	2034	3257	2215
改写10000000条记录	1844	1744	1904	1862
删除10000000条记录	1908	1892	1982	2031

                                                                                                                                   表7 是否使用cache的环境下的测试对比

数据库	4.0gcc with cache 处理速度(条/s)	4.0gcc without cache 处理速度(条/s)	4.1gcc with cache 处理速度(条/s)	4.1gcc without cache 处理速度(条/s)
插入1000000条记录	5917.16	5917.16	5681.82	5617.98
查询1000000条记录	3546.10	5000.00	3267.97	4566.21
改写1000000条记录	6060.61	6097.56	5747.13	5813.95
删除1000000条记录	5813.95	5847.95	5586.59	5524.86
插入5000000条记录	5707.76	5773.67	5567.93	5524.86
查询5000000条记录	3396.74	5025.13	3134.80	4562.04
改写5000000条记录	6127.45	6009.62	5820.72	5714.29
删除5000000条记录	5219.21	5714.29	5122.95	5307.86
插入10000000条记录	5688.28	5737.23	5503.58	5324.81
查询10000000条记录	3332.22	4916.42	3070.31	4514.67
改写10000000条记录	5422.99	5733.94	5252.10	5370.57
删除10000000条记录	5241.09	5285.41	5045.41	4923.68

 

                                                                                                                                              图3 是否使用Cache的性能比较

这个测试可以证明，使用Cache时，同样是有成本的，特别是对查询语句，成本还比较高,单条查询SQL的使用Cache成本大约是比不是使用Cache的成本高40%，这个成本应该来自MySQL要消耗将结果保存到Cache和淘汰出Cache的时间。[注]

 

这儿说的是成本，不是性能比较。不过还是怀疑MySQL的Cache设计有问题。

MySQL有一个配置参数query_cache_type 表示查询的Cache类型。0表示 OFF，不进行缓冲，1 表示ON，进行缓冲，2表示 DEMAND，只对SELECT SQL_CACHE开头的查询进行缓冲。

3.2         查询时使用Cache

如何让查询时能使用上Cache，我考虑这样进行测试，查询1条记录后，再查询4次。同时不进行任何操作，保证后面的查询命中Cache。测试的数量为1000000条。

测试的结果如下：

                                                                                                                                   表8 查询1000000条记录5次的消耗时间

数据库	查询1000000条记录5次耗时	平均
MySQL3.23	1082	4621.07
MySQL 4.0 gcc	892	5605.38
MySQL 4.1 gcc	855	5847.95
MySQL 4.1 gcc(不使用查询Cache)	1410	3546.10
MySQL 4.1 icc	858	5827.51
MySQL 4.1 icc(不使用查询Cache)	1530	3267.97
MySQL 5.0	942	5307.86

 

 

                                                                                                                                      图4 是否使用cache对查询效率的影响

可以看到MySQL的Cache在这个理想的测试环境下确实提高了。但是这些提高也是要付出代价的。

3.3         查询Cache的代价

正当我对上面的结果欢心鼓舞是，而查询的资料发现给自己浇了些冷水，上面的测试非常理想，MySQL的Cache使用有很多限制。

首先我们从Without Cache组测试数据可以看到，使用Cache后进行查询是有成本的。

在Cache中的MySQL查询结果是和查询SQL对应的，如果你想命中Cache，两次使用的查询语句必须完全一样。所以你最好使用同1个人编写的API查询数据库。

另外，MySQL的查询Cache淘汰策略近乎弱智，对于MyISAM引擎，对数据表的任何一次都会淘汰所有相关此表在Cache中的数据[注]，而InnoDB的引擎会在任何一次提交后淘汰所有的和此次交易相关表在Cache中的数据。所以对于一个频繁进行操作的数据库，MySQL的查询Cache的命中率肯定不会太高，而且查询同时要增加和Cache相关的成本，要消耗有入Cache和淘汰出Cache的时间。

所以你在使用MySQL Cache时最好仔细考虑一下。如果数据库的数据表基本是静态数据。可以使用这个Cache。如果是动态数据，而且改写操作频繁，是否值得用这个Cache很值得怀疑。[注]

 

更加理想的淘汰方式当然是和键相关。这正是c4a 和其他很多大型数据库Cache的设计思想。

从个人观察得数据来看，真实环境的查询Cache的命中率大约在3%左右，我观察得最好的情况某产品的DB命中率为30%，但是我怀疑其数据库中有一段时间不更改的静态表（混合静态数据和动态数据这不是一个良好的数据库设计风格）。即使有30%的命中率，考虑到前面所提到的查询成本，还是得不偿失，所以我个人建议在大规模更新处理的数据库上，关闭查询Cache。