Restoring an old backup to latest MySQL release

Question

Our old database is MySQL 5.1 server and is four years old. If I uninstall MySQL 5.1 Server and install the latest release and restore the backup, will I gain performance benefits? I guess the new version release says that there are many performance benefits.

Answer 1

IMHO, when it comes to MySQL 5.5, you will definitely see much better performance from InnoDB than in past releases because dirty pages in the InnoDB Buffer Pool do not linger as long. Those dirty pages are flushed more robustly.

You also have the luxury of creating multiple InnoDB Buffer Pools (See innodb_buffer_pool_instances and innodb_buffer_pool_size). To reduce thread locking amongst the buffer pools, make sure set innodb_thread_concurrency to 0 to let InnoDB storage engine decide best how to handle thread allocation.

CAVEAT ON MIGRATION TO MySQL 5.5

Make sure you mysqldump all databases EXCEPT the mysql schema. There is a cleaner, crisper way to migrate User Grants out of the MySQL Schema. In fact, there are two options for accomplishing this:

OPTION 1 : Use mk-show-grants

This dumps out all the MySQL Grants as SQL statements, which is completely portable to any MySQL 5.x instance.

OPTION 2 : Run these commands (My personal emulation of what mk-show-grants does)

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Answer 2

It depends. You might see performance benefits, you might see worse performance. For example, a new feature might mean the planner chooses a different kind of join in a crucial query - the impact of such a change in the real world of your application cannot be predicted with certainty (planners make educated guesses, not definite calculations).

So if you are planning a migration from one version of any RDBMS to another, you need to have adequate planning and time to test performance after the change in a separate 'test' environment that mirrors production as closely as possible.

On the positive side, very often new releases do improve database performance, but in no way should that give you a false sense of security unless your customer is very unimportant to you.

Answer 3

As Jack Douglas mentions, it really depends and whatever you do, make sure you test it out before putting it in a production environment.

But your mileage WILL vary, depending on the storage engines you use, which features you use, and so on. You can check out the changes of MySQL 5.5 for more of an idea what is new, and what has changed. For instance, innodb received some performance boosts (but MyISAM was default in 5.1, so that may be what all your tables are).

As a side note, always read the process of updating to make sure your upgrade goes smoothly. Pay attention to the specific conflicts that might arise from upgrading from 5.1 to 5.5.