0

MySQL query caching – How do you turn it on?

By Michael Alfaro on March 28, 2012
Share on Facebook1Tweet about this on Twitter2Share on LinkedIn9Google+1

Found the need for query caching when I found one of our client’s MySQL boxes having CPU spikes around 80% very frequently.  I realized the amount of content on the site had grown along with the amount of traffic to the site, to a point that it was starting to query the DB very frequently. So I start to look up optimizing MySQL and caching to see what I could find, and this blog post came up: http://www.howtogeek.com/howto/programming/speed-up-your-web-site-with-mysql-query-caching/

Here’s the portion of the post I was able to directly use on our client’s box to bring the CPU usage back down to the single digit to teens range.  Thanks to The Geek for this!

Enable Caching with Server Running

The first thing you’ll want to do is make sure that your installation of MySQL actually has query caching support available. Most distributions do, but you should check anyway.

You’ll want to run this command from your MySQL console, which will tell you if query caching is available.

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Don’t mistake this as meaning that query caching is actually enabled, because most hosting providers aren’t going to enable this by default. Oddly enough, my Ubuntu Feisty installation already had it enabled…

Next we’ll need to check and see if query caching is enabled. We’ll need to check more than one variable, so we may as well do it all at once by checking for the variable query%

mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 8388608 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+

Here’s the important items in the list and what they mean:

  • query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable caching.
  • query_cache_type – This value must be ON or 1 for query caching to be enabled by default.
  • query_cache_limit – This is the maximum size query (in bytes) that will be cached.

If the query_cache_size value is set to 0 or you just want to change it, you’ll need to run the following command, keeping in mind that the value is in bytes. For instance, if you wanted to allocate 8MB to the cache we’d use 1024 * 1024 * 8 = 8388608 as the value.

SET GLOBAL query_cache_size = 8388608;

Similarly, the other options can be set with the same syntax:

SET GLOBAL query_cache_limit = 1048576;
SET GLOBAL query_cache_type = 1;

Now how do we tell if it’s actually working? You can use the SHOW STATUS command to pull all the variables that start with “Qc” to take a look at what is going on under the hood.

mysql> SHOW STATUS LIKE 'Qc%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 65     |
| Qcache_free_memory      | 201440 |
| Qcache_hits             | 18868  |
| Qcache_inserts          | 2940   |
| Qcache_lowmem_prunes    | 665    |
| Qcache_not_cached       | 246    |
| Qcache_queries_in_cache | 492    |
| Qcache_total_blocks     | 1430   |
+-------------------------+--------+
8 rows in set (0.00 sec)

You’ll notice in the stats that I have plenty of free memory left. If your server shows a lot of lowmem prunes, you might need to consider increasing this value, but I wouldn’t spend too much memory on query caching for a web server… you need to leave memory available for apache, php, ruby, or whatever you are using.”