Speeding up conversion from MyISAM to InnoDB

Question

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?

Mallon

Answer 1

• 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.

Michaelamichaele

Answer 2

If you're after a fast (though somewhat lo-fi) solution, you could simply export the data to text file (via mysqldump), change the table type to InnoDB in the resultant text file and then re-import the data.

That said, you'd need to test this by importing into a different database to ensure there are no issues.

Thymelaeaceous

Would that be faster than ALTER TABLE table_name ENGINE=InnoDB;? Why is that? – Mallon

It might be faster as it doesn't have to rebuild the indexes in the same way. (If you look at the dump file you'll see that it turns of indexing, does all of the inserts and then re-enables indexing on a per-table basis.) That said, I'd imagine the ALTER TABLE would do this as well to be honest. – Thymelaeaceous

Re-enabling indexes after loading dump takes forever on InnoDB, so it wouldn't be much faster - I've tried that already. – Cooke

The faster way so far: dump / change ENGINE / restore db – Guggenheim

Answer 3

The table will only be inaccessible for writes; reads will continue to access the old MyISAM table for the duration of the ALTER.

Seriously, rebuilding a 1.5G table should not take long, if your app cannot tolerate that amount of downtime, you should have some HA system in place already that you can use to do this. Presumably your technical support team can put out a notice to tell users about the downtime and given sufficient warning, you will do this at a quiet time of day/week (We normally find sunday morning to be a good time, but that may vary if you have a lot of customers in Muslim countries)

You can find out how long it will take by running in on the table with the same size of data in it on your non-production system of the same configuration and spec, which you doubtless have for performance testing.

Coquillage

Answer 4

Using pt-online-schema-change would render your problem irrelevant. pt-online-schema-change is a command-line tool designed by Percona (arguably the top MySQL consultancy in the world) to solve this very problem. It allows you to conduct ALTER statements on any table without blocking either reads OR writes, which is likely your ACTUAL goal if you say you are trying to speed up this conversion in production.

After installing the Percona Toolkit, you would simply run the following command in your O/S shell:

$ pt-online-schema-change h=your_host.com,t=your_db.your_target_table --alter "ENGINE=InnoDB"

Ester