Any gotchas at all with converting from MyISAM to InnoDB?

Question

I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table will throw a warning, except the manual page for ALTER TABLE. It's that kind of thing I need to know about before converting over. I thought I'd be fine with my queries but apparently not.

Answer 1

Here are some gotchas

Memory Usage

MyISAM

• only caches index pages.
• shared keycache (sized by key_buffer_size).
• You can also set up dedicated keycache, one or more tables per cache table.

InnoDB

• caches data pages and index pages.
• one buffer pool and one size before MySQL 5.5
• 1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

FULLTEXT Indexes

MyISAM

• Supports FULLTEXT indexes

InnoDB

• Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
• Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

MySQL 5.5 and back

To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from
(
    select table_schema,table_name
    from information_schema.tables
    where engine='MyISAM'
    and table_schema NOT IN ('information_schema','mysql')
) tbl
INNER JOIN
(
    select table_schema,table_name
    from information_schema.statistics
    where index_type='FULLTEXT'
) ndx
USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.

OPTIMIZE TABLE

MyISAM

• The MyISAM table is shrunk
• ANALYZE TABLE runs index statistics on all indexes

InnoDB

• ANALYZE TABLE is totally useless because index stats are always being recollected
• With innodb_file_per_table disabled, ibdata1 will grow bigger
• With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk

Answer 2

I think the biggest gotcha would be around innodb being transactional. You'll want to know if the MySQL libraries being used by your applications auto_commit by default or not.

Python, for example, does not auto commit. This means if an application was inserting a row right before closing it's connection that insert would now be rolled back after you alter to innodb. The python script for example would need to be sure to call connection.commit();

Another point of difference could be around around multi row inserts or updates. Consider a single multi row inser

insert into tbl values (...row1...),  (...row2...),  (...rowN....);

Consider what happens if there is some kind of error such as a unique key collision on row3. With MyISAM the first two rows would have been written, under innodb all rows being written would be rolled back leaving nothing written in the even of such an error.

With innodb you will enter the world of deadlocks. These aren't inherently bad unless they're occuring with such frequency to prevent any work from being done. However your applications will need to be coded in a such a way they anticipate deadlocks and handle them appropriately (which most likely means just retry).

Consider memory/storage limitations. Innodb is a lot more resource intensive than MyISAM. If you have enough RAM to keep your buffer pools large enough to accommodate all your tables then you're golden.

Look for tables that have large primary keys. Innodb's clustered indexing means each secondary index holds another copy of the corresponding row's PK. If you have 2 secondary indexes that means each rows PK is stored 3 times (PK + each index). If the pk spans across several column and large datatypes (char(N) for example) you can see how the index requirements can quickly explode under innodb.