I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records)
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:
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:
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?
I'd say mysqldump is your safest option. Of course make sure you back everything up before doing things like this. I would also suggest you do the altering on a separate server cause in general such actions are utterly resource consuming.
select * into
Is a really bad idea since you would loose all indexes.
select into outfile
Would be pretty much the same as mysqldump. However it would be far more resource consuming than a mysqldump.
Personally that's what I've always done when I've needed to make a major change in a table.
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;
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:
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