Tuning MySQL query_cache

An appropriate amount of "query_cache" is believed to improve MySQL performance at the expense of available memory. "Appropriate" will vary widely with usage and "performance" on a live site can be elusive. On Ubuntu, the defaults are modest but "work".

# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M

Drupal 5.x provides a summary at "admin/logs/status/sql" and 6.x at "admin/reports/status/sql".

After running up query_cache size to 128M, the summary is

Query cache information

The MySQL query cache can improve performance of your site by storing the result of queries. Then, if an identical query is received later, the MySQL server retrieves the result from the query cache rather than parsing and executing the statement again.

Variable Value Description
Qcache_queries_in_cache 49380 The number of queries in the query cache.
Qcache_hits 2287650 The number of times MySQL found previous results in the cache.
Qcache_inserts 436428 The number of times MySQL added a query to the cache (misses).
Qcache_lowmem_prunes 110096 The number of times MySQL had to remove queries from the cache because it ran out of memory. Ideally should be zero.
 

It is now 256MB and will take a few hours to populate the cache. Not sure how far I am willing to go. Memory is a finite resource. Shared hosting people will have to plead with their server administrator. VPS users may be out of luck. Typical RAM slice is 256MB. That leaves dedicated servers for many.

After 24 hours, 256MB is sufficient to keep "prunes" at 0.

Query cache information

The MySQL query cache can improve performance of your site by storing the result of queries. Then, if an identical query is received later, the MySQL server retrieves the result from the query cache rather than parsing and executing the statement again.

Variable Value Description
Qcache_queries_in_cache 87368 The number of queries in the query cache.
Qcache_hits 982257 The number of times MySQL found previous results in the cache.
Qcache_inserts 253657 The number of times MySQL added a query to the cache (misses).
Qcache_lowmem_prunes 0 The number of times MySQL had to remove queries from the cache because it ran out of memory. Ideally should be zero.

 


Comments

The downside of a large

The downside of a large query_cache_size in MySQL is that writes to a table must invalidate the cache for that table.  Combine this with the course grain locking scheme for the cache and you can end up with lock contention if you have moderately heavy write loads and a large query cache. 

David Lutz at Sun recently posted his findings on this behavior using DTrace to deeply examine where the problems can arise:  http://blogs.sun.com/dlutz/entry/mysql_query_cache_sizing