Using and Tuning of the Query Cache

MySQL provides a mechanism to cache a query and its result set for reuse by all users. This can be extremely effective in environments where a large number of small, lexically equivalent queries are repeated often.

Parameters Affecting the Query Cache

Parameters in the my.cnf file exist to enable, configure and tune the query cache. Pay careful attention to the values of query_cache_size and query_cache_type because they interact in a non-standard way and can cause confusion.

have_query_cache

• Indicates that the server supports the query cache
• Specified as Boolean (YES or NO)
• Available as of 4.0.2

query_cache_limit

• Maximum size of a cacheable result set
• Specified in megabytes
• Available as of 4.0.1

The query cache is a relatively small structure when compared to the key cache (key_buffer_size) or innodb buffer pool (innodb_buffer_pool_size). This value should be set to maximize the number of queries captured, rather than capture a few large queries. Thus, it should be relatively small.

query_cache_min_res_unit

• Minimum block size of the query cache
• Specifed in kilobytes
• Available as of 4.1

query_cache_size

• Memory allocated to the query cache
• Specified in megabytes
• Available as of 4.0.1

If this value is 0, the query cache is disabled regardless of the value for query_cache_type. If this value is non-zero and the query_cache_type is 0 or OFF, the memory is still allocated and not used.

query_cache_type

• Sets the query cache state
• Specified as an integer (0,1,2)
• Available as of 4.0.3

When set to 0, no caching occurs. Memory can still be allocated and wasted. When set to 1, all results smaller than the query_cache_limit are cached unless the SELECT statement contains the SQL_NO_CACHE directive. When set to 2, results are cached only if the SELECT statement contains the SQL_CACHE directive and the results are smaller than the query_cache_limit.

query_cache_wlock_invalidate

• Controls locking aspects of tables with rows in the cache
• Specified as integer value of 1
• Available as of 4.0.19
• Specific to MyISAM

Normally a MyISAM table blocks all queries when a write lock is acquired. This behavior is not true when the result is in the query cache. Thus a dirty read from the cache is possible. Setting this parameter to 1 will purge any cached results involving the table when a write lock is acquire, thus preventing dirty reads.

Enabling the Query Cache

To enable the query cache you must set the query_cache_type to either 1 or 2 and set the query_cache_size to a non-zero value. It is important to do both because setting query_cache_size to a non-zero value while setting query_cache_type means memory is allocated but not used. This will likely exacerbate the problem you are attempting to solve by enabling the cache.

Consider the effect of allocating memory to the query cache and adjust other memory parameters accordingly. MySQL needs approximately 40kb of memory for the query cache to exist. Failure to allocate enough memory will result in a warning to the log.

Tuning the Query Cache

Like all things database, the query cache can be tuned to improve performance for your situation.

• Use the FLUSH QUERY CACHE statement to defragment the memory structure and avoid unnecessary pruning. No queries are removed in this operation.
   
• Use the RESET QUERY CACHE statement to clear the cache.
   
• Issue the a SHOW STATUS LIKE 'qcache_%' to see statistics on cache usage.

The Qcache_lowmem_prunes status variable counts the number of queries and result sets removed from the cache to make room for new ones. Use the rate of change on this variable to help determine the size of the cache and its overall efficiency. If this variable grows very fast all the time, it means that the cache is not providing a useful service and should likely be disabled. If it turns out that there are a large number of Qcache_hits and Qcache_lowmem_prunes is growing rapidly, this indicates that the cache size should be increased.

Qcache_not_cached indicates the number of queries executed but not cached. In general this is due to the query_cache_limit. Consider raising the size of this value and monitor the ratio of Qcache_hits to Qcache_lowmem_prunes. If the ratio decreases, this could mean you will need to increase the size of the cache to handle other cacheable queries.

About the Author

Boyd Hemphill is employed in the education industry for the state of Texas.  As an administrator, he supports multiple systems from 3.23 to 4.1.x.  As a developer, he works in a LAMP environment using  MySQL 4.1 and PHP 5.   Development will begin against the new 5.0 server in early 2006.  You can contact him at behemphi@yahoo.com.