我应该牺牲我的innodb_buufer_pool _size/RAM来为query_cache_size腾出空间吗? [英] Should I sacrifice my innodb_buufer_pool _size/RAM to make space for query_cache_size ?
问题描述
我有一个16GB的专用Mysql服务器数据库.我的innodb_buffer_pool_size设置为11GB左右，我正在系统中实现查询缓存，大小为80mb.我应该从哪里开始这个空间，innodb_buffer_pool_size或RAM?
I have a 16GB dedicated Mysql server database.My innodb_buffer_pool_size is set to around 11GB ,i am implementing query cache in my system ,which has a size of 80mb. From where should i make this space ,innodb_buffer_pool_size or RAM ?
推荐答案
Back in Jun 2014 I answered https://dba.stackexchange.com/questions/66774/why-query-cache-type-is-disabled-by-default-start-from-mysql-5-6/66796#66796
在那篇文章中，我讨论了InnoDB微观管理如何在InnoDB缓冲池和查询缓存之间发生变化.
In that post, I discussed how InnoDB micromanages changes between the InnoDB Buffer Pool and the Query Cache.
最简单的答案是仅禁用查询缓存，但是有一个陷阱:您必须同时设置
The simplest answer would be to just disable the query cache, but there is a catch: You must set both query_cache_size and query_cache_type to zero(0).
如果您未设置 query_cache_type 设置为零(0)，则仍会进行更改的微观管理.这可以通过MySQL文档的第8段进行验证. 查询缓存配置" 说
If you don't set query_cache_type to zero(0), the micromanagement of changes still occurs. This is verified by what the Paragraph 8 of the MySQL Documentation "Query Cache Configuration" says
如果query_cache_size为0，则还应该将query_cache_type变量设置为0.在这种情况下，服务器根本不获取查询缓存互斥量，这意味着无法在运行时启用查询缓存，并且减少了运行时的开销.查询执行.
If query_cache_size is 0, you should also set query_cache_type variable to 0. In this case, the server does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.
使用查询缓存
如果您确实希望使用查询缓存，则需要研究数据.基本上，您需要知道结果的大小.然后，您可以设置 query_cache_limit 和 query_cache_min_res_unit 以容纳平均大小结果集以及最大尺寸结果集.
USING THE QUERY CACHE
If you really wish to use the Query Cache, then you need to study your data. Basically, you need to know the size of your results. You would then set query_cache_limit and query_cache_min_res_unit to accommodate the average size result set as well as the maximum size result set.
As an alternative, you can enable the Query Cache globally but disable its use from your DB Session as stated in Paragraph 9:
设置GLOBAL query_cache_type值可确定进行更改后连接的所有客户端的查询缓存行为.各个客户端可以通过设置SESSION query_cache_type值来控制其自己的连接的缓存行为.例如，客户端可以禁止对自己的查询使用查询缓存，如下所示:
Setting the GLOBAL query_cache_type value determines query cache behavior for all clients that connect after the change is made. Individual clients can control cache behavior for their own connection by setting the SESSION query_cache_type value. For example, a client can disable use of the query cache for its own queries like this:
mysql> SET SESSION query_cache_type = OFF;
您的实际问题
YOUR ACTUAL QUESTION
The Query Cache, InnoDB Buffer Pool, Per Connection Threads (See my post https://dba.stackexchange.com/questions/16969/how-costly-is-opening-and-closing-of-a-db-connection/16973#16973), and OS all compete for RAM.
仅查看查询缓存和InnoDB缓冲池时，您需要查看MySQL运行数天或数周后InnoDB缓冲池中有多少可用空间.
When looking at just the Query Cache and the InnoDB Buffer Pool, you need to see how much space is free in the InnoDB Buffer Pool after MySQL has been running for days or weeks.
您可以运行此查询
SELECT variable_value / 64 free_mb
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_free';
这将告诉您可以降低InnoDB缓冲池多少.然后，您可以按该大小增加查询缓存.如果此查询返回的结果少于1024，则可能不值得付出努力.您可以从RAM中获得额外的空间，也可以仅禁用查询缓存.
This will tell you how much you can lower the InnoDB Buffer Pool. You could then increase the Query Cache by that size. If this query returns less that 1024, it is probably not worth the effort. You will either have get the additional space from RAM or just go with disabling the Query Cache.
这篇关于我应该牺牲我的innodb_buufer_pool _size/RAM来为query_cache_size腾出空间吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
