Speeding up conversion from MyISAM to InnoDB
I have a MySQL 1.5 GB MyISAM-table (1.0 GB data, 0.5 GB indexes) in production which I'm about to convert into InnoDB.
Since the table is used in production I'd like to make the downtime as short as possible.
My questions:
What MySQL configuration options should be adjusted in order to speed up
ALTER TABLE table_name ENGINE=InnoDB;?What other tricks can be used in order to speed up conversion of a production database table from MyISAM to InnoDB?
Answer
- Setting a large innodb_buffer_pool_size (2GB or more)
- preread your old myisam data/index files using shell commands
- increase innodb_log_file_size (256 MB)
- Do the alter table in X parallel threads, where X is the qty of CPU cores on your server
- other minor tweaks for conversion only (innodb_doublewrite=0, innodb_flush_log_at_trx_commit=0)
setting innodb_buffer_pool_size as high as possible is the typical way to speed up innodb tables creation - your dataset looks like it could fit inside a 2GB innodb buffer pool, so any decent 64 bits server should allow that. alter table type=innodb is also faster than dump+reimport solution, and is easy to run in parallel.
Also make sure you have increased the innodb_log_file_size from the default of 5Mb to 128 or 256MB. Careful with that, and it needs a clean shutdown + erasing the old ib_logfile*.
If your server has something like 8GB of ram, and that you run a 64 bits version of mysql I would suggest a 2GB innodb_buffer_pool, and you can even preread the old MYD and MYI files before closing for downtime, so that they will be in the OS's page cache when the real work starts.
If you also go for the minor tweaks, please keep in mind that you need to undo them after the conversion (another small downtime) to have your data safe, I doubt they are worth it for such a small dataset though.
Good luck.