mysql - 来自 mysqltuner.pl : query_cache_limit 的调优建议

Question

mysqltuner.pl脚本给了我以下建议:

query_cache_limit (> 1M, or use smaller result sets)

MySQL状态输出显示:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 12264      | 
| Qcache_free_memory      | 1001213144 | 
| Qcache_hits             | 3763384    | 
| Qcache_inserts          | 54632419   | 
| Qcache_lowmem_prunes    | 0          | 
| Qcache_not_cached       | 6656246    | 
| Qcache_queries_in_cache | 55280      | 
| Qcache_total_blocks     | 122848     | 
+-------------------------+------------+
8 rows in set (0.00 sec)

从上面的状态输出，如何判断是否需要建议增加query_cache_limit？

Answer 1

您最好的办法是设置某种测试工具，在您的数据库上执行真实的(由您的场景定义)负载，然后使用不同的设置针对 MySql 运行该测试。调优本身就是一门艺术，如果不了解您的确切需求，很难给出一个包罗万象的答案。

来自 http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html :

The Qcache_free_memory counter provides insight into the cache's free memory. Low amounts observed vs. total allocated for the cache may indicate an undersized cache, which can be remedied by altering the global variable query_cache_size.

Qcache_hits and Qcache_inserts shows the number of times a query was serviced from the cache and how many queries have been inserted into the cache. Low ratios of hits to inserts indicate little query reuse or a too-low setting of the query_cache_limit, which serves to govern the RAM devoted to each individual query cache entry. Large query result sets will require larger settings of this variable.

Another indicator of poor query reuse is an increasing Qcache_lowmem_prunes value. This indicates how often MySQL had to remove queries from the cache to make use for incoming statements. Other reasons for an increasing number of Qcache_lowmem_prunes are an undersized cache, which can't hold the needed amount of SQL statements and result sets, and memory fragmentation in the cache which may be alleviated by issuing a FLUSH QUERY CACHE statement. You can remove all queries from the cache with the RESET QUERY CACHE command.

The Qcache_not_cached counter provides insight into the number of statements executed against MySQL that were not cacheable, due to either being a non-SELECT statement or being explicitly barred from entry with a SQL_NO_CACHE hint.

您的命中率与插入率大约为 1:15 或 6%，因此看起来您的设置可以进行一些微调(尽管正如我所说，您是最好的判断者，因为您了解自己的要求最好)。