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.