MySQL查询缓存:最大缓存大小限制为128 MB? [英] MySQL query caching: limited to a maximum cache size of 128 MB?

问题描述

我的应用程序非常占用数据库资源，因此我竭尽全力确保该应用程序和MySQL数据库尽可能高效地协同工作.

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.

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

query_cache_size is the maximum amount of data that may be stored in the cache and query_cache_limit is the maximum size of a single resultset in the cache.

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

My current MySQL query cache is configured as follows:

query_cache_size=128M
query_cache_limit=1M

tuning-primer.sh为我提供了有关正在运行的系统的以下调整提示:

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

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

mysqltuner.pl提供了以下调优提示:

And mysqltuner.pl gives the following tuning hints:

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

Variables to adjust:
    query_cache_size (> 128M)

两个调整脚本都建议我提高query_cache_size.但是，根据mysqltuner.pl，将query_cache size增加到128M以上可能会降低性能(请参见 http://mysqltuner.pl/).

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

您将如何解决这个问题?您是否会在mysqltuner.pl发出警告的情况下增加query_cache_size或尝试以某种方式调整查询逻辑?大多数数据访问是由Hibernate处理的，但应用程序中也使用了很多手工编码的SQL.

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.

推荐答案

通常会在以下情况下发出高速缓存大小过大"警告:假定您的物理内存很少，并且高速缓存本身很需要进行交换，或者会占用OS所需(例如文件缓存).

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).

如果您有足够的内存，则可以安全地增加query_cache size(我见过使用1GB查询缓存进行安装).

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

但是您确定使用的是查询缓存吗?是否有很多 verbatim重复查询?您能发表一个典型查询的例子吗?

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

这篇关于MySQL查询缓存:最大缓存大小限制为128 MB?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！