MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)
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:


Install MySQL 5.6
Install MySQL 5.1's InnoDB Plugin if you want to keep MySQL 5.1 (Applicable to MySQL 5.1.38+)


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


May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance