Enable Query Caching (query_cache) in MySQL

269ac00f765508cb74b25d006dbf5bb2

Written by Dennis Nind

Last published at: February 8th, 2019

You can check whether or not query caching is already enabled using the following script...

MariaDB [(none)]> show variables like 'query%';
+------------------------------+---------+
| Variable_name                | Value  |
+------------------------------+---------+
| query_alloc_block_size      | 16384  |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit    | 4096    |
| query_cache_size            | 0      |
| query_cache_strip_comments  | OFF    |
| query_cache_type            | OFF    |

From the highlighted section above, you can see the cache_size is 0, and the query_cache_type is 0. You can enable query_cache by adding the following to your my.cnf in the [mysqld] section...

query_cache_type = 1 
query_cache_size = 40M

If we use query_cache_type = 2 the MySQL will only cache those queries that specifically asked for it, with the directive “SQL_CACHE”. This generally wouldn't be used on a shared hosting environment and should instead be set to 1. Please note that you can also change or update this value (and monitor its effect) when MySQL is running / without a restart...

MariaDB [(none)]> set global query_cache_size=80000000;

or similar...

MariaDB [(none)]> set global query_cache_size=80M;

The query_cache_size specifies the size of the cache, you may run some tests to know if the size you are using is the appropiate. Another usefull parameter is: query_cache_limit, the default value for this is 1M it tells MySQL what is the biggest query it should cache, you can modify this to fit your needs

query_cache_limit = 2M

Now while running your server and your applications that uses MySQL server, enter the console with this.

mysql -u root -p

Once in the console use this command to get some usefull info:

show status like 'qc%';

You should get something like this:

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2105     |
| Qcache_free_memory      | 28552504 |
| Qcache_hits             | 124244   |
| Qcache_inserts          | 40719    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 90       |
| Qcache_queries_in_cache | 9415     |
| Qcache_total_blocks     | 21119    |
+-------------------------+----------+
8 rows in set (0.00 sec)

Now, lets explain a little what these values means.

  • Qcache_free_memory: Is the memory free for new queries to be cached
  • Qcache_hits: Query cache hits, shows how many queries have been served by the cache, keep an eye on thise number, the bigger the better.
  • Qcache_inserts: Tells you how many queries have been cached, since the last time MySQL has been restarted
  • Qcache_lowmem_prunes: This is another important number, if this number grows too fast, it means you are running out of cache memory, as it indicates how many queries have been discarted from cache, in order to insert new ones, increase the query cache size (query_cache_size) to avoid this.
  • Qcache_queries_in_cache: The queries actually in cache.

Try to review these vaules from time to time, to be able to make some tunning to your configuration, also check your system memory, you can use vmstat to know how much of it MySQL is using, so you do not run out of memory. Experiment with query cache limit, and query cache size, to get the best query cache hit rate. You can analyze the usage of your query cache using the following formulas:

Current size compared with maximum available size:

To calculate the percentage used value for the query cache you can use the following formula:

((query_cache_size-Qcache_free_memory)/query_cache_size)*100

The Query Cache Hit Rate:

The percentage hit rate on the cache can be calculated as follows:

((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

e.g.The usage of 33% says that of all select statements executed, 33% of them can be satisfied by the cache and hence do not have to be re-executed.

Hits to Insert Ratio and Insert to Prune Ratio

These two ratios are calculated by the following two formulas:

  1. Qcache_hits/Qcache_inserts
  2. Qcache_inserts/Qcache_prunes

DO NOT allocate too much memory

Using less memory than available can reduce the performance, but using more memory than available can lead to worse performance or even crashes. A general resource allocation formula is:

memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections