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. |
- tarvid's blog
- Login or register to post comments
- Printer-friendly version
- Send to friend
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