MySQL 查询缓存：最大缓存大小限制为 128 MB？

MySQL query caching: limited to a maximum cache size of 128 MB?

提问by knorv

My application is very database intensive so I've tried really hard to make sure the application and the MySQL database are working as efficiently as possible together.

我的应用程序是数据库密集型的，所以我非常努力地确保应用程序和 MySQL 数据库尽可能高效地协同工作。

Currently I'm tuning the MySQL query cache to get it in line with the characteristics of queries being run on the server.

目前我正在调整 MySQL 查询缓存以使其符合在服务器上运行的查询的特征。

query_cache_sizeis the maximum amount of data that may be stored in the cache and query_cache_limitis the maximum size of a single resultset in the cache.

query_cache_size是可以存储在缓存query_cache_limit中的最大数据量，是缓存中单个结果集的最大大小。

My current MySQL query cache is configured as follows:

我当前的 MySQL 查询缓存配置如下：

query_cache_size=128M
query_cache_limit=1M

tuning-primer.shgives me the following tuning hints about the running system:

tuning-primer.sh给我以下关于正在运行的系统的调整提示：

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 127 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 99.95 %
Current query_cache_min_res_unit = 4 K
However, 21278 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size

And mysqltuner.plgives the following tuning hints:

并mysqltuner.pl给出以下调优提示：

[OK] Query cache efficiency: 31.3% (39K cached / 125K selects)
[!!] Query cache prunes per day: 2300654

Variables to adjust:
    query_cache_size (> 128M)

Both tuning scripts suggest that I should raise the query_cache_size. However, increasing the query_cache sizeover 128M may reduce performance according to mysqltuner.pl(see http://mysqltuner.pl/).

两个调整脚本都建议我应该提高query_cache_size. 但是，query_cache size根据mysqltuner.pl（参见http://mysqltuner.pl/），增加超过 128M 可能会降低性能。

How would you tackle this problem? Would you increase the query_cache_size despite mysqltuner.pl's warning or try to adjust the querying logic in some way? Most of the data access is handled by Hibernate, but quite a lot of hand-coded SQL is used in the application as well.

你会如何解决这个问题？尽管有mysqltuner.pl警告，您会增加 query_cache_size还是尝试以某种方式调整查询逻辑？大多数数据访问由 Hibernate 处理，但应用程序中也使用了大量手工编码的 SQL。

采纳答案by Quassnoi

Usually "too big cache size" warnings are issued under assumption that you have few physical memory and the cache itself well need to be swapped or will take resources that are required by the OS(like file cache).

通常“缓存大小过大”警告是在假设您的物理内存很少并且缓存本身需要交换或将占用所需资源OS（如文件缓存）的情况下发出的。

If you have enough memory, it's safe to increase query_cache size(I've seen installations with 1GBquery cache).

如果您有足够的内存，增加内存是安全的query_cache size（我见过带有1GB查询缓存的安装）。

But are you sure you are using the query cache right? Do have lots of verbatim repeatingqueries? Could you please post the example of a typical query?

但是您确定您使用的是查询缓存吗？是否有大量逐字重复的查询？您能否发布一个典型查询的示例？

回答by mikn

The warning issued by mysqltuner.py is actually relevant even if your cache has no risk of being swapped. It is well-explained in the following: http://blogs.oracle.com/dlutz/entry/mysql_query_cache_sizing

即使您的缓存没有被交换的风险，mysqltuner.py 发出的警告实际上也是相关的。它在以下内容中有很好的解释：http: //blogs.oracle.com/dlutz/entry/mysql_query_cache_sizing

Basically MySQL spends more time grooming the cache the bigger the cache is and since the cache is very volatile under even moderate write loads (queries gets cleared often), putting it too large will have an adverse effect on your application performance. Tweak the query_cache_sizeand query_cache_limitfor your application, try finding a breaking point where you have most hits per insert, a low number of lowmem_prunesand keep a close eye on your database servers load while doing so too.

基本上 MySQL 花更多的时间来整理缓存，缓存越大，由于缓存在即使中等写入负载下也非常不稳定（查询经常被清除），将它设置得太大会对您的应用程序性能产生不利影响。为您的应用程序调整query_cache_size和query_cache_limit，尝试找到一个断点，在那里您每次插入的点击次数最多，数量很少，lowmem_prunes并在这样做的同时密切关注您的数据库服务器负载。

回答by Nanne

You shouldbe easy on increasing your cache, it is not only a "not that much available mem" thing!

您应该很容易增加缓存，这不仅仅是“没有那么多可用的内存”！

Reading for instance the manualyou get this quote:

例如阅读手册，你会得到这个报价：

Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be.

小心将查询缓存的大小设置得过大，这会增加维护缓存所需的开销，可能超出启用它的好处。几十兆字节的大小通常是有益的。数百兆字节的大小可能不是。

There are variousother sourcesyou can check out!

还有各种其他的来源，你可以看看！

A non-zero prune rate may be an indication that you should increase the size of your query cache. However, keep in mind that the overhead of maintaining the cache is likely to increase with its size, so do this in small increments and monitor the result. If you need to dramatically increase the size of the cache to eliminate prunes, there is a good chance that your workload is not a good match for the query cache.

非零修剪率可能表明您应该增加查询缓存的大小。但是，请记住，维护缓存的开销可能会随着其大小的增加而增加，因此请以小增量执行此操作并监视结果。如果您需要显着增加缓存的大小以消除修剪，则您的工作负载很可能与查询缓存不匹配。

So don't just put as much as you can in that query cache!

所以不要在查询缓存中尽可能多地放入！

The best thing, would be to gradually increase the query cache and measure performance on your site. It's some sort of default in performance questions, but in cases like this 'testing' is one of the best things you can do.

最好的办法是逐渐增加查询缓存并衡量您网站的性能。这是性能问题的某种默认设置，但在这种情况下，“测试”是您可以做的最好的事情之一。

回答by Brian van Rooijen

Be careful with setting the query_cache_sizeand limit to high. MySQL only uses a single thread to read from the query cache.

将query_cache_size和 limit设置为高时要小心。MySQL 仅使用单个线程从查询缓存中读取。

With the query_cache_sizeset to 4G and query_cache_limit12M we had a query cache rate of 85% but noticed a recurring spikes in connections.

随着query_cache_size变量设置为4G和query_cache_limit12M我们有85％的查询缓存率，但注意到，在连接的反复出现尖峰。

After changing the query_cache_sizeto 256M with 64K query_cache_limitthe query cache ratio dropped to 50% but the overall performance increased.

将query_cache_size更改为 256M 和 64K query_cache_limit 后，查询缓存比率下降到 50%，但整体性能有所提高。

回答by Hayden

Overhead for Query cache is around 10% so I would disable query caching. Usually if you can't get your hit rate over 40 or 50 % maybe query cache isn't right for your database.

查询缓存的开销约为 10%，因此我将禁用查询缓存。通常，如果您的命中率无法超过 40% 或 50%，则查询缓存可能不适合您的数据库。

I've blog about this topic... Mysql query_cache_size performance here.

我写过关于这个主题的博客...这里的 Mysql query_cache_size 性能。

回答by PePe

Query Cache gets invalidated/flush every time there is an insert, Use InnoDB/cache and avoid query cache or set it to a very small value.

每次插入时查询缓存都会失效/刷新，使用 InnoDB/缓存并避免查询缓存或将其设置为非常小的值。