We are looking for community feedback on the use-cases for the Query Cache in MySQL.
As astute followers will notice, the query cache is now off by default in 5.6, but realistically it’s always been disabled because the previous configuration was ON with zero memory allocated to it.
The reason for its disabling, is that the query cache does not scale with high-throughput workloads on multi-core machines. This is due to an internal global-lock, which can often be seen as a hotspot in performance_schema.
The ideal scenario for the query cache tends to be largely read-only, where there are a number of very expensive queries which examine millions of rows only to return a few. A hypothetical example might be a complex query to build a list of values for a drop-down list that always appears on a webpage form. In a situation like this, the query cache can mask performance problems caused by missing indexes, which makes it helpful for novice users.
My opinion however (and it does not necessarily reflect that of the MySQL team), is that this scenario is becoming less frequent:
- Many users are building queries via ORMs, which can often offer caching directly.
- Tooling is getting better, and novice users can discover missing indexes via things like MySQL Workbench’s visual explain.
- Any updates can invalidate the cache, and good performance practice teaches us that we should not pay attention to the average, but try and tighten the variance between user requests.
Have I missed anything? Please leave a comment, or get in touch. I would love to hear how you are using the Query Cache.