The MySQL query cache is one of the prominent features in MySQL and a vital part of query optimization. It is important to know how the MySQL query cache works, as it has the potential to cause significant performance improvements – or a slowdown – of your workload.
The MySQL query cache is a global one shared among the sessions
It caches the select query along with the result set, which enables the identical selects to execute faster as the data fetches from the in memory. It is important to have everything identical, no new comments, spaces, or most significant differences in the WHERE clause. Basically when you trigger a select query, if it is available in the cache; it fetches from there or it considers the query as a new one and will go to the parser.
Even though it has some nice advantages, the MySQL query cache has its own downsides too. Well, let’s think about this: If you are frequently updating the table, you are then invalidating the query cache for ALL queries cached for that table. So really, anytime you have a “frequently updated table” means you’re probably not going to get any sort of good usage from the MySQL query cache. See the below example.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031320 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.06 sec) mysql> select * from d.t1; 405 rows in set (0.05 sec) mysql> select * from d.t1 where id=88995159; 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1020600 | | Qcache_hits | 0 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 2 | | Qcache_total_blocks | 6 | +-------------------------+---------+ 8 rows in set (0.00 sec) |
From the above we are sure the queries are cached. Let us try an insert and see the status, it will invalidate the query cache and reclaim the memory.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> insert into d.t1 (data)value('Welcome'); Query OK, 1 row affected (0.05 sec) mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031320 | | Qcache_hits | 0 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.00 sec) |
Now let us think about how to decide the MySQL query cache size:
To exemplify: I am having a mysql instance with two tables “t” and “t1”. Table “t” is with numerous records and “t1” is with fewer records. Let’s restart the mysql and see the query cache details.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> show variables like 'query_cache_size'; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | query_cache_size | 1048576 | +------------------+---------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031320 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.01 sec) |
From the above status note the below four points.
1) There is around 1 MB free space with Qcache.
2) The queries in Qcache are zero.
3) There are no Qcache hits.
4) Qcache lowmem prunes is zero.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> select * from d.t1; 405 rows in set (0.03 sec) mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1021624 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.01 sec) |
From the aforesaid status, it is clear the query has been cached and it should execute much faster in the next try and increase the Qcache hits status variable by one.
1 2 3 4 5 6 7 8 9 10 | mysql> select * from d.t1; 405 rows in set (0.00 sec). mysql> SHOW STATUS LIKE "%Qcache_hits%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 1 | +---------------+-------+ 1 row in set (0.00 sec) |
Now let us see how the data is getting pruned from the Qcache. For this, I will execute a select on table “t” which is having massive records.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | mysql> select * from d.t where id > 78995159; mysql> SHOW STATUS LIKE "Qcache_lowmem_prunes"; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Qcache_lowmem_prunes | 1 | +----------------------+-------+ 1 row in set (0.00 sec) mysql> select * from d.t1; 405 rows in set (0.02 sec) mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1021624 | | Qcache_hits | 1 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 1 | | Qcache_not_cached | 6 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.01 sec) |
The Qcache_lowmem_prunes is the status variable which indicates how many times MySQL had to clear/prune some data from the Qcache to make space for the outputs of other queries. We need to observe the Qcache_lowmem_prunes status variable and try to increase/adjust the size of the cache until we get a very low-value ratio for the variable.
It is also undesirable to keep the query cache relatively high value at 256 MB as the Qcache invalidation becomes costly. For details, Peter Zaitsev wrote about this a few years ago in a post that’s still relevant today titled, “Beware large Query_Cache sizes.”
Contention often makes the MySQL query cache the bottleneck instead of help when you have many CPU cores. Generally, the MySQL query cache should be off unless proven useful for your workload. So it is important to know your environment well to enable the MySQL query cache and to decide what the query cache size should be.
There will also be circumstances where there is no chance of identical selects and in this case, it is important to set the query_cache_size and query_cache_type variable to zero. The query_cache_type variable controls the query cache and setting the query_cache_type to zero will reduce the significant overhead in query execution. On a highly concurrent environment, there are chances of query cache mutex, which may become the source of a bottleneck. Setting the query_cache_type to zero will avoid the query cache mutex, as the query cache cannot be enabled at runtime which reduces the overhead in query execution. Please go through the details of QUERY CACHE ENHANCEMENTS with Percona Server.
Hopefully, this blog helped to explain how the MySQL query cache operates.
There is a nice query cache tuner made by Domas Mituzas:
http://dom.as/tech/query-cache-tuner/
Hi Arunjith,
Thanks for explaining the “qcache_lowmem_prunes” so clearly and concisely.
The post by Peter that a large query cache can cause serious performance issues is valid only when you have a query invalidating a large set of queries (we’re talking 10s of thousands of queries being invalidated). For most sites (especially non-transactional sites), this is not the case.
Also, would it be possible to give a brief explanation on what a free block is and how it works?
Hi Fadi,
The free blocks are the number of non-contiguous free memory blocks available in the query cache. Initially the query cache will be long and contiguous. Over time and with use, the long contiguous regions become fragmented into smaller and smaller contiguous areas.
To better utilize the memory you can defragment the query cache with help of FLUSH QUERY CACHE statement. Basically the FLUSH QUERY CACHE does not empty the query cache but it defragments the query cache and the value for the Qcache_free_blocks becomes ‘1’.
Hi Arunjith,
Thanks a lot for explaining the free blocks concept. I will test it on a large website at night and see if I can run it through a cron every nigh (let me know if there are better ways).
I once wrote a blog about flushing the query cache w/o cron:
http://databaseblog.myname.nl/2011/06/regularly-flushing-mysql-query-cache.html
Nice Article !
I have also worked around this MySQL Query Cache and created my note in this blog:
please visit some of my view about MySQL Query Cache.
For basic theory :
http://www.dbrnd.com/2015/08/mysql-query-cache/
For basic configuration:
http://www.dbrnd.com/2015/08/mysql-query-cache-configuration/
How to optimize first Byte Connect time as you seen in the report?
I have a column in my table which keeps on changing very frequently .How should i implement caching on that particular table ,should i move this column to different table ? How should i avoid caching invalidation ?
Thanks folks, helped me much to improve & eval response time!