Percona Configuration Wizard and query cache values

MySQL & MariaDB Other MySQL® Questions
1

I’m rather new to using the Percona Config wizard and so far I’m rather impressed. However, I’ve got a question that’s been bugging me. I was just hired to take over for a Systems Engineer who was frankly out of his league. (Which isn’t particularly important.) One of the things I’ve been fighting is a webserver that’s used to provide a social network type environment for teachers to share lesson plans all over the world. It’s a fairly busy server and the previous Admin essentially installed MySQL 5.5 on a Ubuntu 12.04 LTS server and left the my.cnf file with the default settings.

Currently the server is residing on a virtual machine with 8 vCPUs and 16GB of RAM. It’s the only VM on the host at the moment since I’m struggling to determine why the server is so unstable. Uptime has been increased from 8+ hours to about 72 hours between crashes.

My question concerns the query-cache parameter from the Configuration Wizard (and query-cache values in general). In every instance when I put in the current configuration, why does the Wizard always report query-cache-size = 0 and query-cache-type = 0? We’ve got a TON of queries that are generally repeated and it seems the query-cache would be ideal for that. I’ve rather ignored that part of the configuration and set ours up with cache-type = 1 and cache-size = 128M (up from 32M).

Mysqltuner (which I know isn’t always reliable) is reporting that in one day we had 2.2million pruned queries from the cache. Granted that was with the cache size of 32M.

What I’m trying to figure out is if the query-cache-size = 0 all queries are dropped after they are completed, which, in the case of repeated queries seems to be a bit inefficient. Other suggested configurations (from google and a couple of DBA contacts I have) say that the query cache size shouldn’t be 0 for a site like ours.

So, what gives? I know there probably isn’t a ‘right’ answer, but a deeper explanation on the pros and cons of the query-cache-size parameter being 0 or NOT being 0 would be incredibly helpful.

2

For many workloads, having the query cache off is quicker. This is not always true, so testing in your own environment is required (as with anything). The reason behind this is that maintaining the query cache can be very resource intensive, and can can quickly become a point of contention where threads start getting backed up. This is due to the fact that any time a base table is updated for a query result set in the query cache, the cached data for that query becomes invalidated.

So if you have a decent number of CRUD operations going on (i.e. inserts, updates, deletes), your system could be spending a ton of processing time simply maintaining the query cache data and not serving up cached data.

On the flip side of that, if the data that is getting SELECT’ed a lot does not change often, i.e. reference data, then the query cache might be great, but that is not very often the case for OLTP systems (more useful in a data warehouse environment where data may only change once a day or once an hour).

The other option is to to set your query cache type to 2, and only cache specific queries that you tell it to with SELECT SQL_CACHE. This would work if you have a subset of queries that could use the query cache effectively (i.e. for static data), and you can explicitly cache those queries while not caching all other queries that are hitting data that is changing frequently.