I've been working for some weeks now to optimize a client's MySQL databases. I consider myself an apprentice MySQL DBA (e.g. there's much room for me to improve :P ), and I've found the process to be difficult. Although there is a whole lot of information about what to do and how -- some blog posts out there are absolutely invaluable -- the settings are not always straightforward to understand, since they often have many impacts and to correctly modify them, you need to understand what they do and how they change things under the hood for MySQL.

After bumping table_cache and tmp_table_size / max_heap_table_size up, I was left with one weirdness: the table cache is marked as being nearly empty (good) but there were a lot of query cache prunes (bad). This situation actually makes the cache add latency for most cases (in other words, it slows down tremendously your database).

Reducing query_cache_min_res_unit for less cache fragmentation

I've found this blog post which talks about experimenting with decreasing the default cache block size from the default value of 4K to something closer to the average query result size:

I made the calculations that are done in this blog post and I had approximately the same average result size (around 2.2K), so I intend to try and apply the same two-step modification to the value of query_cache_min_res_unit.

To actually go further, I installed the mysql cacti templates from http://code.google.com/p/mysql-cacti-templates/ in order to have some real followup of performance. I set up a bunch of graphs for general purpose values, and I added the three following graphs: Query cache, Query cache memory and Temporary objects. I think with the data that those graphs will provide, I'll be able to take a much more enlightened decision about the query cache.

Is the query cache useful at all?

I've also found this interesting page questioning the usefulness of the query cache in most cases (read the comments, there's a bunch of good points that people put up):

To sum it all up:

  • The query cache in MySQL adds overhead to each request, since you have to verify whether your request is cached or not, and if not, once you get the results MySQL will try and store that in the cache (if possible).
  • The query cache can be useful to save a lot of processing time if used wisely: for queries repeated often on tables that seldom change.
  • In most cases, you won't be able to use the cache appropriately, so the query cache just gets in your way.

If you're not able to use the query cache appropriately for it to save your server processing time, then it's considered better to turn it off altogether.

I'll have to verify the percentage of cache hits over the next two weeks or so to judge if the cache is useful or not.