≡ Menu

What is the recommended value of query_cache_size in MySQL?

The question – What is the recommended size of the query_cache_size variable in MySQL?

The MySQL query cache is an in memory cache that stores the complete result sets of frequent SELECT queries. Instead of issuing the query again to the database engine, MySQL retrieves the result set from the cache and returns that to the client. The query cache is global, in that, all MySQL clients use the same query cache. The query caching functionality is very useful to websites that have a relatively static database and the pages are just a rendering of the data in the database. But query caching can also be useful, to some extent, to websites that have a high number of frequent SELECT statements from tables that change often.

Increasing your query_cache_size will definitely have an impact on performance of a few high frequency queries. On my machine, I have 2 GB of RAM and my query_cache_size variable is set to 16 MB which is quite less.

The decision on whether to increase the size of the variable query_cache_size can be easily made using a few diagnostic mysql sessions.

Should you increase query_cache_size?

First login to mysql command line prompt:

mysql -u databaseusername -p

This will prompt you for a password and then show you the mysql command prompt.

Use the following session to get somme statistics on your query cache:

mysql> SHOW STATUS LIKE "qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 2618    |
| Qcache_free_memory      | 8089536 |
| Qcache_hits             | 216892  |
| Qcache_inserts          | 3331399 |
| Qcache_lowmem_prunes    | 540755  |
| Qcache_not_cached       | 11490   |
| Qcache_queries_in_cache | 4788    |
| Qcache_total_blocks     | 12430   |
+-------------------------+---------+
8 rows in set (0.00 sec)

The Qcache_free_memory shows that currently there is about 8 MB of free memory from the available 16 MB. This means that about half of the query_cache_size is available for caching the outputs of more queries!

However, note that there is another variable called Qcache_lowmem_prunes which indicates how many times MySQL had to prune the query cache (remove some data) to make space for the outputs of other queries. This clearly indicates that increasing your query_cache_size will have a positive impact on performance. Try to increase the size of the cache till you get a very low value of Qcache_lowmem_prunes.

In any case, if like me, you have about 2 GB of available memory, you must first check on how much free memory you have:

ewhathow@ewhathow.com [~]# free -mt
             total       used       free     shared    buffers     cached
Mem:          2048       2043          4          0          0       1616
-/+ buffers/cache:        426       1621
Swap:         2048        717       1330
Total:        4096       2761       1334
ewhathow@ewhathow.com [~]#

So I have about 1.6 GB free! This clearly indicates that I should use a higher value of the query_cache_size.

How to increase the query_cache_size

The variable query_cache_size can be increased using the my.cnf configuration file of MySQL.

First locate the file:

ewhathow@ewhathow.com [~]# mysql --help | grep my.cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
ewhathow@ewhathow.com [~]#

My my.cnf is located at /etc/my.cnf.

Then, check if you have already set the query_cache_size variable in my.cnf:

ewhathow@ewhathow.com [~]# grep -i query_cache_size /etc/my.cnf
query_cache_size=16M
ewhathow@ewhathow.com [~]#

This shows that the variable is set to 16 mega bytes. To increase it, just use your favorite editor and open my.cnf.

vi /etc/my.cnf

Then add the following line to it, replacing 100M with the value that you want the variable to have:

query_cache_size=100M

Now you must restart MySQL:

service mysqld restart

So this was a short tutorial on how to determine whether increasing query_cache_size will help improve your MySQL database performance. Try higher values till you see the prunes reduce to a very low value. Note that after changing your cache size, you must wait for a day for the statistics to get updated. I hope this article was of use to you!

Incoming search terms:

  • query_cache_size (65)
  • mysql query_cache_size (22)
  • Whatistherecommendedvalueofquery_cache_sizeinMySQL? (13)
  • mysql cache_size default (1)

Comments on this entry are closed.

  • Madhur February 25, 2014, 8:39 pm

    Very useful post.
    Thanx

  • Dan April 30, 2014, 12:18 pm

    Great article! Thanks.