Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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
share|improve this answer
add comment

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.

share|improve this answer
    
+1 about the test environment –  Derek Downey Jul 26 '11 at 14:28
    
My most concern is security fixes. MySQL 5.1 had few very serious bugs that we encountered once in four years but it was enough to give us sleepless nights. –  RPK Jul 26 '11 at 14:49
    
As far as I can tell, 5.1 is still getting security releases, but availability might depend on your OS or support package. It you are after security and stability don't forget that there is a lot to be said for using a release that has been around for a while rather than the latest and greatest. –  Jack Douglas Jul 26 '11 at 14:55
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.