Your database is one of the most important aspects of your server software stack. Call me old fashion, but I still rely on MySQL as my database of choice and don’t see myself changing any time soon. Over the past 7 years of running FantasySP, I’ve learned quite a bit about maintaining MySQL and making sure my server is running as smooth as possible.
Keeping MySQL Up to Date
The most obvious way to ensure MySQL performs its best is to make sure that your server software is up to date. Updating to the latest minor release of MySQL should be one of the safer routes to go in. These fix bugs, security, and minor performance improvements. You should update to the minor release versions on a pretty regular basis.
A major release version upgrade is much more involved. If you see that MySQL is nearing its end of life, then it’s time you start planning. I have done 2 major upgrades from 5.1 -> 5.5, and then from 5.5 -> 5.6. Chances are you will encounter problems with query performance with a major release update. MySQL Query Cache Optimizer can behave very differently depending on what major release version you are using. Queries that ran fine on 5.4 will run poorly on 5.5.
When Upgrading MySQL to a New Major Release
In order to safely upgrade to a newer MySQL major release version you should first make sure that you are running something like NewRelic to monitor your query performance. Get a good baseline for typical application performance so you can compare it to when you upgrade.
To be safe, you could be running the old & new version of MySQL on your development box if you can. This way you can plug in two queries with the same dataset on two different version of MySQL and get an idea of performance beforehand. I usually don’t do this and just deal with the shitstorm that ensues, but some of you may want to play it safe.
Usually when a query performs badly it is because the newer version of MySQL decided to ignore the indices that were previously created for a table. I have seen this happen quite a few times. If you are running a vanilla WordPress installation then you probably won’t have to deal with this, but custom apps will probably encounter this problem. You’ll be able to see bad query performance when you use EXPLAIN on your troublesome query. Full table scans lead to terrible performance, so don’t be alarmed if this happens.
To fix this you may have to develop a better index to speed up performance, or you can force MySQL to use a specific index with the following query:
SELECT * FROM table_name USE INDEX (names) WHERE last = ‘something’
Another reason an updated major release of MySQL runs poorly could be that your configuration file is outdated. Sometimes they may change the names of something or remove it and MySQL will start with Query Cache disabled or default settings. Make sure you save a backup of your original my.conf file.
Useful Scripts to See Performance
Using NewRelic is great, but there are two popular database scripts to help with diagnosing performance issues. You have probably heard of them, but I just want to double check you have them. One is mysqltuner.pl and the other is tuning-primer.sh.
They will tell you how your cache is performing, if you have full table scans, how many prunes are happening per day, if your query cache is big enough, etc. All very useful stuff to determine performance. I am assuming you know how to deal with fixing those issues, but if you don’t you can Google them or buy a MySQL book or two to learn more.
Software to Manage & Run MySQL Queries
My development environment of choice is Windows. With that in mind, I like to run a local MySQL application to make my life easier to diagnose queries, create tables, indexes, synchronize data, etc.
My software of choice is SQLyog and can’t say enough about how awesome it is. If you deal with custom MySQL queries a lot, then I strongly suggest using an application similar to this to make your life easier.
Pruning Old Data
MySQL databases tend to be like old attics where you just keep saving shit even if you don’t actually need it. If you have data from 2011 that you don’t need then make sure you get rid of it.
If you properly prune your database on an annual basis then you can extend the life of your current hardware stack, save money, and have much better performance. A smaller database means less space for MyISAM indexes, which means you need less memory for caching. (Assuming you use MyISAM)
So the big question is, how do you prune old rows in a table with 20 GIG worth of data without using DELETE queries? We all know that DELETE queries are way too expensive to run at this scale. The solution? Create a new temporary table with the same indices and table names. Then copy the data you need from the old table to the new table. Once your done, DROP the original table and rename your new table to the old table.
I learned this trick from Sean at the Clicky blog and trimmed the size of my database in half.