The query_cache_size
looks like the kind of setting that one would usually want enabled which puzzled me since it defaults to 0
. Then I read the following about the query_cache_wlock_invalidate
setting from the MySQL docs
Normally, when one client acquires a WRITE lock on a MyISAM table,
other clients are not blocked from issuing statements that read from
the table if the query results are present in the query cache. Setting
this variable to 1 causes acquisition of a WRITE lock for a table to
invalidate any queries in the query cache that refer to the table.
This forces other clients that attempt to access the table to wait
while the lock is in effect.
It makes no mention about the InnoDB engine. Does this setting also prevent reads from cache when Innodb has a write lock on the row/table?
Best Answer
I am glad you asked about InnoDB and the Query Cache.
IMHO They should never come up in the same sentence or the same conversation. Please forgive the my use of them in the first sentence.
All joking aside, I have addressed in the DBA StackExchange many times
Jun 11, 2014
: Increased query_cache_size, Queries slowed drastically on increased trafficSep 26, 2013
: query cache hit value is not changing in my databaseSep 05, 2012
: Is the overhead of frequent query cache invalidation ever worth it?Here is my
Jun 11, 2014
post as to why it should be disabled (with exceptions)According to the MySQL Documentation on Query Cache States
This is definitely a huge problem with InnoDB tables because InnoDB mechanisms make inroads on the query cache. I wrote about this phenomenon before
Sep 05, 2012
: https://dba.stackexchange.com/questions/23699/is-the-overhead-of-frequent-query-cache-invalidation-ever-worth-it/23727#23727Sep 26, 2013
: https://dba.stackexchange.com/questions/50290/query-cache-hit-value-is-not-changing-in-my-database/50535#50535There are some options you have in this
OPTION #1
You could disable the query cache completely
If all queries against InnoDB tables are satisfactory thereafter, then add this to
my.cnf
OPTION #2
If you really want a bigger query cache, perhaps you should place limits on how big entries should be
Changing those options can allow you to dictate the minimum number of query cache entries.
For example, if you set the following
This will limit the number of query cache entries
If you really know your data workload and throughput, you could experiment with the limits.
YOUR ACTUAL QUESTION
Yes it is. In fact, InnoDB can be a real control freak in this respect when your make the Query Cache too large. While the blanket answer for this is to leave query_cache_size = 0, it is possible to intelligently size it for your dataset and workload.