mysql query cache

Mysql 4+ has a feature known as query cache. Here mysql caches the result set. So suppose a query is run and it takes 5 seconds to run and query cache is enabled, so results are cached in the cache. Next time if the same query is run again (remember - exactly same query that is strcmp(old_query, new_query) == 0) then the results are fetched from the cache and shown. And this takes very less time - say only 0.1 seconds.

I think, all of you who would be working with mysql for some time now, would be aware of this feature. The above para was just to refresh your memories.

Now lets check out the variables in mysql configuration file (my.cnf) which control the query cache.

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)

have_query_cache says whether mysql supports query cache.

query_cache_limit Dont cache results which are larger than this size. By default it is 1 MB. If your result set is larger, you can increase it as you like.

query_cache_min_res_unit The minimum size for blocks allocated by query cache. Default is 4096 Bytes (4KB). Will talk about this later.

query_cache_size Amount of memory allocated for caching results. Default is 0 - which disables query cache. You can set it to 128 MB or 1 GB. Depending on the memory available with your machine

query_cache_type 0 or OFF would turn query caching off. 1 or ON would turn the query cache on and the result set of every mysql query would then be cached. 2 or DEMAND would enable query cache but all result sets wont be cached. To cache results in this case you will have to specify "SQL_CACHE" in the query.

query_cache_wlock_invalidate 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 that table. This forces other clients that attempt to access the table to wait while the lock is in effect.

Now lets see how query cache works and how to tune it.

mysql> show status like '%qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 7 |
| Qcache_free_memory | 133638224 |
| Qcache_hits | 284 |
| Qcache_inserts | 626 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 550 |
| Qcache_total_blocks | 1116 |
+-------------------------+-----------+
8 rows in set (0.00 sec)


Qcache_free_blocks Number of free memory blocks in query cache

Qcache_free_memory Amount of free memory in query cache

Qcache_hits Number of hits to the query cache. Or, the number of times a query was found in the query cache.

Qcache_inserts Number of queries inserted in the query cache.

Qcache_lowmem_prunes Number of queries that where deleted from the query cache due to low cache memory.

Qcache_not_cached Number of not-cached queries

Qcache_queries_in_cache Number of queries registered in the query cache

Qcache_total_blocks Total number of blocks in the query cache

So as and when queries are inserted in the cache, the Qcache_inserts and Qcache_queries_in_cache would increase. Qcache_free_memory would ofcourse decrease. Whenever any DML query is run on a table, the queries in the cache related to that table are removed.

Some variables which let us know the efficiency of the query cache :

If the number of Qcache_hits is less than the number of queries_in_cache then the queries cached are not being used efficiently. And if Qcache_not_cached increases very quickly then queries are not being cached. This could be due to the fact that the result set of the queries are bigger than the variable query_cache_limit. So you should then increase this variable from its default value of 1M to 2M or maybe more.

If the variable Qcache_low_mem_prunes is increasing very fast, it would mean that the memory allocated to query cache is low. Cause mysql is freeing up memory to allocate new queries. Mysql is indirectly asking you to increase the query_cache_size

Mysql allocated memory for query result set in blocks. The default block size is 4K. So Qcache_free_blocks can be an indication of fragmentation. A high number as related to the Qcache_total_blocks means that the cache memory is seriously fragmentation. If the result set size is much less than 4K then fragmentation is high. There is another variable query_cache_min_res_unit which could then be used to decrease the block size from 4K to maybe 2K and help reduce fragmentation.

I had come across a similar issue on one of my servers. Where Qcache_free_memory was high, Qcache_total_blocks was also good. But Qcache_free_blocks was high and Qcache_lowmem_prunes was increasing very fast. And the server was performing very badly. What actually happened was that the cache memory was seriously fragmented. So almost every query was being executed and inserted in the query cache which would replace another query. The insertion and removal of queries from the query cache was taking up extra resources in addition to the resources needed for executing the query. This situation called for defragmentation of the query cache memory. So i ran FLUSH QUERY CACHE. It brought down Qcache_free_blocks and brought the increment of Qcache_lowmem_prunes to a crawl. And automatically the server performance improved. Now the query cache is flushed every 24 hours.

MySQL query cache is a very efficient tool if used properly.