MySQL – When to Switch from MyISAM to InnoDB

innodbmyisamMySQLmysql-5.5

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?

Best Answer

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:

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:

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

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:

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 !!!

Related Question