MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

backupinnodbmyisamMySQLmysqldump

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?

Best Answer

RECOMMENDATION #1

Let's presume you have a fair amount of disk space on your cloud server. You create the InnoDB table in stages to watch the effect of diskspace and RAM in slow motion.

Let's say your MyISAM table is called mydb.mytable and you want to convert it to InnoDB.

Try the following:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ENGINE=InnoDB;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Play with the table.

If you like its performance, then

DROP TABLE mydb.mytable_old;

If you do not like its performance, then

TRUNCATE TABLE mydb.mytable_old;
INSERT INTO mydb.mytable_old SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_zap;
ALTER TABLE mydb.mytable_old RENAME mydb.mytable;
DROP TABLE mydb.mytable_zap;

RECOMMENDATION #2

You mentioned you are running MySQL 5.1. Unless you install the InnoDB Plugin, InnoDB is single threaded. To get InnoDB to be multithreaded:

Once you do either one, please configure InnoDB to engage multiple cores. See my past posts:

Related Question