Upgrading MySQL can be a bit of a leap of faith. You hope for everything to go well but really don't want to rely on mere hope to ensure that you don't find yourself with a nasty surprise late one night.
Every time I talk to a group about MySQL related topics, I try to find out what they’re currently running in production. Much to my surprise, even today, it seems that the vast majority (80-90%) are running a version of MySQL 5.0.xx. And most either haven’t seen a truly compelling reason to move to 5.1.xx and now find themselves on the fence, waiting to see if 5.1.xx or 5.4.xx is the way to go.
Someone recently asked me how I was planning to test and upgrade from MySQL 5.0.xx to MySQL 5.4.xx. That seemed like a useful topic to dig into this week.
Even if you’re not interested in many of the new features in the 5.1 or 5.4 releases, there are some good reasons to consider upgrading–especially if you’ve upgraded hardware in the last year or so. Newer versions of MySQL give you the ability to use the InnoDB plugin which boasts the following:
- fast index creation (finally, no need to re-write all data when you add/drop an index)
- better locking to scale on machines with many cores
- more control over performance parameters, some useful if you’re migrated to solid-state storage
- new row/file format
- per-table data compression (great for text columns)
- …and lots more
The great thing about the move to a newer InnoDB is that you can take advantage of some of the benefits without having to change anything in your configuration–just upgrade. And once you’ve done that, you can make incremental changes down the road that will help even more. For example, you may upgrade and watch things for a few weeks before you decide to enable compression on a table or two. And then after getting comfortable with compressed tables, you might start dig into some of the more serious performance tuning that the InnoDBplugin exposes.
In testing an upgrade like this, there are a few dimensions I’m interested in:
- data consistency
- query consistency
Data consistency means that an upgraded server will have the same data stored at a given point in time as a non-upgraded server. There should not be any strange drift, missing records, or extra records.
Query consistency is about making sure that queries will return the same data on an upgraded server as on a non-upgraded server.
Stability is simple. The newer server should not crash any more than your existing server–hopefully that’s never!
Performance simply means that the upgraded server should be faster than your old server–or at least as fast. This is not just about overall throughput–it means that individual operations that were previously fast still are.
If the upgraded MySQL server is consistent from a data and query point of view, does not crash, and is faster than the old one then I consider it a successful upgrade. It’s that simple–in theory.
Now, how do you go about trying to verify these things?
First of all, it’s worth noting that you can put a lot of time, energy, and money into this sort of testing. Every organization has a different notion of how much is “reasonable” given the environment, data, and users. What I propose here is a method that isn’t excessively demanding while also being comprehensiveenough to catch most potential problems before a full deployment.
I’m assuming there’s no good automated testing system in place already–something that can compare a set of tasks run against two MySQL servers. Smaller and mid-sized organizations are not likely to have developed such a beast yet.
So, unless there are compatibility issues listed in the documentation, I always plan to upgrade a singe slave in a MySQL cluster to test a new release. There aren’t many cases when a newer slave can’t replicate from an older master, so this is a safe choice. Once I’ve done that, I first want to just let the server run for a while (maybe a day or so) without any clients hitting it. This lets me ensure that replication is still happening and there’s nothing horribly wrong. I may check the server’s error log a few times just in case.
After I’m happy that it’s replicating, I’ll pull out a copy of mk-table-checksum (from Maatkit, which I wrote about earlier) and try to verify that the data on the salve is the same as on the master.
Next, I’m going to start sending live read queries to the slave so that I can see how it responds. I’ll check monitoring tools, watch the error logs, and make sure that nothing strange appears to be happening at the application layer. From past experience, I’m not too worried about MySQL returning wrong results–it’s a pretty well tested product. But I have seen circumstances where the query optimizer changes enough between versions that it causes issues. Queries still return the same (correct) data, but sometimes the server selects a query plan that’s slower than the old one, thus turning previously fast queries into real problems.
Over the next few days, I’ll continue checking on the average response time of the new server and comparing it to the others. I’ll watch logs and just sniff around for any anomalies.
Now if there are different clusters or different “roles” that slaves can serve, I’ll try to test in as many of those as possible too.
If everything still looks good after all that, I’ll consider actually making changes to the configuration. Maybe I’ll convert some InnoDB tables to the new Barracuda file format. Once that’s done, I’ll repeat the process above: first just let it slave for a while, then run some consistency checks, and finally let it serve some real read query traffic.
Supposing that also goes well, I might consider enabling compression for some of my larger tables with TEXT columns. I’d repeat the process once again to do so. Ditto foradjusting some of the new performance knobs and levers that InnoDB exposes.
Upgrading from one major release version of MySQL to another is not the sort of task you want to do in a hurry. It’s important to validate that it behaves as expected and doesn’t cause any major performance or stability regressions in your systems. Using the techniques here, you can get a sense of what an upgrade will be like in your own environment.
You could easily skip a bunch of testing and make all the changes at once–and it might work. But if it doesn’t, you’re left wondering which change was the problem. The file format? Compression? Something else?
How have you handled testing MySQL server upgrades in your environment?