Someone once said, “…cache invalidation is one of the most terrifying things in IT…” I heard this from a guru at some event somewhere in the world. The phrase stuck with me, but I can’t recall who said it. Maybe it was just stored in my cache—and when that cache was invalidated, it wasn’t saved to disk.
MySQL has many buffers and caches. One interesting guy is the MySQL Query Cache. This is a specialized cache that stores a hash of executed queries—specifically, SELECTs—and their results.
In other words, if you ask the MySQL server for all customers from São Paulo with profile “A” using:
sqlCopyEditSELECT * FROM clientes WHERE cidade = 'São Paulo' AND perfil = 'A';
MySQL stores a hash of this query as a key, and caches the entire result set.
If the exact same SELECT is issued again, instead of reprocessing it, MySQL will simply fetch the cached result and return it immediately. Fast! Easy! Simple!
Sounds great? Well… here lies the danger! If you have a relatively static website database, it works wonderfully. But if the tables involved are updated frequently, it can become a nightmare.
Why? Because of cache invalidation—the art of clearing out stale or expired data from the cache.
If you know MEMCACHED, Query Cache works similarly: you decide what to cache. But MEMCACHED lets you set expiration times. Up to MySQL 5.5, Query Cache does not have time-based expiration. It invalidates cached results whenever any table involved in the cached query is updated.
So, if you cache SELECTs involving tables with lots of updates, you might actually hurt performance. MySQL has to store the results, then immediately invalidate them frequently.
In summary, Query Cache was created to speed up your MySQL server by quickly returning pre-processed query results. The trick is to choose carefully what to cache—and what not to. This applies to all caches, including MEMCACHED.
Many clients have told me they saw performance drop after enabling Query Cache. The answer? Misuse.
That’s why I dislike most blogs and how-to’s out there—they tell you how to enable features but rarely explain the underlying concepts. Without understanding the concept, you can easily run away from the holy grail of performance.
A practical example: imagine an online sales system. Which tables to cache, which not, and which to treat carefully?
- Cache: cities, states, payment methods, promotions, product rankings*, customer rankings*, products;
- Don’t cache: budgets, orders (shopping cart), stock movements;
- Think carefully about: customers
Rankings usually involve complex SELECTs scanning multiple tables—expensive queries. That’s where summary tables and scheduled events come in handy.
For example, create an event that runs hourly to update summary tables for product and customer rankings. Since these only update hourly, caching SELECTs on these summary tables makes sense.
When I teach Performance Tuning or MEMCACHED + MySQL, I always say:
“Cache everything that’s expensive to process, but never forget other performance techniques.”
Summary tables are a great example—combining summarization with caching.
In the next article, we’ll discuss how to configure the Query Cache.
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
