Checking MySQL Query Cache

MySQL query cache can be useful, if it works. Here’s how to check it’s effectiveness.

show status like 'qc%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 6407 | | Qcache_free_memory | 24176544 | | Qcache_hits | 3075026 | | Qcache_inserts | 2435740 | | Qcache_lowmem_prunes | 363018 | | Qcache_not_cached | 157193 | | Qcache_queries_in_cache | 16022 | | Qcache_total_blocks | 39912 | +-------------------------+----------+

You can see here that we have 24Megs of unused query cache. This is essentially wasted memory. Make sure you’re not using a ridiculously high amount, since every time you perform an insert or update to a table it’ll kill any queries in the cache that reference those tables.

Personally, I prefer to use memcached, which can be used as a central cache system that every machine benefits from.