A Tale of Two MySQL Upgrades

Sheeri

11

At the beginning of 2013, Mozilla’s MySQL databases were a mix of MySQL 5.0, Percona’s patched MySQL 5.1, Percona’s patched MySQL 5.5 and MariaDB 5.5. MySQL 5.1 was released in November 2008 – so at the beginning of the year, we still had databases with no new major features in 4 years. Currently we have almost all our databases at Oracle’s MySQL 5.6 – the only stragglers are our cluster running TokuDB and a few machines that are no longer in use. Here’s a graph showing the state of our machines – you can see that in the first half of the year we concentrated on upgrading our 5.0 and 5.1 servers to 5.5, and then in the second half of the year we upgraded everything to MySQL 5.6 (click on the image to get a larger version):

MySQL Versions in 2013

After running some tests, we determined that MariaDB 5.5 was the best option for us and our particular workload. For most of our servers, it did not matter whether we use Percona, MariaDB or Oracle’s MySQL, but our Bugzilla servers really benefited from MariaDB’s better subquery optimization, so we went with that. We had set up some Percona 5.5 servers over the spring/summer of 2012, when we moved some of our infrastructure to a new data center.

We upgraded to MySQL 5.5 to be on a recent version of MySQL. In the middle of the year, we had a choice – should we stay where we were, or should we upgrade? We had no particular directive from developers to upgrade for the new MySQL 5.6 features. However, we have been doing more and more digging into our systems, and we really wanted the performance_schema features so we could dig even more. We want to be able to parse queries in real-time, perhaps with Anemometer without having to take an offline log file and run pt-query-digest on it.

So, we chose to upgrade to MySQL 5.6. Unfortunately, there were no other GA products to test against – by mid-2013, neither MariaDB nor Percona had a GA 5.6 product, so our bake-off was functional only, not performance-related. Oracle’s MySQL 5.6 passed with flying colors, and so we proceeded to upgrade.

Now, we have a recent and consistent version of MySQL installed, that we can work with to gain insights into our systems. A pretty great goal to have been met for 2013!

11 responses

  1. Fernando Mattera wrote on :

    So Sheery, finally you choose oracle mysql 5.6?

    1. Sheeri wrote on ::

      Yes, the decision was made in the late spring. I’d thought you might have seen some updates along the way, like https://twitter.com/sheeri/status/372099683394400256.

  2. Scott Metcalf wrote on :

    Congrats, I am sure a lot of hard work went into this.

  3. Keith W wrote on :

    Sheeri could you blog a little more on the process of upgrading that you took? Did you have to dump and restore? What was the down time like? We also have a mix of boxes (mostly 5.1) and we are now trying to decide on the most efficient way to upgrade.

    1. Sheeri wrote on ::

      Keith – sure, I will elaborate, but those deserve their own blog posts…the procedures were pretty different. From 5.1 to 5.5 we did the full dump and restore, and for 5.5 to 5.6 we did an in-place upgrade, although we did run into a few issues.

      I will say we had no downtime because we are n+1 on our servers (that is, we have an extra server based on our needs, so if we need 1 master and 4 slaves on a service, we actually have 1 master and 5 slaves), so we upgraded the slave first, failed over, and then upgraded the master.

      1. Keith W wrote on :

        Thanks for the reply! If you find the time I would really like to read the additional blog posts on what you did and how it all worked out. I am always interested in seeing how others get things done.

      2. Colin wrote on ::

        Hi Sheeri!

        I’d be curious to know what issues you faced going from MariaDB 5.5 to MySQL 5.6? That in itself should be an interesting blog post.

        Thanks

        cheers,
        -colin

        1. massimo wrote on :

          hey Sherri,

          why not the dump procedure as from 51->55?? could you describe the issue you got? Do you think the procedure with full dump/restore could be more smooth?

          1. Sheeri wrote on ::

            Massimo – we weren’t expecting to run into any issues, but yes, we would not have run into them if we had gone with the full dump and restore, like we should have. There will be a blog post on the upgrade from 5.5 to 5.6 soon.

  4. Valerie Parham-Thompson wrote on :

    I’m curious if you are using GTID replication. In my test environment, I’ve seen some things I like about it, but also some things I’ll have to adjust to if we choose that. If you are using it, how do you like administration of the instances with it?

    1. Sheeri wrote on ::

      We are using it on the newer instances we spin up; it’s on 4 different clusters. So far we have had no replication problems, and haven’t had to adjust anything except for configurations, so it’s hard to say, because we haven’t had to put it to the test yet.