Why?
The purpose of this article is to show you some tips that may help you reduce the conversion time of MySQL tables when switching to InnoDB.
Although this is not going to be a problem anymore since MySQL is using InnoDB by default in newer versions, there are still people out there using MyISAM tables which are considering upgrading to InnoDB to get some more feature or improve performance.
Why use InnoDB?
I am not going to start debating this issue, each database environment has it’s own unique needs. If you are switching from MyISAM to InnoDB make sure you know why you are doing this, and what are you hoping to gain. For example I badly needed row-level locking because most of my queries kept waiting by the table locking in MyISAM.
I recommend reading the following articles:
MySQL Performance Blog – Should you move from MyISAM to Innodb ?
MySQL Performance Blog – Moving from MyISAM to Innodb or XtraDB. Basics
MySQL Peformance Blog – Innodb Performance Optimization Basics
MySQL – Restrictions on InnoDB Tables
MySQL – InnoDB Performance Tuning Tips
Conversion
There are 2 ways to handle the conversion:
1) run “ALTER TABLE t ENGINE=InnoDB”
2) dump the database using mysqldump, edit the file and change the “engine” line to use InnoDB and re-import it back, the table will be re-created using InnoDB
I’ll go with the first method, it should theoretically be faster, but you should do you own tests.
The conversion should be very fast for small tables (less than 100.000 rows). But you need to do some planning if you need to convert larger tables.
I recommend first doing a test run of the conversion process on a server which is not in production or actively used, using a recent copy/backup of the live database. Selects are going to work doing the conversion process, but updates/inserts are going to be blocked or may time out depending on your application.
My Story
My main issue was a table with 16M rows. I started by running the conversion in a testing environment, and it took around 6 hours – a pretty long time but considered acceptable since I could run it during night and the downtime should not be noticed by a lot of users.
Before starting the conversion on the live server, I have also applied the following MySQL server parameters:
innodb_buffer_pool_size = 1536M (this should be less than 80% of the available memory)
innodb_log_file_size = 128M (beware that in order to change this parameter, you need to delete/move the log files or mysql won’t start)
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0
And don’t forget to remove these temporary parameters after you’re finished with the table conversion.
The final table conversion, using these parameters, ran for 1 hour and 11 minutes. I have no idea if these parameters account for such a large change in time (from 6h to 1h), or is just the fact that the live server had more memory available (the old myisam data files were also prefetched into memory by running dd on the .MYD and .MYI files a couple of times).