Search code examples
mysqlupgradedbmigrate

mysql db enterprise upgrade from 5.5.42 to 5.7.11

All,

Our product uses Mysql enterprise db. currently at 5.5.42 but planning to upgrade to 5.7.latest. What changes should I expect to see ?

I'm interested in :
1) Performance impacts
2) Broken behavior
3) Changed behavior
4) Improvements -- functional and performance-wise

Is it advised to 1st upgrade our product to use 5.6.x and then next to 5.7.x OR directly to 5.7.x and then test/QA from there ? Any inputs will be valuable.

Solution

  • Performance

    Performance gains in 5.7 can be achieved only on high loaded systems. If you have less than 40-60 parallel executions, then expect small performance fall around 10%. This is very system- and load-dependent. Always test yourself! Here is good MySQL 5.7 performance test done by Percona: https://www.percona.com/blog/2016/05/17/mysql-5-7-read-write-benchmarks/

    Changed and broken behaviour, Improvements

    There were a lot of changes in 5.7. Check this link for complete list: http://www.thecompletelistoffeatures.com/

    I would mention the following changes, that one should think of during upgrade:

    1. SQL_MODE was changed. It is good for new installations, but for old systems most probably you will have to set it back to the old value.
    2. First GA release had password expiry, but after complaints it was disabled in 5.7.11. So nothing to worry about now.
    3. SQL optimizer was changed a lot. Queries should run faster now, but I expect, that some could run slower. The most time should be spent on queries testing on the new version!

    Here is good overview of all changes affecting upgrade to 5.7: http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

    Upgrade

    There are two ways to upgrade: using mysql_upgrade and mysqldump.

    1. With mysql_upgrade go with one version at a time. There is no need to test intermediary version. Test only final version.
    2. With mysqldump go directly to 5.7. It is advisable to skip mysql database. Users can not be migrated with mysqldump, use grant statements.

    For further information read this: http://dev.mysql.com/doc/refman/5.7/en/upgrading.html