Is enabling MySQL query_cache_size Innodb Lock Safe?

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?

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 traffic
• Sep 26, 2013 : query cache hit value is not changing in my database
• Sep 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 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

• Sep 05, 2012 : https://dba.stackexchange.com/questions/23699/is-the-overhead-of-frequent-query-cache-invalidation-ever-worth-it/23727#23727
• Sep 26, 2013 : https://dba.stackexchange.com/questions/50290/query-cache-hit-value-is-not-changing-in-my-database/50535#50535

There are some options you have in this

OPTION #1

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 my.cnf

[mysqld]
query_cache_size = 0;

OPTION #2

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

[mysqld]
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.