MySQL Query Cache Performance

The MySQL Query Cache is a powerful feature which when used correctly can give big performance gains on your MySQL instance. The Query Cache works by storing in memory the SQL query (or Stored Procedure) along with the retrieved results. Whilst in cache, if the SQL query is called again, MySQL does not have to rerun it, it can quickly retrieve the results from the cache and send these back. Of course if there has been an insert, update or delete on a table which is referenced by the cached query then the statement will be forced to rerun. Whilst the Query Cache is great for applications with a high number of reads versus writes, there are also a couple of reasons which will make your queries un-cacheable.

• Use of functions, such as CURRENT_DATE, RAND and user defined functions

• Queries that use bind variables

AppDynamics for Databases shows the Key Performance Indicators of the MySQL Query Cache in graphical format.

• Graphical View of current allocation compared to maximum

• Percentage Utilization figures

• A ratio of Hits to Inserts is displayed in order to show the Query Cache effectiveness. A high ratio of hits to inserts tells us that there are lots of identical SQL statements being run on the database and are therefore being serviced directly from cache. A low ratio shows that the cache is not much utilized.

• The ratio of Inserts to Prunes represents how many times SQL queries are being inserted into the cache compared with how many times a query is being removed from the cache (pruned). This is also a good indicator of SQL reuse on the database and hence query cache effectiveness.

The maximum size (in MB) of the Query Cache is set in the database variable query_cache_size. A setting of 0 will automatically disable the cache.