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. |
