1

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".

  • 1
    A 4GB query cache size is a terrible idea. The system will have to spend a lot of resources just managing what is in the cache. My understanding is that it should never be greater than 128MB. It is actually recommended to disable the query cache completely in newer versions of MySQL, unless you know you need it. In any case, a caching layer like Redis is probably going to be more effective than the query cache, though it does take application support. – Micah Yoder Nov 21 '14 at 21:25
1

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

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?)

| improve this answer | |
  • Thank you for the answer, I still see no hits in normal use (actually 2 hits had happened since I asked this question - so it do happen but extremely rare). I noticed that if I do a simple query such as "SELECT * FROM table WHERE id = 1" or similar I do get a hit if I run it twice very quickly after each other. Just two or three seconds is enough to miss the cache. I see the queries_cache_variable varying a lot, so it seem to me that queries are thrown out of the cache very quickly. free_memory is always big and lowmem_prunes is 0. I seem to have several hundreds of incoming inserts in bursts – Zitrax Nov 25 '14 at 15:24
  • I am guessing we modify tables so often that our inserted queries are quickly thrown out again. – Zitrax Nov 25 '14 at 15:44

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.