It’s been a while since I’ve posted anything new. In the meantime I’ve been pretty busy. Working on production systems often means that you are not running bleading edge. That’s fine but sometimes you need to look at doing the upgrade and to do that you have to do quite a few checks to see how well newer versions of the software you use will work.
I’ve been in that situation with MySQL. I have quite a few boxes most of which are 5.0 and have been working fine. MySQL-5.1 has been GA now for some time, and I have started to look at it as support for 5.0 is about to end. I’ve already found quite a few 5.1 bugs, good enough to crash the server, but again that’s to be expected. Heavy loads and odd usage of the server means that MySQL can never be able to test all things. Replication problems caused a few issues with the first versions of 5.1 but they all seem to have been ironed out now. The other main headache I experienced was upgrading one specific mysql 5.0 instance. It proved quite painful. Turns out that this is mainly due to problems if you have stored procedures or triggers and the rather awkward process you need to go to upgrade in these circumstances. Something to go into more detail at another time, but improving the upgrade procedure is something that MySQL should really look into. My conclusion is that people just don’t use triggers or stored procedures, except at least for very simple stuff. Perhaps those that do like to suffer in silence?
Those of you who do not have triggers or stored procedures of course will wonder what all the fuss is about. Other boxes I’ve upgraded just work without almost any effort at all. Really, really easy. So just be aware that your mileage may vary.
One thing is getting the upgrade done and seeing that things seem to work fine. For me many of the boxes are replication slaves so it is necessary to check that replication works too. The final task is to check the box under load. That is the real test of any new version of software. See how it goes under real load and compare the numbers. If you are lucky you get extra features AND better performance. If you can: load the box until it breaks. Do that also with the old version on the same hardware and then you can see how they compare. In spite of all these synthetic benchmarks you can run, realistic testing is so much more convincing.
So that is what I’ve been doing by making a simulated load from web users and applying it to a MySQL server. I was not sure what to expect and some contacts had reported worst performance on 5.1 compared to 5.0. I have been pleasantly surprised. MySQL 5.1 does perform a lot better than 5.0, and while I am not looking to use it, 5.4 is so much better still. That is, the same hardware can take about 20-30% more load on 5.1 vs 5.0 and about the same again on 5.4 vs 5.1. I would like to use 5.4 but it is not going to be GA so I will have to wait for 5.5 or whichever version MySQL decides to push out after 5.1. Nevertheless, it is nice to know that things are better and there is more improvement to come in the future. It may be worth pointing out that these tests were done on new 16-core machines with quite a lot of memory (48 GB). It’s been known that 5.0 does not scale well past 4-way servers, and 5.1 has some limitations as the number of cores increases. Sun has been trying to fix this with 5.4 and it shows. Good stuff.
Moving to 5.1 looks promising and is worthwhile. If MySQL can get some of the 5.4 performance improvements out soonish then that will be even better. Do your experiences with the newer versions of MySQL co-incide with mine?