Lot of talk lately about the effectiveness or lack thereof of the MySQL query cache. I’m kind of surprised we are still having this discussion in late 2009, I really thought these issues have been long settled.
It’s well known that the query cache is terrible for environments with a constant stream of writes. Each write invalidates the cached results for every query touching that table. This means that your database needs to be constantly inserting and purging the query cache in an environment with mixed reads and writes (aka the most common type of “Web 2.0″ workload you’ll pardon the outdated cliché).
But now, Mark’s post goes one step further and reveals that even in a completely read-only environment, the query cache is still not effective. This was news to me, as I haven’t even bothered testing this feature in a few years. I’m sure if you really tried you could devise a scenario where it would outperform the InnoDB default, and it seems that consultants have encountered some installations where it has helped. However I’m quite certain that in a well-designed modern workload, the query cache is not going to be a good approach.
The obvious alternative solution is to use an external caching system, preferably a L1/L2 cache using APC/memcached or similar. Combine that with the most excellent MySQL/memcached UDF to intelligently prune your cache and you’ve got a system that easily can achieve performance at high concurrency.
I know what I’m saying here is not exactly breaking news (memcached was created 6 years ago), this really is the standard implementation now… my only point is that any discussion about the query cache that doesn’t suggest an external L1/L2 cache is missing the most important aspect.
Drizzle made the right call to remove the query cache feature entirely, with a plugin interface to optionally support that as needed. That approach may be too drastic for MySQL itself, which still has some low-end use cases that may be able to get a performance benefit from it (and where the caches may be too much overhead). In my opinion though, it should be disabled or set to DEMAND by default in a future version (and maybe ship the memcached UDF plugin too!). Is it too late for 5.4 GA?
PlanetMySQL Voting: Vote UP / Vote DOWN