Increase MySQL performance with query cache query_cache

Written by
Date: 2010-03-29 10:36:30 00:00

In order to increase the performance of MySQL server, query_cache may be turned on.

What this is going to do is make that the queries that are repeated will be cached and not actually executed each time they are requested by the code, this aproach is really good specially for php / MySQL based web pages, if a site grows and get lots of visits a day, tunning it is a must to better use the hardware resources, and not limit the webserver because of MySQL problems.

Let’s see how to enable query_cache. Edit the file my.cnf and add at least these two lines.

query_cache_type = 1 
query_cache_size = 40M

In the section of [mysqld] of the file, in the example, we are enabling the query_cache in the mode, where it caches all queries but the ones specifically specifying not to be cached with the directive “SQL_NO_CACHE”.

If we use query_cache_type = 2 the MySQL will only cache those queries that specifically asked for it, with the directive “SQL_CACHE”.

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.