Migrating from MyISAM to InnoDB

Question

I searched a bit for this topic and found some results. However, I’m looking if anyone knows or can point me to some reliable or authoritative case studies that detail the benefits (especially performance) of moving from MyISAM to InnoDB.

It’s nice if you could point to MySQL version 5.5 and higher, which has InnoDb as its default storage engine.

Answer 1

As you no doubt know from your searches, InnoDB is primarily better than MyISAM for inserting large volumes (not counting disparate features, such as foreign keys or full-text search, which are exclusive to InnoDB and MyISAM, respectively), while MyISAM is primarily useful for tables that are read more often than they are written.

However, an article from MySQL Performance Blog may be relevant to your specific interests:

High speed insert with MySQL and Innodb

To a lesser extent, this question is also: MyISAM vs InnoDB .

EDIT:

There is also a webinar on migrating from MyISAM to InnoDB , although I have not watched it. The description seems to deal with MySQL 5.5, making InnoDB default, although, again, it might be useful. You can access the webinar directly at http://blip.tv/file/4905943 .

Answer 2

This issue is actually well addressed in DBA StackExchange

https://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam

https://dba.stackexchange.com/questions/194/how-do-you-tune-mysql-for-a-heavy-innodb-workload

https://dba.stackexchange.com/questions/2885/should-i-move-to-innodb-during-a-planned-migration

https://dba.stackexchange.com/questions/5119/convert-table-from-myisam-to-innodb-without-downtime

In my years as a MySQL DBA, I personally converted dozens of MySQL databases for my employer WordPress and Drupal Clients. The immediate benefit was eliminating table locking issues in high read and high write environments.