Enabling MySQL’s Query Cache

Submitted by elyzium on Thu, 2019-03-21 10:03

MySQL is the most common database used with Drupal. MySQL has the ability to cache frequent queries
in RAM so that the next time a given query is issued, MySQL will return it instantly from the cache.
However, in most MySQL installations, this feature is disabled by default. To enable it, add the following
lines to your MySQL option file; the file is named my.cnf and specifies the variables and behavior for
your MySQL server In this case,
we’re setting the query cache to 64MB:

First , let's make sure that your installation of MySQL actually has query caching support available. Most distributions do, but you should check anyway.

MariaDB [(none)]> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

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%

MariaDB [(none)]> 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             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
| query_prealloc_size          | 8192     |
+------------------------------+----------+
8 rows in set (0.00 sec)

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;

# The MySQL server
[mysqld]
query_cache_size=64M

The current query cache size can be viewed as output of MySQL’s SHOW VARIABLES command:

mysql>SHOW VARIABLES LIKE 'query_cache%';
...
| query_cache_size | 67108864
| query_cache_type | ON
...

Experimenting with the size of the query cache is usually necessary. Too small a cache means
cached queries will be invalidated too often. Too large a cache means a cache search may take a
relatively long time; also, the RAM used for the cache may be better used for other things, like more web
server processes, memcache, or the operating system’s file cache.

Other directives may also need to be done:

1) innodb_buffer_pool_size
2) tmp_table_size
3) max_heap_table_size
4) table_open_cache

Comments

Rickyapalk (not verified)

Tue, 2019-12-03 14:30

Main menu

Status message

You are here

All Tags

Top 30 viewed articles

Enabling MySQL’s Query Cache

Tags:

Comments

Pages

Add new comment

Filtered HTML

Plain text

To Do List