Upgrading support.mozilla.org databases

Sheeri

9

A while ago (November 2012 to be exact), we upgraded the support.mozilla.org databases from Percona 5.1 to MariaDB 5.5 (the next step, happening soon, is upgrading them to Oracle’s MySQL 5.6). One of the engineers and I had a conversation where he mentioned that “one of our worst performing views on SUMO is doing waaaayyy better with the upgraded databases”, that it “seems more stable” and that “I stopped receiving ‘MySQL went away or disconnected emails’ which came in once in a while.”

It’s always nice to see upgrades actually making a difference. In our case we saw a lot less CPU wait, though that might also be partially due to tuning the memory settings on the machines and adding in another read slave to handle queries. As a result, network traffic throughput went from less than 1 Mb/sec to about 18 Mb/sec, because the machines were just handling more queries per second, period.

(I had this e-mail as a draft for a while and decided to clean it up and publish it now!)

9 responses

  1. Abe wrote on :

    Nice! I don’t really know why is it so hard to realize that MySQL 5.6 is WAY better then MariaDB 5.5 . . . And I bet my ass that it will be even better that the MariaDB 10 series

    1. Sheeri wrote on ::

      Well, to be fair, MySQL 5.6 is not comparable with MariaDB 5.5. And MariaDB 5.5 is much better for subqueries than either Oracle’s or Percona’s MySQL 5.5, which is why we chose it. We haven’t yet tested what’s better for our needs in the 5.6/10 series, but so far it looks like Percona does not have a lot of added stuff (other than XtraDB in general), and I don’t think we’ll want/need what MariaDB 10 puts in, so I believe we’ll end up with Oracle’s MySQL 5.6, but we do have to test things first.

  2. Wlad wrote on :

    So, to quickly summarize :
    1) You did not test MySQL 5.6
    2) Your users are happy with MariaDB 5.5,
    3) You will migrate to MySQL5.6 in the nearest future (next step, happening soon)

    1. Sheeri wrote on ::

      Not exactly.

      1) Our users were happy with Oracle’s MySQL 5.0.
      2) We upgraded to Percona’s MySQL 5.1 for speed improvements.
      3) Our users were happy with Percona 5.1
      4) We upgraded to MariaDB 5.5 for speed and security improvements, and new features like performance schema.
      5) Our users are happy with MariaDB 5.5
      6) We will definitely upgrade to MySQL 5.6, to take advantage of the PERFORMANCE SCHEMA, so query digests are much easier to perform.
      7) We will test what works best for us – Oracle’s MySQL 5.6, Percona’s MySQL 5.6, or MariaDB’s 10. Preliminary thoughts based on reading about the featuresets of all three point to Oracle’s MySQL 5.6 being the best solution, but we have to actually test it. MariaDB 10 looks great for the features it has, but we do not need things like Cassandra integration at this point in time. If MariaDB 10 actually works better even if we’re not using the special features, we’ll use it.

      1. Wlad wrote on :

        If I’m happy, I wish that nothing changes. Perhaps that is not normal.

        1. Sheeri wrote on ::

          If you’re happy with MariaDB, why are they changing it? They are adding new features and making it better.

          We constantly strive to make things better. With MySQL 5.6, the DBAs can provide a better level of service because we can do a more thorough and more relevant query review (http://en.oreilly.com/mysql2010/public/schedule/detail/13267) in much less time.

          So I guess not ALL the users are happy, because right now there are missing tools that people don’t know about. There is always room for improvement!

          1. Wlad wrote on :

            “If you’re happy with MariaDB, why are they changing it? ” Not sure I understand.

            First I’m a MariaDB developer, not a user, thus the question whether I’m happy with it is not relevant. And I’m changing it because different users are not entirely happy and want either something new, or they file bugs,. If everyone would be very comfortable with the current state of art, then I’d rather switch to something different.

        2. Sheeri wrote on ::

          Yes, that’s it exactly – the users (in this case, our developers) don’t actually know the new features. A lot of the new features are making queries faster without having to change the queries at all, so upgrading improves the user experience. We are always striving to improve the experience for anyone using databases at Mozilla, so we upgrade.

          That was the reason to upgrade from Percona 5.1 to MariaDB 5.5. The reason we want to go to MySQL 5.6 is the performance schema improvements and the fact that we can much more easily do query and schema reviews, again to provide a better experience for our users (developers).

          So we do not just upgrade because there is something new out there. We evaluate if the new features/bugfixes are appropriate for our needs, and if they are, we upgrade.

        3. Shlomi Noach wrote on ::

          @Wlad,
          Reasons to upgrade might include:
          – Better maintenance features (e.g. GTID should make life easier with promoting slaves). The users are happy, the DBAs may not be so happy
          – Better monitoring capabilities: PERFORMANCE_SCHEMA has a lot more info (I’m personally unfamiliar with it as yet). If this helps diagnosing your occasional database hiccup — then the DBA is happy (even thought the users may be happy nonetheless)
          – The users can be happy about using MyISAM’s FULLTEXT; but when the table crashes they will not be so happy. If InnoDB offers the same, the users will keep being happy.
          – Older versions do not get as many updates. This is not specifically about MariaDB; in fact I had a case with Percona Server gone old, which all of a sudden started crashing every few days (afetr working for 2 years without problem). It was my conclusion that a system update caused this; a minor upgrade to Percona Server solved the problem.