Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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): Speeding up conversion from MyISAM to InnoDB

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?

share|improve this question

migrated from stackoverflow.com Feb 6 '13 at 18:20

This question came from our site for professional and enthusiast programmers.

    
Thank you for accepting... +1 !!! –  RolandoMySQLDBA Feb 8 '13 at 18:07

2 Answers 2

up vote 0 down vote accepted

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:

share|improve this answer
    
Thanks @RolandoMySQLDBA ! I didn't even realize about multi-threading not being included in MySQL 5.1. –  alex Feb 8 '13 at 17:52

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.