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.