Overview

This article will help you enable query caching in your my.cnf configuration file for MySQL. Although this article will actually increase the memory used by MySQL the performance gain can be dramatic depending on your queries and database usage.

This is Part 2 of a series of articles on how to tune your (dv) for better performance. Please see Optimizing your (dv) server for additional articles in this series.

Enable query caching

Enabling query caching can improve performance of frequently used MySQL queries.

1. Create a backup of your MySQL config:

   cp /etc/my.cnf /etc/my.cnf.YYYY-MM-DD.bak 

   (Remember to replace YYYY-MM-DD with today's date, ie: 2011-03-30)

2. Edit MySQL configuration:

   vi /etc/my.cnf

   Insert the following lines under the [MySQLd] section:

   query-cache-type = 1
   query-cache-size = 8M

   The ##M is the amount of RAM to use for query cache. You may want to increase / decrease this value depending on your needs. Rage or Extreme server may want to set this value to 16 MBs. Save the file.

3. Restart MySQL

   /etc/init.d/mysqld stop
   /etc/init.d/mysqld start

   You can verify your changes via command line MySQL with the following method:

   mysql -uadmin -p`cat /etc/psa/.psa.shadow`

    

   You can check whether the query cache is present in your MySQL server using the following statements from the MySQL prompt type:

   SHOW VARIABLES LIKE 'have_query_cache';

    

   To check that caching is now enabled:

   SHOW VARIABLES LIKE 'query%';

    

   This will cache a lot of the frequently used queries, so the performance increase may not be seen immediately.

More tips

• Defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.
• The RESET QUERY CACHE statement removes all query results from the query cache.
• The FLUSH TABLES statement also does this.
• To monitor query cache performance, use SHOW STATUS to view the cache status variables:

  SHOW STATUS LIKE 'Qcache%';

The output of this command will look similar to this:

 | Qcache_free_blocks             | 376       |
 | Qcache_free_memory             | 1724608   |
 | Qcache_hits                    | 65894     |
 | Qcache_inserts                 | 53777     |
 | Qcache_lowmem_prunes           | 9307      |
 | Qcache_not_cached              | 1348      |
 | Qcache_queries_in_cache        | 1676      |
 | Qcache_total_blocks            | 4168      | 

In the above example, you can see that Qcache_lowmem_prunes is a large number. This means that the query cache had to remove queries to make room for other queries. Based on this example, you would want to increase query-cache-size slightly to keep your queries inside cache. If your Qcache_lowmem_prunes is "0", then you may want to decrease query-cache-size to free up memory for other processes.