I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records)

  • I'm going to be converting this table to InnoDB in a production environment.

The DB is on a cloud server with 8GB of RAM (will likely upgrade to 30GB RAM just for the conversion).

One method to do this is to simple write an ALTER query:

What settings and configs would be optimal to convert in this way as quickly and smoothly as possible?

Some of the suggested settings I've read:

  • large innodb_buffer_pool_size (is 4GB the largest possible?)
  • preread your old myisam data/index files using shell commands
  • increase innodb_log_file_size (what's best size in the case?)
  • Do the alter table in X parallel threads, where X is the qty of CPU cores on your server (how can you do this?)
  • other minor tweaks (innodb_doublewrite=0, innodb_flush_log_at_trx_commit=0)

Note: Similar question here (but much smaller table example with different server size): https://stackoverflow.com/questions/2081823/speeding-up-conversion-from-myisam-to-innodb/2081871#2081871

Of course, there are other methods to do this like:

  • Do a MYSQLDUMP of the old table and reinsert into a new clean InnoDB table,
  • Run "SELECT * ... INSERT INTO... " query, or
  • Run "SELECT INTO OUTFILE..." query on old table and then a "LOAD DATA INFILE... " query into the new table

1. How would you configure your settings for the purpose of making this alter go as quickly and as smoothly as possible?

2. Which method do you think is the best alternative?

| |