How query cache can cause performance problems?
Whenever query cache is enabled, any query executed has to access it for one reason or another. For example, the text of a reading query has to be looked up against the cache contents in search for the previously buffered result that could be returned, a freshly generated result from a SELECT statement has to be stored into the cache, but also writing to any table triggers invalidation of all cache entries associated with the particular table, regardless of the original modification size. All this activity relies on a single, global mutex lock. The lock is used to guarantee consistency for the cache contents and for the results returned from it. It implements concurrency control in such way that, while one thread (i.e. one query) is changing anything in the cache, no other query can access it until the lock is released.
Such architecture is not a problem unless a combination of factors occurs that results in the query cache mutex being locked extremely frequently or for extended periods of time. When it happens, in the best case it just builds some serialization around the execution of queries, which means higher response times. In the worst cases, it may bring a database instance almost to a stop when very few queries can make it past the lock. The factors mentioned earlier are:
- high concurrency – many queries sent concurrently over a large number of connections
- workload type – often a good mix of reads and writes
- query cache size – invalidating results, which all happens under the lock, takes longer with more data cached
There are no rules that would allow anyone to predict what combination of these can actually trigger the problem and therefore enabling the query cache always exposes MySQL instance to the possibility of it occurring. Since out of the three factors only size can be controlled, to minimize the risk, it is advised to be conservative when setting database options and never allow query cache size larger than one or two hundred megabytes. In many opinions, it is even best to disable it entirely as there are not too many workloads, where the cache can actually be efficient.
