MySQL Query Cache

First, for the uninitiated, here’s a little background:

There are three primary MySQL caching techniques: file-based, application, and the built-in query cache.  There’s a good overview of the techniques here.

In the simplest sense, the MySQL Query Cache stores the text of the query and the result of the query in memory.  The Query Cache section of the MySQL 5.1 Reference Manual states it this way:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

It is easy to check your setting:

SHOW VARIABLES LIKE ‘have_query_cache’;

Then, check cache-related variables like this:

SHOW STATUS LIKE ‘%qcache%’;

Check out the “MySQL Query Cache is not the magic bullet” section on this page.

For a fascinating read on proposed improvements to the query cache mechansim, you must read the April 10, 2011 article:  “Should we give a MySQL Query Cache a second chance?” by Peter Zaitsev on the MySQL Performance Blog.  He writes:

Over last few years I’ve been suggesting more people to disable Query Cache than to enable it. It can cause contention problems as well as stalls and due to coarse invalidation is not as efficient as it could be. These are however mostly due to neglect Query Cache received over almost 10 years, with very little changes done to the core of original implementation which appeared in MySQL 4.0 Query Cache was designed to work with single core systems and relatively small memory amounts, both of which are things of the past.

However, if you think about Core idea of the MySQL Query Cache – it is great.

Zaitsev is an expert (and the previous manager of the High Performance Group within MySQL).  He makes a compelling, detailed list of proposed improvements: Make it Lockless, Fast Invalidation, Support for Delayed Invalidation, Smart Query Matching, Protection from Multiple Misses, More Granular Invalidation, Query Cache Storage Engines, Compression, Delayed Updates, Improve Manageability, and Self Refresh.

Zaitsev’s article is outstanding and should not be missed.