Introduction to the MySQL query cache

 

IMPORTANT NOTICE: MySQL query cache is disabled by default.

MySQL query cache is a cache mechanism that stores the text of the query (e.g. 'SELECT * FROM users WHERE deleted = 0') and the result of the query into memory. If an identical query is executed again the result stored within the cache will be returned instead. Naturally MySQL will invalidate cache entries for any given table if that table is modified. It is also very worth mentioning that MySQL uses byte by byte comparison of SQL statements for the cache. This in practice means that these two SQL queries will be considered different: 'SELECT * FROM users' and 'select * from users'. So having a fixed standard for writing SQL statements in your projects will lead to increased performance if run on a MySQL server with enabled query cache.

Getting a hang of the Query Cache

This is the version of MySQL on my desktop Debian machine:

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.1.46-1-log |
+--------------+

This is my Debian version info:

$ uname -a
Linux david 2.6.26-2-686-bigmem #1 SMP Tue Mar 9 18:01:52 UTC 2010 i686 GNU/Linux

Naturally, the first action in playing with the Query Cache will be to check if it works and turn it on if it does not:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Note: When using a standard MySQL binary, this value is always YES, even if query caching is disabled! Value of YES only indicates that the support for MySQL Query Cache is compiled into the binary but does NOT indicate that it is operational. So the only way to actually determine if query cache is operational is to check if queries are being inserted and retrieved from the cache.

This is how you will gain access to the variables relevant to the monitoring and configuration of the query cache:

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 3979     |
| Qcache_free_memory      | 11542464 |
| Qcache_hits             | 825713   |
| Qcache_inserts          | 267189   |
| Qcache_lowmem_prunes    | 184299   |
| Qcache_not_cached       | 104195   |
| Qcache_queries_in_cache | 14179    |
| Qcache_total_blocks     | 35683    |
+-------------------------+----------+

From these you can clearly determine that the cache is operational. Now, what if your cache is not operational and you want to enable it? Setting the following variables in my.cnf and restarting MySQL server will enable the query cache if it has been compiled into the binary.

query_cache_size = 32M
query_cache_type = 1

Pay close attention to the values of Qcache_free_blocks and Qcache_free_memory. If the ratio is relatively high it indicates that the cache memory has become rather fragmented which can also be a rather nasty and hard to figure out gotcha. If this ever occurs, it can be temporarily fixed using:

mysql> FLUSH QUERY CACHE;

MySQL Query Cache is not the magic bullet

You turned the cache on and you expect better performance? Hold your horses. You need to think about it a little bit. MySQL Query Cache is a rather complex toy and before you understand when it's useful you need to understand how it operates. There are several situations where the cache might prove to be a performance killer. One of these situations is when you have a lot of writes to the database. Every time you write something to a table MySQL Query Cache must discard all cached queries that had data from that table. So if your application does a lot of writes it might get to the point where the cache is doing a lot of caching and discarding but delivers very few cache hits to the point where it actually hurts performance.

There is a way to be selective about caching though. If you want to make really smart applications you can select manually queries that need to be cached. This way you can avoid queries that would frequently be flushed from the cache from ever being cached. If query_cache_type is set to 1, (meaning ON) all cacheable queries will be cached except if flagged with SQL_NO_CACHE.

SELECT SQL_NO_CACHE * FROM table_that_is_often_written_to;

If query_cache_type is set to 2, (meaning ON DEMAND) then you must flag queries that you WANT cached.

SELECT SQL_CACHE * FROM table_that_does_not_change_much;

There is another interesting case I found online. Too large cache. How can a large cache be a hindrance you wonder? Well I would have never imagined it if I didn't read of a real case scenario where it happened. Apparently these guys had some extra memory on the server so they spared 500MB to their MySQL Query Cache. Looks like a fine idea on the surface, right? What ended up happening was a server that spent majority of it's processing on sorting cache entries. Cache index needs to be sorted, that is obvious, right? Well when you have a 500MB cache you also have a huge cache index. And every entry that goes into that index needs to go to it's correct place. And so we ended up with a server that spent most of it's time doing just that. Not a very good way to spend your CPU time on a busy web server.

Tune, tune and then tune some more

It takes some time for the Query Cache to reach a steady state where you can actually use the stats to assess the performance of the cache. So leave it running so it can at least fill the cache close to full. Now go and check the stats with:

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 3979     |
| Qcache_free_memory      | 11542464 |
| Qcache_hits             | 825713   |
| Qcache_inserts          | 267189   |
| Qcache_lowmem_prunes    | 184299   |
| Qcache_not_cached       | 104195   |
| Qcache_queries_in_cache | 14179    |
| Qcache_total_blocks     | 35683    |
+-------------------------+----------+

Based on these numbers you can make a semi educated guestimation on the performance of your cache.

Nasty gotcha with table locking

So you lock your tables thinking there is no way to get data from them when you do that. But, somehow, your MySQL server with a full peace of mind returns result from locked tables. Next step is to file a bug report, right? Well... maybe, if you don't use query cache that is. It turns out that query cache can return results for locked tables. So be careful not to make that assumption in the first place.

Queries must be deterministic

Don't forget that queries that use non-deterministic functions can not be cached. Any query that uses functions such as UUID(), RAND(), CONNECTION_ID() will not return the same data on each call and as such are not candidates for caching. If queries of this type are a majority of queries in your application you might consider not using query cache at all.

It would be nice to actually know how much of an improvement have you made wouldn't it?

So how to go about assessing how much actual speed improvement has been achieved. MySQL server it self offers a pretty good profiling tool. The new profiler became available in the 5.0.37 version of the MySQL Community Server. MySQL profiler can be used directly from MySQL console prompt and is a handy tool to use for some quick profiling tasks. If you need to profile a complex PHP/MySQL application you might opt for XDebug/KCacheGrind solution which will enable you to get an idea how much of your web site processing goes to MySQL queries and how much goes to PHP processing.

Your thoughts

Stefan, 10-02-12 09:22
Hello,

in our online shop we noticed frequently a 3 minutes long congestion with actually put all our websites on hold. We spend properly several months to find the problem, spend shitloads of money on new servers without any success.

At the end it turned out to be the query_cache, that we used a by far to highvalue (2GB). The German phrase is "viel hilft viel", roughly translated "a lot helps a lot". Now everytime someone changed something in the article database, the server wrote 2GB of cache to the drive which took longer than expected and locked the table.

Now we are using 512MB and it's much faster, we might even reduce is to 128MB or 256MB. So, be careful what type of figures you are using.

Stonki

Add comment

* - required field

*




*