≡ Menu

How to enable or increase the MySQL query cache size?

The question – How do I know if query caching is enabled on my Linux server? Is there a way to check what the size of the cache is and how to increase the cache size?

MySQL comes with a query cache functionality that caches results of frequent queries for shorter execution time. Query caching stores the result of a SELECT query in memory so that MySQL does not have to issue the query again. Storing the result sets of frequently used SELECT queries in memory (RAM) has a strong impact on the performance of your web pages. For websites that have a small number of SELECT queries that are very often used, just to render the data in the database on the web site, query caching can have a significant effect on database performance. In most websites, the database is the most important bottleneck of performance. By storing results of queries in memory, this bottleneck can be improved considerably.

In this tutorial, I explain how to enable, disable and increase the query cache on MySQL over the Linux machine. The article is applicable to any Linux flavor like CentOS or Debian.

Check whether your MySQL installation has the query cache functionality:

To do this, login to your MySQL command prompt tool:

mysql -u username -p

After you login, you will get the mysql command prompt. Type in the following query on the mysql command prompt:

SHOW VARIABLES LIKE 'have_query_cache';

Here is the output:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

mysql>

This variable have_query_cache represents that your MySQL installation contains the functionality of query caching. It does not say that query caching is enabled. To check if query caching is enabled, you should use the following command:

SHOW VARIABLES LIKE 'query_cache_size';

This will output the query cache size on MySQL:

mysql>
mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+
1 row in set (0.00 sec)

mysql>

This shows that query caching is enabled and the size of the query cache is 16 MB. Setting this variable query_cache_size to 0 will disable the query caching.

How to increase the query cache size?

To increase the query cache size, you need to set the variable query_cache_size in my.cnf.

First locate your mysql configuration file:

mysql --help | grep -i my.cnf

This will output:

root@server [/etc]# mysql --help | grep -i my.cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
root@server [/etc]#

So MySQL first loads /etc/mysql/my.cnf and then /etc/my.cnf and then my.cnf in the home directory. Variables that are specified in multiple files will have a preference for values according to the preference given in the file name order in the above bolded line.

Now run the following command:

grep -i query_cache /etc/my.cnf

This will output:

root@server [/etc]# grep -i query_cache /etc/my.cnf
query_cache_limit=1M
query_cache_size=16M
query_cache_type=1
root@server [/etc]#

There are three variables for the query caching functionality.

query_cache_size – This variable is the query cache size. Make this 0 to disable query caching.
query_cache_type – This variable should be kept at the value of 1 which says that query caching is enabled for all queries except queries that begin with SELECT SQL_NO_CACHE
query_cache_limit – This controls how much maximum memory can be used for each individual query

To increase the query cache size, simply type in a different number and save your my.cnf file.

For example, if you want the query cache to be 100 MB, change the above variable in my.cnf to:

query_cache_size=100M

The value of 100M is quite sufficient for most servers. I personally use the value of 16 MB which works well in my case, but 100M might be good if you have a lot of memory (RAM). The query_cache_limit of 1M should be quite sufficient for most queries.

How to enable query caching?

To enable query caching, just set the variable query_cache_size to anything above 0. Note that the minimum query cache size is 40 KB because of the overheads involved with query caching. If you set it to less than 40K, you will get a warning and query caching will not work properly.

How to disable query caching?

To disable query caching completely, set the variable query_cache_size to 0:

query_cache_size=0

After making changes to the my.cnf file, you must restart MySQL:

service mysqld restart

Incoming search terms:

  • mysql enable cache (1)

Comments on this entry are closed.