3

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?

5

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

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.

  • Wow @RolandoMySQLDBA thanks for providing such an informative answer. For a simpleton such as myself could it be summarized as: "Yes it's thread safe, but because InnoDB is such a control freak it could reduce performance, thus you either want to leave it off or do a lot of further checking/tweaking/adjusting to make sure performance is not reduced." – Programster Jun 13 '14 at 9:45
  • In your example, you're setting the cache to 2GB - most advice elsewhere suggests sizes like 256K - a few MB. A smaller cache means less entries, which means less maintenance overhead. Is a small cache better than no cache? You won't get a super high hit rate, but maybe something like 30% - is there any way to know if that's better than nothing? – Ralph Bolton Mar 17 '17 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.