I do not know if it is because we’re hosting a free webinar on migrating MyISAM to Innodb or some other reason but recently I see a lot of questions about migration from MyISAM to Innodb.
Webinar will cover the process in a lot more details though I would like to go over basics in advance. You can also check my old post on this topic about Moving MyISAM to Innodb as well as searching the blog – We’ve blogged so much on this topic.
So what are the basics ?
Regression Benchmarks – Make sure to run regression benchmarks in particular in terms of concurrent behavior. You may have hidden dependencies of MyISAM table lock behavior in your applications, also check if your application handled deadlocks well. MyISAM will not produce deadlocks for Innodb you should always see deadlocks as a probability as long as you write to the database. They may be rate but it is rather hard to guaranty you will never run into them.
Performance Benchmarks – Innodb and MyISAM have different performance properties and you can’t really say one is faster than other it is very much workload dependent, and again concurrent tests should be important here. Innodb also may result in different plans for some queries which is rather easy to check with mk-upgrade
Feature Differences – There are some feature differences between MyISAM and Innodb though well it is typically easily spotted by converting tables to Innodb on restored backup. Full text search indexes, GIS, multi-column auto increment keys are great examples. There are also different limits for MyISAM and Innodb – it is possible to have some rows which can be stored in MyISAM but would not fit to Innodb, though this is an exception.
Space Innodb Tables tend to be larger. Again converting schema will show you some of this. Though the best is to take a look at the size after stressing system with load for a while as depending on the schema and usage Innodb tables may increase in size significantly due to fragmentation.
Usage Differences What works well for MyISAM may not work for Innodb and vice versa. You may benefit from different index structure for Innodb, including different primary key setup, as well as you may want to structure workload differently. With MyISAM it is often for people to do updates in small chunks, almost row by row to avoid holding table lock for long time in Innodb you want larger updates to reduce cost of transaction commit. You also may want to avoid excessive SELECT COUNT(*) FROM TBL (with no where clause) which is very fast for MyISAM but does table/index scan for Innodb.
Defaults You need to know two things about defaults for Innodb. First Depending on MySQL version they may be somewhere from suboptimal to absolutely disastrous, you do not want to try to run Innodb or XtraDB with them. Second Innodb is tuned to be ACID by default – if you’re moving from MyISAM often you do not need such strong guarantees and can at least change innodb_flush_log_at_trx_commit=2. It still will be much more secure than storing data in MyISAM. The 3 most important values to check are innodb_flush_log_at_trx_commit, innodb_buffer_pool_size and innodb_log_file_size. There are a lot more options for fine tuning but make sure at least these are right.
Great overview, thanks for putting this together! The chapter “Limits on InnoDB Tables” in the reference manual is also worth a read:
http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html
By the way, you might want to elaborate on the “GIS” part in your “Feature Differences” list. Since MySQL 5.0.16, InnoDB is certainly capable of storing spatial data types as well. However, it does not support spatial (R-Tree) indexes, which are required to speed up lookups.
Hi!
Thanks for a great blog!
In my application I have some performance problems. One of ideas for improvement was to change MyISAM to InnoDB, since I have 10 processes running insert/updates very often. I’m trying to figure out if row-locking instead of table locks can benefit in this case. However if Table_locks_waited is small percentage ( less than 1%) of Table_locks_immediate it looks for me that other things need to be improved first, right?