Daily Archives: June 27, 2019

Enabling query cache for MariaDB/MySQL

Query cache can speed up to 2-3 times queries that are often run.
It is pretty easy to set up. First you need to check if query cache is supported for your system:

show variables like 'have_query_cache';

That should return:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Then you need to check some variables related to query caching. I will explain each of them:

mysql> show variables like 'query_cache_%' ;
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 262144   |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

query_cache_limit – the total size each individual query can be. In my case that is 262144
query_cache_min_res_unit – how large is each chunk/block of cached data
query_cache_size – the total amount of cached queries, 0 disables it, and it needs to be at least 40kb at the lowest to work. Usually the default is 16,777,216
query_cache_type – if the value is ON it means cache query is enabled. If it is OFF it means it is disabled.

Add the following to your sql config file, probably in /etc/mysql/my.cnf and edit the values to fit your needs and/or your servers specs –

[mysqld]
query_cache_type=1
query_cache_size = 32M
query_cache_limit=512K

Then restart mysql/maridb and you should be good to go. You can verify the changes in the config file by running again:

show variables like 'query_cache_%';

You can check some query cache stats by running this:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2        |
| Qcache_free_memory      | 14713664 |
| Qcache_hits             | 1750     |
| Qcache_inserts          | 1643     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 247      |
| Qcache_queries_in_cache | 423      |
| Qcache_total_blocks     | 887      |
+-------------------------+----------+

For further and more detailed information, MariaDB knowledge base about Query Cache is far better place.