During last couple of months I had number of consulting projects with MySQL Query Cache being source of serious problems. One would see queries both for MyISAM and Innodb tables mysteriously stalling for few seconds with “NULL” in State column.
These could be troubleshooted to waiting on Query Cache which at the same time had massive amount of entries invalidated by some batch data load job.
When you should worry ? If you set query_cache_size relatively high at 256MB or more. It can be seen worse if your query cache size is in Gigabytes. At the same time check how many queries do you have in cache – Qcache_queries_in_cache – if it is in hundreds of thousands it may take a while to invalidate them. But first of all you should have something which causes massive amount of invalidations like tens-hundreds of thousands queries being invalidated by single insert – this typically happens if you have rare bulk loads rather than constant insertion.
You can also check how long FLUSH QUERY CACHE takes – if it is in seconds this is about as long as invalidation can take in extreme case.
How to solve the problem ? The easiest solution is to decrease query cache size – smaller values do not have such problems but also often will be less efficient. In many cases you may want to disable query cache at all and use external cache such as memcached which does not have this problem. You also can cause invalidations to happen more regularly but this also should affect query cache hit rate.
