October 21, 2014

What’s the recommended MySQL version?

I see this message on our forums, and I think it’s a great question: “Which version of Percona Server is currently recommended?” It’s really the same question as “Which version of MySQL is currently recommended?” I’ll respond here and then post a link in the forum as a reply.

In my opinion, it’s important to qualify this question by understanding whether we’re talking about an existing MySQL installation, or a new one. The answer is different for each case. (There are other qualifying questions I’d ask too, but this is the biggest distinguisher).

For an existing MySQL database server, I’d encourage not jumping on a new version immediately when it comes out. Let some early adopters try it out first, and when it gets more broad deployment, then consider it. The reason I say this is that the previous version is going to be maintained quite actively for some time. You can continue to run the previous version safely and responsibly. A major upgrade should really be about features, not bug fixes or security patches; those critical fixes should be applied to the previous version. So if you’re not dying from the lack of a new feature that’s available in the new version, then there shouldn’t really be urgent pressure to upgrade.

For new deployments, I’d definitely start by using the most recent version from the very beginning, or maybe even using a release candidate of the next version if it’s available, especially if the new deployment isn’t in production yet. The reason is that I like to keep the upgrade cycle as long as possible, and if you’re going to production with something established or old, you’re going to have to look at an upgrade sooner. You might as well develop the application to the new or upcoming version in the first place. A properly tested upgrade is a fair amount of work, and it’s nothing to jump into. Sometimes I even try to skip a version if I can. A fair number of our customers are still running MySQL 5.0 and are now considering or executing upgrades to MySQL 5.5.

So what’s the bottom line?

  • For new applications, I’ve said use MySQL 5.5 without hesitation for many months now, and now I’d even say use MySQL 5.6.
  • For older applications running MySQL 5.0, I’d say upgrade to 5.5 directly, skipping over 5.1. MySQL 5.0 is really old now, and although Percona still supports it and will as long as we’re asked to, the reality is it’s slow, limited, and buggy in comparison with 5.5. Everyone on 5.0 just needs to be upgrading, even super-conservative companies.
  • For applications running MySQL 5.1, you can safely keep doing it for a while if you are very conservative, but you really need to be thinking about when you’re going to leave 5.1 for something newer. And if you’re relatively agile and don’t need a lengthy process (weeks or months) to upgrade, you should be thinking about upgrading in the near future. At this point, MySQL 5.1 is starting to get old, and MySQL 5.5’s new releases have just minor fixes for edge-case problems, not major reworking as we saw in the 5.0 and 5.1 series.

That was a long bottom line. Here’s a short one: upgrade to 5.5 or 5.6, or plan to do it in the near future.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.

Comments

  1. ngm says:

    You say that MySQL 5.0 is slow compared to MySQL 5.5. I know that InnoDB has many improvements in 5.1/5.5 but, for a server that uses mainly MyISAM, can you feel the difference between 5.0 and 5.5?

    Great article!

    Thank you!

  2. There aren’t as many major benefits at the storage engine layer if you’re not using InnoDB, but there are still lots of optimizer improvements, new functionality and features, and so forth. For a few examples: PERFORMANCE_SCHEMA, semi-synchronous replication, more functional stored procedure language, events, and pluggable authentication. If you look at the changelogs you will still see a fair amount of MyISAM-specific improvements, too. Maybe I’ll get time to write a post on that in the future.

  3. Chris says:

    Eeck. We are still stuck on 5.0 here at work. Its difficult to make a case for upgrading to the business peeps.

  4. Scott says:

    So then, might I ask you as you have proven very knowledgeable, our office sticks to what Redhat supports in their yum repos so that we can get support from them if need be. Staying true with Redhats release cycle where they release something that’s hot at release, and then never updating it until Redhats next big version release, we with RHEL5 are stuck with 5.0.77. We would have to have some solid justification to go against Redhat and install our own versions, and oftentimes that isn’t even enough. Heck, we essentially have to be told by our vendors who provide us licenses to implement their software that we MUST upgrade or else the software won’t work. Short of doing just that, what in your experience with newer versions is a compelling justification to upgrade past 5.0.77 that RHEL5 provides?

  5. rxfh says:

    There are a whole lot of improvements. To name a few I use;
    – innodb file_per_table (less heartburn with shared innodb table space)
    – Table compression (we compress our data about 55% down while improving perf due to less disk seeks)

    You can also get support directly for MySQL, regardless of what OS you’re running under. Someone who supports MySQL for a living (percona, blue gecko, Oracle etc) is going to be a lot better about giving useful support than an OS provider who has tons of packages to support. It’s about specialization – you wouldn’t ask your plumber to yank a tooth, would you? OS vendors have more of a vested interest in keeping the ‘overall’ system cohesive. I doubt they’re going to be much help when it comes to some gnarly InnoDB crash recovery, you’d have to escalate it to someone like Percona for help at that level. So what value do they really bring? Other than a checkbox that you have “support” which means very little when it all hits the fan.

    Unfortunately in our industry that checkbox often dictates what we are ‘allowed’ to run. I’d approach that with figuring out if there are some compelling reasons to upgrade ( like software becoming obsolete ), figure out a new support path and build a business case. You could peruse the changelogs between 5.0 / 5.1 / 5.5 and build a pretty strong case.

    Specific to 5.0, it’s always been a crapball. I cringe when I see it still being shipped.

  6. Jonathan says:

    @Scott RHEL 6 has been out for nine months and has MySQL 5.1.x available in its repositories.

  7. In my opinion, using the OS’s distributed version of MySQL is a grave mistake. It is rarely updated, and various OS distributions ship pretty stupid changes to MySQL, including braindead init scripts and a surprising number of server patches that are not explained or documented clearly. I would definitely use the official MySQL RPMs from mysql.com instead (or Percona Server, if you want its enhancements), and if you need support, get it from Oracle, Percona, or SkySQL. This is not a fuzzy question in my mind. If you are an app developer or a convenience user of MySQL, sure, go with whatever is in your default repositories on your own laptop. But if you’re running a real database server, use the real database server software, period.

    To my point about being rarely updated, if you used the official RHEL RPMs from their repository, then you ran on MySQL 5.0.22 for ages. That is an incredibly buggy and slow version that was obsoleted immediately and frequently while MySQL recovered from the poor-quality 5.0 initial release. It was ridiculous that RHEL didn’t update their repositories for so long, and I saw many serious downtime incidents because of bugs that had been fixed in official MySQL for years while RHEL was stuck on that old nasty version. Read the white papers on percona.com/about-us/mysql-white-papers for the details about how much downtime is caused by not upgrading.

  8. Jonathan says:

    Thanks for the free advice, Baron. I really value your opinion. I agree with you on this. I’ve been pushing for the company I work for to start using the official RPMs from mysql.com.

  9. Erik says:

    You say to upgrade from 5.0 to 5.5 directly.
    However, in the MySQL documentation: http://dev.mysql.com/doc/refman/5.5/en/upgrading.html
    It says: “If you currently are running MySQL 5.0 and wish to upgrade to a newer series, upgrade to MySQL 5.1 first before upgrading to 5.5.”

    I thought problems may arise if you upgrade from 5.0 -> 5.5

  10. The manual is being cautious, and that’s fine. In reality the upgrade from 5.1 to 5.5 doesn’t involve changes to InnoDB file formats or the like, so all that’s required is the updates to the system tables in the mysql database. I’m mainly pointing out that you don’t have to do the following, which is a pretty common thing people believe is necessary: dump, load into 5.1; dump, load into 5.5. That can take an eternity and make the upgrade process much more stressful and harder to justify to the business folks.

  11. Oh, and there are some other things like deprecated server options and SQL syntax that (finally!) no longer work at all in 5.5, and so on. Of course you want to test the upgrade carefully. But you knew that — you weren’t about to get bitten by any surprises ;)

  12. Simon Kuhn says:

    It does seem that there are a number of places still running 5.0 who have finally realized that they need to move on. I’m working for one of those places at the moment. Unsurprisingly, they aren’t too happy with their MySQL performance and tried throwing hardware at it without much impact.

    I think what held them back (up until now), more than having to dump / restore databases, was worrying about replicating between different major releases. Of course, MySQL indicates that going from an master to a slave that is one major release newer is supported (http://dev.mysql.com/doc/refman/5.5/en/replication-compatibility.html), but then you’re only on 5.1 and you have to turn around and do it all over again (plus: your compatibility testing probably only covered 5.1, now you have to start over with 5.5).

    For a replication hierarchy of 4 servers, you wind up having to have 10 different production changes to go from 5.0 -> 5.5.

    So if you’ve had positive experiences replicating from a 5.0 master to 5.5 slaves, I think that would be great to include as well. I think the more people just skip straight over 5.1, the better — that way they won’t be tempted to stop at 5.1 and stay there for another 5 years.

  13. arun says:

    MySQL 5.5 is crashing when I ran crash me tests, here is the bug filed http://bugs.mysql.com/bug.php?id=62215

  14. Simon, we’ve had clients replicate from 5.0 to 5.5 for testing during upgrades. I don’t recall a case where it hasn’t worked.

  15. Ian Rogers says:

    Hi Baron,

    Why does Percona’s own website still proclaim (proudly!) that it’s running on 5.1? I’m trying to get a site I work for to upgrade and it would certainly help the cause if Percona ran its own latest software…

  16. We are renovating a lot of our infrastructure, including a new datacenter, and upgrading to the latest version has just gotten sidelined. But in principle we like to “dog food” what we build and make sure that the we use the latest and greatest.

    By the way, Percona’s website (percona.com) has practically zero database dependency. Almost everything is just plain PHP. Part of being a database company is knowing when we can avoid using databases :-)

  17. Denis M says:

    Another important question is: what is the best OS for MySQL?
    Percona server builds for FreeBSD removed from the site, is it a hint?

  18. If the FreeBSD builds that used to be there are actually gone, it’s because we are rebuilding them after a server failed (see past blog posts for more on this).

  19. sudheer says:

    we are planing to upgrade from 5.1.43 to 5.5.? ? Can you suggest us which sub versions is good in point out performance

  20. Tony says:

    I’ve seen this server failure mentioned a couple of times; any ETA on when FreeBSD builds will be back up? Alternatively, it seems like it might be easier to just maintain a port than rebuilding for each new release…

  21. johnea says:

    Thanks so much for the guide! Skipping the 5.1 stage made an already painful upgrade a little easier.

    Unfortunatly, it wasn’t completely successful for me. I upgraded mysql 5.0.90 to 5.5.20, on FreeBSD 8.1.

    All of the databases have MyISAM tables. I don’t believe I had any InnoDB tables.

    After upgrading, I can’t start MySQL 5.5 without innodb_force_recovery set.

    If I try to start without it, there are 100 retries of the error:

    120208 13:40:35 InnoDB: Error: trying to access tablespace 1 page no. 252,
    InnoDB: but the tablespace does not exist or is just being dropped.
    120208 13:40:35 InnoDB: Error: trying to access tablespace 1 page no. 252,
    InnoDB: but the tablespace does not exist or is just being dropped.

    Then the server shuts down.

    I ran mysql_upgrade once I got the server back up, but it still requires the innodb_force_recovery to start.

    Any suggestions would be greatly appreciated.

    Thanks again for the article!

    johnea

  22. johnea says:

    Sometimes all you really have to do is ask 8-)

    Almost immediately after posting, i found a blag post on upgrading from MyISAM to InnoDB:
    http://highervisibilitywebsites.com/convert-your-mysql-database-myisam-innodb-and-get-ready-drupal-7-same-time

    This post described deleting the ib_logfile0 and 1 out of the /var/db/mysql/ directory after each change to InnoDB parameters.

    I stopped mysqld, removed the files, commented out the innodb_force_recovery, restarted mysqld, viola! It works!

  23. johnea says:

    OK, I typed too soon, when I restarted the server I’m still having CREATE TABLE issues that seem to be due to the InnoDB difficulties 8-(

    Something is deffinetly _not_ right with InnoDB after this 5.0 to 5.5 upgrade.

    Hoping someone will have an idea as to what that something is.

    Thanks Again!

  24. johnea says:

    OK, just for completion of the bread crumbs, I got this working.

    Dropping all the databases and restoring from sql archives did the trick.

    You did make sql dumps of everything before the upgrade? Right?

  25. Thanks for finally writing about >What’s the recommended MySQL
    version? – MySQL Performance Blog <Loved it!

Speak Your Mind

*