MySQL Tuning: Disable Query Cache on Frequently Updated Databases
It may seem counterintuitive, but for some types of applications, the query cache, especially if set to be large, can reduce performance. MySQL’s query cache stores the results of previously executed queries. In theory, the cache exists to speed up your application by responding to the same query with results directly from memory instead of parsing, optimizing, and re-executing the query against the database.
However, the query cache is only useful in certain scenarios. A fairly static database which is hit by the same SELECT queries often will benefit from the result set coming directly from the cache instead of hitting the tables. However, there are many scenarios that result in the query cache being bypassed:
- The SELECT query doesn’t match exactly. Checking the query cache is the first thing MySQL does when processing the query. It checks the cache before doing any optimization of the query, so whitespace, capitalization differences, comments in the query, etc. will all cause the cache to be bypassed.
- Only full queries are cached. Subqueries, parts of a UNION, etc. will not match the cached query and won’t benefit from the cached results.
- Stored procedures are not cached, even if they return a result set.
- Prepared statements are not checked against the cache.
- Queries that contain non-deterministic functions aren’t cached. That includes CURDATE(), RAND(), or any other function where the output isn’t always the same.
- Any query which changes a table touched by a cached query invalidates all of those queries.
That last exception is the important one. Any INSERT or UPDATE query will invalidate all cached SELECTs against a table. If your database is frequently hit by INSERT and UPDATE queries, it’s going to be spending precious time checking the query cache and invalidating cached queries. If your query cache is large, your SELECT queries may be noticeably slowed waiting on a response from the query cache while it’s busy removing invalidated queries by some other thread. You’ll also be wasting valuable memory on a cache you’re not using while you could be allocating that memory to more valuable buffers or caches, to the application, or to the operating system.
If that describes the average usage for your database, you may see a small performance boost by disabling the query cache altogether. To do so, edit your my.cnf configuration file and set the query_cache_size to 0 and query_cache_type to 0.