When to use MySQL query_cache?

Question

Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a better memory caching solution (such as memcache.d).

But they also mentioned that there are a few cases in which query_cache is helpful. So a general recommendation would be to have it enabled to on-demand (using SELECT SQL_CACHE, with a query_cache_type = 2 config setting).

My question is, assuming you've got a caching solution like memcache.d in place, what type of circumstances would make the query_cache more optimal?

Edit: added link

Answer 1

Memcached (or Coherence) caches entire result sets. A cache in the database caches database rows. So let's say you have an access pattern where the query is fixed and the data changes infrequently (e.g. select * from restaurants where location='london'). You might run that query thousands of times for every time that a new restaurant is added, so caching the entire result set makes sense, it saves going to the database every time - but you still have all the manageability and flexibility of the RDBMS and SQL (you just need to kick out the cache on the odd occasion the data changes). Some people call this reference data or static data.

But let's say you have an ad-hoc access pattern (perhaps there are lots of options for your user to find exactly where they want to eat tonight, but it's rare for two users to have exactly the same preferences). Then you might want to cache the rows (to save going to the disk) but assemble each result set on-the-fly in memory. That's when you would want the database itself to manage what and how it caches. In most cases, a hybrid or layered approach will work best.

Note that there is also a third kind of caching in action - the OS's filesystem cache. I don't like these, for the simple reason that if you read a block from the disk it now exists in the database cache and the filesystem cache, yet the database doesn't "know" about the latter, so it can't do anything clever with it, like see how often it is used. From the DBA's perspective, any spare memory on the system over and about what the OS itself needs to be happy is wasted.

Answer 2

I think there's a lot of wrong information about the query cache out there.

The best case for the query cache, is when you have to examine a very large number of rows, but only return a few to a client. A typical situation where this is to be common, is a system where no proper optimization or indexing has been applied.

In a situation where many of the queries are primary key lookups, or otherwise very well optimized, the query cache can cause negative scalability. Yes: it makes things worse!

The reason for this, is that the design adds some internal locking, which limits your MySQL server from scaling on multi-core machines.

The query cache is a cause for many "sudden stalls" in MySQL - not all of them obvious. In Percona Server, we added a new state to the processlist (Waiting on Qcache mutex): http://www.percona.com/docs/wiki/percona-server:features:status_wait_query_cache_mutex

(Disclaimer, I work for Percona.)

Answer 3

I don't think it is already mentioned here, but it's possible that query cache also has an adverse affect on performance; perhaps this is what was mentioned on your podcast. If query cache efficiency is low (Qcache_hits / (Qcache_hits + Com_select)) and there are many query cache prunes (Qcache_lowmem_prunes/Uptime) occurring then it's possible that the overhead of maintaining the cache is costing more than you gain.

This post from Peter Zaitsev covers things in a bit more detail. Contrary to some answers here, he states that the cache is for whole result sets. However, the post is several years old. Some more recent thoughts were posted in April.

I have always had the impression that it is caching full result sets, not rows as mentioned above. If you have exactly the same query, it will skip parsing/planning and return the same result set (the maximum size of which is controlled by query_cache_limit).

Answer 4

If you have the query cache disabled, then a high read environment of where the SELECTs are very simple, then there are no locking mechanisms enabled. I just recently experienced this with MySQL 5.5 using Multiple Buffer Pools.

If you call the same basic queries repeatedly, no need to parse the same query over and over again till the cows come home. A small query cache should suffice in a heavy read environment using a small set of SELECTs you know will always be called.

memcached is much more handy for large sets of data in heavy read environment. Query cache is a lame duck at that point.