7

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

Improve this question
  • Query cache sucks big time if you are ever going to do writing. Glad it's disabled by default in the later MySQL versions. – Pacerier Jan 19 '15 at 21:02
5

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.

Improve this answer
  • 1
    Is this really true for MySQL? query_cache_limit describes the maximum result set size that can be cached, as I understand it. – Sam Brightman Aug 31 '11 at 15:32
  • A cache like Oracle Coherence doesn't typically cache result sets per se, but rather it caches the "things" your application puts together out of multiple queries -- things like "data objects" or DTOs or POJOs, or documents like XML or JSON. The way to effectively cache these is to either (a) pre-load everything (I think that's what you meant), or (b) load only on cache miss -- which is nicely accomplished with a "read-through" cache. Then you either time the cache out (for caches that allow dirty data), or you invalidate the cache from the app level, or you stream updates into cache from DB. – cpurdy Jun 4 '14 at 1:54
  • @Gaius, Why do you say that MySQL query cache cachees only database rows as opposed to entire result sets? Aren't the database rows of the query equivalent to the entire result set? – Pacerier Jan 19 '15 at 21:00
  • Because the database cache saves you a round trip to the disk, it doesn't save you re-executing the query (tho' you may be saved a re-parse). Whereas memcached uses a hash of the text of the query to access the cache directly. – Gaius Jan 20 '15 at 22:50
6

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

Improve this 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).

Improve this answer
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.