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
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 state occurs while a session is waiting to take the query cache lock. This can happen for any statement that needs to perform some query cache operation, such as an INSERT or DELETE that invalidates the query cache, a SELECT that looks for a cached entry, RESET QUERY CACHE, and so forth.
This is definitely a huge problem with InnoDB tables because InnoDB mechanisms make inroads on the query cache. I wrote about this phenomenon before
There are some options you have in this
You could disable the query cache completely
SET GLOBAL query_cache_size = 0;
If all queries against InnoDB tables are satisfactory thereafter, then add this to
query_cache_size = 0;
If you really want a bigger query cache, perhaps you should place limits on how big entries should be
- You could raise query_cache_min_res_unit. It's default is 4K. If you set this higher, this will prevent small results from being in the query cache. This will reduce the overall number of entries in the query cache.
- You may want to change query_cache_limit. It's default is 1M.
Changing those options can allow you to dictate the minimum number of query cache entries.
For example, if you set the following
query_cache_size = 2G
query_cache_min_res_unit = 64M
query_cache_limit = 128M
This will limit the number of query cache entries
- Minimum of 16 (2G / 128M)
- Maximum of 32 (2G / 64M)
If you really know your data workload and throughput, you could experiment with the limits.
YOUR ACTUAL QUESTION
Is enabling MySQL query_cache_size Innodb Lock Safe ?
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.