- Why does converting tables from MyISAM to Innodb cause performance problems?
All storage engines have different performance properties and even though MySQL provides the same basic functionality for all storage engines, changing storage engines should not be taken lightly from a performance standpoint. The list below includes the most common causes of performance regressions while converting to Innodb and includes some tips related to them:
- Innodb tables typically have larger memory and disk footprint, so disk performance requirements and size of memory grows to maintain the same level of performance.
- Innodb tables are much more sensible to server settings. Therefore, make sure innodb_buffer_pool and innodb_log_file_size are adjusted to reflect your load and hardware configuraton.
- Innodb tables are clustered by the primary key. If you're performing operations with random primary key values, they may be slower than MyISAM tables. This especially applies to inserts.
- LOAD DATA and Index Build operations are not optimized for Innodb and always performed row by row, instead of by sorting the data.
- Innodb uses the primary key as row pointers for Secondary indexes, so Index long primary key may decrease performance significantly, especially for key accesses.
- Innodb uses synchronous disk I/O requiring the disk to report that data is actually written to the plate before it can consider operation completed. This increases data security, but reduces performance, especially on lower end disk sub-systems.
- Innodb is a transactional and multi-versioning storage engine. This means it has to deal with the appropriate overhead such as maintaining a transaction log and undo tablespace with previous row versions.