关于MySQL Query Cache的一些交流心得

最新推荐文章于 2022-01-09 13:21:11 发布

iteye_21202

最新推荐文章于 2022-01-09 13:21:11 发布

阅读量97

点赞数

文章标签：数据库前端 ViewUI

今天线上MySQL 出现内存使用率报警，就去查了下mysql内存使用的参数，重点是缓存，关于innodb_buffer_pool_size以及query cache的使用。

query_cache_type 默认是打开的，而且缓存区query_cache_size默认大小是32M，通常建议不超过256M大小，可以用过查询cache参数来看具体值：

mysql> show variables like '%cache%';
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| binlog_stmt_cache_size       | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| max_binlog_stmt_cache_size   | 18446744073709547520 |
| metadata_locks_cache_size    | 1024                 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 33554432             |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| stored_program_cache         | 256                  |
| table_definition_cache       | 400                  |
| table_open_cache             | 512                  |
| thread_cache_size            | 8                    |
+------------------------------+----------------------+
18 rows in set (0.00 sec)


mysql>

“Qcache_free_blocks”：QueryCache中目前还有多少剩余的blocks。如果该值显示较大，
则说明QueryCache中的内存碎片较多了，可能需要寻找合适的机会进行整理（）。
●“Qcache_free_memory”：QueryCache中目前剩余的内存大小。通过这个参数我们可以较为准
确的观察出当前系统中的QueryCache内存大小是否足够，是需要增加还是过多了；
●“Qcache_hits”：多少次命中。通过这个参数我们可以查看到QueryCache的基本效果；
●“Qcache_inserts”：多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两
个参数我们就可以算出QueryCache的命中率了：
QueryCache命中率=Qcache_hits/(Qcache_hits+Qcache_inserts)；
●“Qcache_lowmem_prunes”：多少条Query因为内存不足而被清除出QueryCache。通过
“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系
统中QueryCache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换
出
●“Qcache_not_cached”：因为query_cache_type的设置或者不能被cache的Query的数量；
●“Qcache_queries_in_cache”：当前QueryCache中cache的Query数量；
●“Qcache_total_blocks”：当前QueryCache中的block数量；

QueryCache的限制
QueryCache由于存放的都是逻辑结构的ResultSet，而不是物理的数据页，所以在性能提升的同
时，也会受到一些特定的限制。
a)5.1.17之前的版本不能Cache帮定变量的Query，但是从5.1.17版本开始，QueryCache已经
开始支持帮定变量的Query了；
b)所有子查询中的外部查询SQL不能被Cache；
c)在Procedure，Function以及Trigger中的Query不能被Cache；
d)包含其他很多每次执行可能得到不一样结果的函数的Query不能被Cache。
鉴于上面的这些限制，在使用QueryCache的过程中，建议通过精确设置的方式来使用，仅仅让合
适的表的数据可以进入QueryCache，仅仅让某些Query的查询结果被Cache。

另外，如果Qcache_free_blocks值有点偏高,可以用flushquerycache来清理下。

一个朋友的建议：

第一个：读操作多的话看看比例，简单来说，如果是用户清单表，或者说是数据比例比较固定，比如说商品列表，是可以打开的，前提是这些库比较集中，数据库中的实务比较小。

第二个：我们“行骗”的时候，比如说我们竞标的时候压测，把query cache打开，还是能收到qps激增的效果，当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多，访问量并不多，那么就不要打开，例如社交网站的，10%的人产生内容，其余的90%都在消费，打开还是效果很好的，但是你如果是qq消息，或者聊天，那就很要命。

第三个：小网站或者没有高并发的无所谓，高并发下，会看到很多 qcache 锁等待，所以一般高并发下，不建议打开query cache