Optimizing MySQL's Query Cache

More performance! When it comes to Mysql database hosting the issue of more speed and performance is always paramount. So,  as part of our series on database hosting issues we want to provide you  some tips for understanding the query cache and optimizing it based on your usage for the best database performance.

The query cache operates independently of storage engine type, and serves to store the complete results sets of your queries.  It’s periodically flushed:  if any of the tables in a given result set is changed, the entire cached result set is thrown out.  The default size is only 16 MB, which is not very large.  The query cache is also bound by another parameter, the maximum query result set size, which limits the results stored in the cache by size.

But is the query cache useful to you and your MySQL performance?  The answer is pretty intuitive:  if your data is static and doesn’t change often, then the query cache is a helpful tool; query results can be drawn correctly from the cache.  However, if your data is constantly being updated, and thus the query cache constantly flushed, it’s hindering performance—MySQL will be spending a lot of time checking the query cache and invalidating all the cached queries.  Your SELECT queries could be noticeably slowed.  In this case, assigning memory to the query cache would be a waste of resources.

MySQL offers two important statistics about the use of the query cache feature:

1.)  If all the queries you are performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower.

2.)  Searches for a single row in a single-row table are 238% faster with the query cache than without it. This can be regarded as close to the minimum speedup to be expected for a query that is cached.

Is the query cache helping or hindering your database performance?  If it’s getting flushed out all the time by frequent INSERT and UPDATE statements, then probably hindering.  It would be more useful to assign the memory elsewhere.  If you decide that the query cache isn’t useful for your MySQL database, it’s simple to fix.  Edit your my.cnf configuration file and set the following values:

query_cache_size=0

query_cache_type=0

Happy hosting and if you are ever looking for high-performance database hosting, thats one of SingleHop's best areas of expertise.