MySQL no query cache hits

Question

My database have been up and running for a while with query cache enabled, but there are 0 hits. I have tried to check the settings but I am not sure what the problem is. Using InnoDB.

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+------------+
| Variable_name                | Value      |          
+------------------------------+------------+          
| query_cache_limit            | 268435456  |          
| query_cache_min_res_unit     | 4096       |          
| query_cache_size             | 4294967296 |          
| query_cache_strip_comments   | OFF        |          
| query_cache_type             | ON         |          
| query_cache_wlock_invalidate | OFF        |          
+------------------------------+------------+ 

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 622        |
| Qcache_free_memory      | 4291758416 |
| Qcache_hits             | 0          |
| Qcache_inserts          | 1471501    |
| Qcache_lowmem_prunes    | 0          |
| Qcache_not_cached       | 8995948    |
| Qcache_queries_in_cache | 2269       |
| Qcache_total_blocks     | 5199       |
+-------------------------+------------+

Any ideas ? I saw some other similar questions like this - the answer just suggests turning it off though without any good explanation except "the InnoDB Storage Engine likes to play traffic cop with the query cache".

Answer 1

Please look back at my answer again. Issue #1 might be your one and only problem. Why ?

• FACT #1 : 5199 Qcache_total_blocks allocated
• FACT #2 : 622 Qcache_free_blocks
• FACT #3 : 2269 Qcache_queries_in_cache
• FACT #4 : query_cache_limit is 268435456 (256M)
• FACT #5 : query_cache_min_res_unit is 4096 (4K)

What can said here ?

There are 2269 queries in the Query Cache using either 2 or 3 blocks (not accounting for fragmentation). Query Cache is 88.0362% (1 - (622/5199)) used. The largest a query can be inside in the Query Cache is 256M, and the smallest is 4K. So, queries are definitely piling up in it when SELECT queries retrieve result sets that's not there before.

If any SELECT query returns more than 256M or less than 4K, none of the data retrieved will be in the query cache. Consequently, running the same SELECT query (that begger than 256M or smaller than 4K) requires reading the data from disk all over again.

All you could really do is lower query_cache_min_res_unit to the minimum value. That's 512, which 0.5K. You can do that dynamically by running

mysql> SET GLOBAL query_cache_min_res_unit = 512;

Then, go monitor Qcache_hits. If this solves it for you, go add this to my.cnf

[mysqld]
query_cache_min_res_unit = 512

You don't have to restart mysql since you changed it dynamically.

If this does not change anything, then you need to examine the nature of your app. If your app retrieves data by some SELECT query and you never run that same SELECT query again, Qcache_hits cannot be anything other zero. If Qcache_hits is always going to be 0, that's why I had to bring up Issue #2. No sense in having a query cache on if you never cache anything. It will just nominally slow down InnoDB looking for any changed data. You can be sure that the data retrieved from SELECTs will be sitting in the InnoDB Buffer Pool.

You may need to spend some time examining the size of all your result sets and tuning the query cache to behave and dwell in peace with InnoDB (Why query_cache_type is disabled by default start from MySQL 5.6?)