Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

Here are some gotchas

Memory Usage

MyISAM

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

This means you cannot convert MyISAM to InnoDB. 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 MySQL 5.6 goes GPL

OPTIMIZE TABLE

MyISAM

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

InnoDB

share|improve this answer
    
Thanks for the query, small typo though: "engin" instead of "engine" –  Andrew Jan 10 '12 at 14:20
    
Fixed typo. Thanks, @Andrew !!! –  RolandoMySQLDBA Jan 10 '12 at 15:16
    
@RolandoMySQLDBA: You can add that InnoDB does not have spatial indices. –  ypercube Jan 30 '12 at 22:05
add comment

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|mysql-python.sourceforge.net/FAQ.html#my-data-disappeared-or-won-t-go-away], 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.