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。