For some applications, the MySQL query cache can be a handy way to improve the performance of your queries. On the other hand, the query cache can become problematic for performance when certain situations arise. To understand how to troubleshoot these issues, you will first want to see what the MySQL query cache does.
What is the MySQL Query Cache?
The MySQL query cache will cache a parsed query and its entire result set. It is excellent when you have numerous small queries that in turn return small data sets, as the query cache will allow the return results to be available immediately, rather than rerunning the query each time it occurs.
Gathering information about the query cache
To see if the query cache is indeed on, and that it has a non-zero size, you can issue a quick command which will return a result set with this information. This is shown in the example below:
Getting information about the query cache. Source: Database Journal
If the cache is set to off or has no size, then it is not working at all. Setting the query_cache_type to “ON” and the query_cache_size to a number other than zero should get it started.
Some common issues that can cause performance problems are that the query cache size is too small, the query cache is fragmented, and that the query cache has grown too large. Each of these can be problematic in its own way, so being able to troubleshoot these issues can help you keep your database running smoothly.
Query cache size is too small
Ideally, the query cache will have a hit ratio near 100%. This ratio is determined by taking the number of query cache hits and dividing that number by the sum of query cache hits and com select. These values can be obtained by issuing a couple of commands, as shown below:
Getting information for the query cache hit ratio. Source: Database Journal
If the ratio is off, you can increase the query cache size and rerun the numbers. When the ratio is correct, then the query cache size is large enough for the cached queries.
Query cache is fragmented
If a cached query is larger than the result set of the query, then the difference is empty memory that cannot be used by other cached queries. This creates fragmentation. To fix this, you likely just need to adjust your query_cache_min_res_unit value to fix the issue.
Query cache has grown too large
If you are no longer using small queries or are no longer returning small result sets, then having the query cache enabled can actually become counterproductive. Instead of providing quick results, it can create a slowdown. In such cases, you will want to disable the query cache to restore performance.
Getting a MySQL Database
To get started with your own MySQL database, you can use a service such as Morpheus, which offers databases such as MySQL, MongoDB, and others as a service on the cloud. In addition, all databases are automatically backed up, replicated, and archived, and are deployed on a high performance infrastructure with Solid State Drives. Open a free account today!
