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.

  • http://www.seunosewa.com/ Seun Osewa

    How about just reducing the size of the query cache so the small queries on less frequently updated tables will remain in the cache?

  • Dan

    You may be able to do that if you change the query cache type to “on demand” and explicitly tell it which SELECT queries you want cached. Otherwise, my fear is SELECT queries against those other tables might knock longer-lasting SELECTs out of the smaller cache before being quickly removed themselves. In either scenario here, there’s still overhead attached to the UPDATE and INSERT queries checking if they invalidate something in the cache. Only turning off the cache removes that overhead entirely.

  • http://www.justchromatography.com/ Chemist

    So in my.cnf I would add:

    query_cache_type on demand

    But how do you specify the queries to cache?

  • Dan

    The setting for on demand is:
    query_cache_type = 2

    Then you’d add the SQL_CACHE hint to the SELECT queries to have them cached:
    SELECT SQL_CACHE [columns] FROM [table] …

  • Pingback: Tuning and Optimizing Performace of MySQL | lonerunners.net()

  • james

    this was our case. Thanks. We hat a <5% QC hit rate. Disabled.