Scalability sucked when multi-core machines got busy, so DB’s devs will find another way
Looking over the number of results (and the diversity of advice offered) if you search “tuning MySQL query cache”, it’s not entirely surprising.
The problem is scalability, as MySQL Server product manager Morgan Tocker writes here.
The operation of the cache looks simple enough: SELECT commands are stored in a hash table, and if a incoming query matches the hash, the server can return the results from the last time the query executed (with protection so the server doesn’t return stale results).
The problem, Tocker writes, is that the cache “is known not to scale with high-throughput workloads on multi-core machines”.
Even if that could be fixed, he continues, the fix wouldn’t make the query cache’s performance more predictable, and that’s often more important than peak throughput for user-facing systems.
Instead of persisting with fixing the cache, MySQL Server’s developers have decided “to invest in improvements that are more generally applicable to all workloads.”
Developers who need caching can use ProxySQL, and other users upgrading to MySQL 8.0 “will be encouraged to use either Server-side Query Rewrite”. ®