Categories


MySQL qCache goodness

I have to start off by apologizing, I had intended on posting this last week, but got tied up with a lot of work stuff. Our Windows server decided to throw a hissy fit, and I really hate windows. Somehow the permissions got all messed up and IIS wouldn’t read any of the web files, we spent almost three full days trying to get the permissions set back up. Finally we just gave up and decided to back up the system and re-install it.

Anyway, continuing on the theme of system optimizations, I thought I’d talk a little bit about a simple optimization you can make to MySQL, that will greatly increase your server performance. In comes MySQL’s Query Cache or qCache for short. Its already built into MySQL, all you need to do is enable it. “But caching data is bad” or, “My boss says we update our data too much to make it worth caching”. Both of these ideals are in fact wrong. I used to hold to the one that said that caching data is bad, yes, it generally is a bad idea if done improperly, but if done properly it’ll save you a lot of time.

So, MySQL’s qCache works by storing the result sets generated by select statements in memory, saving hard drive access and processor time on subsequent calls to that query. Since most websites are front-end heavy, where the end user is requesting a significant amount of data, and not modifying that data very often, storing the results of common queries in memory saves a lot of time and processor power. But what happens if you are changing data, well MySQL is smart enough to recognize when a table has changed (via an insert or update statement). Upon change to a table, it will simply drop all the data pertaining to that table from the cache and start again. So, in all, qCache doesn’t actually save anything on the first page load, in fact it takes slightly more overhead as the results have to be stored to memory, but on subsequent hits to that page, you will save a lot of time.

For example, on one of our more heavily trafficed production servers at Barking Dog, our Query cache has been running for 23 hours since its last restart. It has stored over 115,334 queries, and served up 18,810,168 requests. That means, 18 million requests have been served out of memory instead of our of the database. Or if you break it down, 227 requests per second on average.

So, you’re now no doubt thinking, hey that could save us a lot of money in expensive server upgrades. How do I install it? Well, here’s a quick tutorial, I’ll write up a full guide when I have time and put it in the guides section of the site.

First off, you’ll need to edit your MySQL configuration file (in my case /etc/my.cnf), look for the section labelled [mysqld] and add the following:

[mysqld]

query_cache_size = 50M
query_cache_limit = 1M

Restart MySQL, and you’re good to go.

Now, you’ll probably want to tweak it a bit more for your own server, for instance, the settings above allow us to store a total of 50 Megabytes of query data, in my case, more than enough, and limits the maximum data stored in one query to 1MB. Meaning, queries that return more than 1MB of data won’t be cached.

Now, you’ll no doubt want to see the results to make sure that the query cache is actually working, you can do so using one of two methods. If you have phpMyAdmin, you can simply log in and go into the “Runtime Information”. You should see a section at the bottom under “More status variables”:

Qcache free blocks 5789
Qcache free memory 18810168
Qcache hits 4161229
Qcache inserts 115334
Qcache lowmem prunes 31113
Qcache not cached 169179
Qcache queries in cache 14302
Qcache total blocks 35561

Or you can issue the following mysql command:

mysql> SHOW STATUS LIKE ‘%qcache%';

Thats it, you’re done. Enjoy the nice and fast MySQL response times.


Your Comment