禁用mysql query cache
时间:2016-05-14 02:27 来源:linux.it.net.cn 作者:IT
当你的数据库打开了Query Cache（简称QC）功能后，数据库在执行SELECT语句时，会将其结果放到QC中，当下一次处理同样的SELECT请求时，数据库就会从QC取得结果，而不需要去数据表中查询。
在这个“Cache为王”的时代，我们总是通过不同的方式去缓存我们的结果从而提高响应效率，但一个缓存机制是否有效，效果如何，却是一个需要好好 思考的问题。在MySQL中的Query Cache就是一个适用较少情况的缓存机制。在上图中，如果缓存命中率非常高的话，有测试表明在极端情况下可以提高效率238%。但实际情况如 何？Query Cache有如下规则，如果数据表被更改，那么和这个数据表相关的全部Cache全部都会无效，并删除之。这里“数据表更改”包括: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。举个例子，如果数据表posts访问频繁，那么意味着它的很多数据会被QC缓存起来，但是每一次posts数据表的更新，无论更新是 不是影响到了cache的数据，都会将全部和posts表相关的cache清除。如果你的数据表更新频繁的话，那么Query Cache将会成为系统的负担。有实验表明，糟糕时，QC会降低系统13%的处理能力。
如果你的应用对数据库的更新很少，那么QC将会作用显著。比较典型的如博客系统，一般博客更新相对较慢，数据表相对稳定不变，这时候QC的作用会比较明显。
不过，可能有的人人为只需要把 query_cache_size 大小调整为 0 就可以了，可以忽略 query_cache_type 参数的值，反正它也是可以在线调整的。
事实果真如此吗？让我们来实际模拟测试下就知道了。
我们模拟了以下几种场景：
1、初始化时，同时设置 query_cache_size 和 query_cache_type 的值为 0；
2、初始化时，设置 query_cache_size = 0，但设置 query_cache_type = 1；
3、初始化时，设置 query_cache_size = 0，query_cache_type = 1，但是启动后立刻 修改 query_cache_type = 0
4、初始化时，设置 query_cache_size = 0，query_cache_type = 0，但是启动后立刻 修改 query_cache_type = 1
5、初始化时，设置 query_cache_size = xMB，query_cache_type = 1，但是启动后立刻 修改 query_cache_type = 0
经过测试，可以得到下面几个重要结论（详细测试过程请见最后）：
1、想要彻底关闭query cache，务必在一开始就设置 query_cache_type = 0，即便是启动后将 query_cache_type 从 1 改成 0，也不行；
2、即便query_cache_size = 0，但 query_cache_type 非 0 的话，在实际环境中，可能会频繁发生 Waiting for query cache lock；
3、一开始就设置 query_cache_type = 0 的话，没有办法在运行 过程中再次动态启用，反过来则可以。也就是说，一开始是启用 query cache 的， 在运行过程中将其关闭，但事实上仍然会发生 Waiting for query cache lock，并没有真正的关闭；
详细测试过程：
一、测试方法
采用sysbench模拟并发oltp请求：
sysbench --test=tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on --num-threads=64 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=1800 --max-requests=0 run
二、具体几种测试模式
1、一直关闭QC（query cache的简写，下同），即 query_cache_size = 0， query_cache_type = 0
测试过程中，一直都没有和query cache lock相关的状态出现，结果tps：2295.34
2、启用QC，但QC size 设置为 0，即：query_cache_size = 0，query_cache_type = 1
测试过程中，一直有 Waiting for query cache lock 状态出现，结果tps：2272.52
3、启用QC，但QC size为0，但启动时立刻关闭QC，即初始化时 query_cache_size = 0，query_cache_type = 1，启动后立刻修改 query_cache_type = 0
测试过程中，也一直有 Waiting for query cache lock 状态出现，结果tps：2311.54
4、关闭QC，但启动后立刻启用QC，即初始化时 query_cache_size = 0，query_cache_type = 0，启动后立刻修改 query_cache_type = 1
这时，会提示报错信息：
失败：ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it
http://linux.it.net.cn
也就是说，如果一开始就关闭 QC 的话，是没办法在运行过程中动态再启用QC的。
5、启用QC，并设置QC size为256M，即 query_cache_size = 256M，query_cache_type = 1
这种情况下，在测试过程中一直有 Waiting for query cache lock 状态出现，并且结果tps也很差，只有 1395.39（几个案例中最差的一种）
6、启用QC，设置QC size为256M，但启动后立刻关闭QC，即 query_cache_size = 256M，query_cache_type = 1，启动后立刻修改 query_cache_type = 0
这种情况下，在测试过程中也一直有 Waiting for query cache lock 状态出现，结果tps：2295.79（在这个模式下，如果设置 query_cache_type = 2，效果也不佳）
第三种模式下，虽然看起来tps还不错，但毕竟上面只是简单模拟测试，实际情况下如果有频繁的query cache lock的话，tps肯定不会太好看。
因此，想要获得较高tps的话，最好还是一开始就关闭QC，不要心存侥幸或者固守陈规。总体而言，QC不建议使用，鸡肋功能"夫鸡肋，弃之如可惜，食之无所得"，导致几十上百倍的性能差异。如果确实有这个缓存需求，应用允许的情况下，可用效率高的Redis或者MC等替代。
Query Cache相关参数：
query_cache_size QC占用空间大小，通过将其设置为0关闭QC功能
query_cache_type 0表示关闭QC；1表示正常缓存；2表示SQL_CACHE才缓存
query_cache_limit 最大缓存结果集
query_cache_min_res_unit 手册上说，QC会按照这个值分配缓存block的大小。
Qcache_lowmem_prunes 这是一个状态变量(show status)，当缓存空间不够需要释放旧的缓存时，该值会自增。
(责任编辑：IT)
| 当你的数据库打开了Query Cache（简称QC）功能后，数据库在执行SELECT语句时，会将其结果放到QC中，当下一次处理同样的SELECT请求时，数据库就会从QC取得结果，而不需要去数据表中查询。 在这个“Cache为王”的时代，我们总是通过不同的方式去缓存我们的结果从而提高响应效率，但一个缓存机制是否有效，效果如何，却是一个需要好好 思考的问题。在MySQL中的Query Cache就是一个适用较少情况的缓存机制。在上图中，如果缓存命中率非常高的话，有测试表明在极端情况下可以提高效率238%。但实际情况如 何？Query Cache有如下规则，如果数据表被更改，那么和这个数据表相关的全部Cache全部都会无效，并删除之。这里“数据表更改”包括: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。举个例子，如果数据表posts访问频繁，那么意味着它的很多数据会被QC缓存起来，但是每一次posts数据表的更新，无论更新是 不是影响到了cache的数据，都会将全部和posts表相关的cache清除。如果你的数据表更新频繁的话，那么Query Cache将会成为系统的负担。有实验表明，糟糕时，QC会降低系统13%的处理能力。 如果你的应用对数据库的更新很少，那么QC将会作用显著。比较典型的如博客系统，一般博客更新相对较慢，数据表相对稳定不变，这时候QC的作用会比较明显。 不过，可能有的人人为只需要把 query_cache_size 大小调整为 0 就可以了，可以忽略 query_cache_type 参数的值，反正它也是可以在线调整的。 事实果真如此吗？让我们来实际模拟测试下就知道了。 我们模拟了以下几种场景： 1、初始化时，同时设置 query_cache_size 和 query_cache_type 的值为 0； 2、初始化时，设置 query_cache_size = 0，但设置 query_cache_type = 1； 3、初始化时，设置 query_cache_size = 0，query_cache_type = 1，但是启动后立刻 修改 query_cache_type = 0 4、初始化时，设置 query_cache_size = 0，query_cache_type = 0，但是启动后立刻 修改 query_cache_type = 1 5、初始化时，设置 query_cache_size = xMB，query_cache_type = 1，但是启动后立刻 修改 query_cache_type = 0 经过测试，可以得到下面几个重要结论（详细测试过程请见最后）： 1、想要彻底关闭query cache，务必在一开始就设置 query_cache_type = 0，即便是启动后将 query_cache_type 从 1 改成 0，也不行； 2、即便query_cache_size = 0，但 query_cache_type 非 0 的话，在实际环境中，可能会频繁发生 Waiting for query cache lock； 3、一开始就设置 query_cache_type = 0 的话，没有办法在运行 过程中再次动态启用，反过来则可以。也就是说，一开始是启用 query cache 的， 在运行过程中将其关闭，但事实上仍然会发生 Waiting for query cache lock，并没有真正的关闭； 详细测试过程： 一、测试方法 采用sysbench模拟并发oltp请求： sysbench --test=tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on --num-threads=64 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=1800 --max-requests=0 run 二、具体几种测试模式 1、一直关闭QC（query cache的简写，下同），即 query_cache_size = 0， query_cache_type = 0 测试过程中，一直都没有和query cache lock相关的状态出现，结果tps：2295.34 2、启用QC，但QC size 设置为 0，即：query_cache_size = 0，query_cache_type = 1 测试过程中，一直有 Waiting for query cache lock 状态出现，结果tps：2272.52 3、启用QC，但QC size为0，但启动时立刻关闭QC，即初始化时 query_cache_size = 0，query_cache_type = 1，启动后立刻修改 query_cache_type = 0 测试过程中，也一直有 Waiting for query cache lock 状态出现，结果tps：2311.54 4、关闭QC，但启动后立刻启用QC，即初始化时 query_cache_size = 0，query_cache_type = 0，启动后立刻修改 query_cache_type = 1 这时，会提示报错信息： 失败：ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it http://linux.it.net.cn 也就是说，如果一开始就关闭 QC 的话，是没办法在运行过程中动态再启用QC的。 5、启用QC，并设置QC size为256M，即 query_cache_size = 256M，query_cache_type = 1 这种情况下，在测试过程中一直有 Waiting for query cache lock 状态出现，并且结果tps也很差，只有 1395.39（几个案例中最差的一种） 6、启用QC，设置QC size为256M，但启动后立刻关闭QC，即 query_cache_size = 256M，query_cache_type = 1，启动后立刻修改 query_cache_type = 0 这种情况下，在测试过程中也一直有 Waiting for query cache lock 状态出现，结果tps：2295.79（在这个模式下，如果设置 query_cache_type = 2，效果也不佳） 第三种模式下，虽然看起来tps还不错，但毕竟上面只是简单模拟测试，实际情况下如果有频繁的query cache lock的话，tps肯定不会太好看。 因此，想要获得较高tps的话，最好还是一开始就关闭QC，不要心存侥幸或者固守陈规。总体而言，QC不建议使用，鸡肋功能"夫鸡肋，弃之如可惜，食之无所得"，导致几十上百倍的性能差异。如果确实有这个缓存需求，应用允许的情况下，可用效率高的Redis或者MC等替代。 Query Cache相关参数： query_cache_size QC占用空间大小，通过将其设置为0关闭QC功能 query_cache_type 0表示关闭QC；1表示正常缓存；2表示SQL_CACHE才缓存 query_cache_limit 最大缓存结果集 query_cache_min_res_unit 手册上说，QC会按照这个值分配缓存block的大小。 Qcache_lowmem_prunes 这是一个状态变量(show status)，当缓存空间不够需要释放旧的缓存时，该值会自增。 (责任编辑：IT) |