The MySQL Query Cache: How it works, plus workload impacts (good and bad)

The MySQL Query Cache: How it works, plus workload impacts (good and bad)

PREVIOUS POST
NEXT POST

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

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.

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 a fewer records. Let’s restart the mysql and see the query cache details.

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 is no Qcache hits.

4) Qcache lowmem prunes is zero.

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.

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.

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

PREVIOUS POST
NEXT POST

Share this post

Comments (9)

  • Daniël van Eeden Reply

    There is a nice query cache tuner made by Domas Mituzas:
    http://dom.as/tech/query-cache-tuner/

    January 2, 2015 at 12:05 pm
  • Fadi El-Eter (itoctopus) Reply

    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?

    January 3, 2015 at 1:08 pm
  • Arunjith Aravindan Reply

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

    January 5, 2015 at 1:07 am
  • Fadi El-Eter (itoctopus) Reply

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

    January 5, 2015 at 2:47 pm
  • Daniël van Eeden Reply

    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

    January 7, 2015 at 1:57 pm
  • Anvesh Patel Reply

    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/

    September 1, 2015 at 1:39 pm
  • WPDIV Reply

    How to optimize first Byte Connect time as you seen in the report?

    October 16, 2015 at 5:49 am
  • Jitin Maherchandani Reply

    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 ?

    February 10, 2016 at 3:59 am
  • MarcH Reply

    Thanks folks, helped me much to improve & eval response time!

    January 14, 2018 at 11:24 am

Leave a Reply