Are there any pitfalls / things you need to know when changing from MyISAM to InnoDB

Question

One of my projects use the MyISAM engine in MySQL, but I'm considering changing it to InnoDB as I need transaction support here and there.

• What should I look at or consider before doing this?
• Can I just change the engine, or should the data be prepared for it?

Answer 1

Yes absolutely, there are many things, you should test your application extremely thoroughly:

• Transactions can deadlock and need to be repeated. This is the case (in some circumstances) even with an autocommitted transaction which only inserts one row.
• Disc usage will almost certainly increase
• I/O load during writes will almost certainly increase
• Behaviour of indexing will change because InnoDB uses clustered indexes - this may be a beneficial effect in some cases
• Your backup strategy will be impacted. Consider this carefully.

The migration process itself will need to be carefully planned, as it will take a long time if you have a lot of data (during which time the data will be either readonly, or completely unavailable - do check!)