We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM.

My original plan was to upgrade the 5.0 master to 5.5 and then convert to InnoDB. Now I’m wondering, would it make more sense to start before the upgrade? I’ve been wary to since I’ve heard that InnoDB under 5.5 is much faster than InnoDB under 5.0. Is this unfounded, and should I start now?

If you need concurrency of heavy UPDATEs and INSERTs, you will want InnoDB

If you need deadlock resolution, you will want InnoDB

If you want a storage engine that caches both data and indexes, you will want InnoDB

If you want to access multiple CPUs effective, you will want InnoDB (and tune it to do so)

Please refer to my past articles on InnoDB:

• Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?
• Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
• Aug 04, 2011 : Optimizing InnoDB default settings
• Oct 05, 2011 : Query runs a long time in some newer MySQL versions
• Dec 19, 2011 : Which first: upgrade mysql version or convert storage engine?
• Jan 09, 2012 : Any gotchas at all with converting from MyISAM to InnoDB?
• Jan 27, 2012 : importing myisam 5.0 database into a 5.5 innodb server
• Jan 18, 2012 : Insert-heavy InnoDB table won’t use all my CPU
• Jan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?
• Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

You said you heard that MySQL 5.5 is faster than MySQL 5.0. Out of the box, that’s not true. YOU MUST TUNE MySQL 5.5 TO GET GREAT PERFORMANCE OUT OF IT !!! Here are my past articles on that subject:

• Jun 19, 2011 : How do I properly perform a MySQL bake-off?
• Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
• Jul 18, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

You mentioned MyISAM being faster than InnoDB. If there are a lot of wide VARCHAR columns in the MyISAM with the default ROW_FORMAT=Dynamic (Default), that can degrade MyISAM performance. Saying one storage engine is faster than the other depends on the application, read/write I/O ratio, storage engine tuning.

See my posts around this subject

• Sep 20, 2011 : Best of MyISAM and InnoDB
• April 12, 2012 : Mysql: Insert performance INNODB vs MYISAM (Not My Post, but good answers are given)
• May 02, 2012 : Which mysql storage engine to choose?
• May 03, 2012 : Which is faster, InnoDB or MyISAM?

Addressing your issue of whether to convert now or later to InnoDB:

In the fifth link I provided ( Which first: upgrade mysql version or convert storage engine? ), I recommend converting first. However, in your particular case, I would recommend converting later. The reason is simple: You are moving the data to a different server. MyISAM tables are very easy to port. Doing an rsync or scp of /var/lib/mysql is more expedient (faster) that converting to InnoDB because the data will be slightly (sometimes significantly) big when existing as InnoDB.

There are two things you will need to do:

NEED #1

If you rysnc/scp /var/lib/mysql you will overwrite /var/lib/mysql/mysql. Therefore, I propose you move your data like this:

• STEP01) On the MySQL 5.5 machine, mkdir /root/mysql_schema
• STEP02) On the MySQL 5.5 machine, cp /var/lib/mysql/mysql/* /root/mysql_schema/.
• STEP03) rsync or scp /var/lib/mysql from MySQL 5.0 Server to MySQL 5.5 Server
• STEP04) On the MySQL 5.5 machine, cp /root/mysql_schema/* /var/lib/mysql/mysql/.

• STEP05) Get the grants from MySQL 5.0 machine as pure SQL and save the file as MySQL50Grants.sql

  • Jul 26, 2011 : Restoring an old backup to latest MySQL release (See the CAVEAT)

  • Jan 27, 2012 : importing myisam 5.0 database into a 5.5 innodb server

  • Jun 13, 2012 : Fastest way to move a database from one server to another

• STEP06) Execute the MySQL50Grants.sql on the MySQL 5.5. Server

NEED #2

You need a straightforward script to convert MyISAM to InnoDB. Please run the following on the MySQL 5.5 DB Server :

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql

When you are satisfied with the conversion script, login to mysql on MySQL 5.5 and run

mysql> source /root/ConvertMyISAMToInnoDB.sql

Give it a Try !!!