Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a table with about 300M rows in MyISAM format I want to convert to Innodb

My original goal was to reduce usage by changing the table schema to have simpler indexes. I dumped all the table, dropped it, recreated it with less indexes, and am now reimporting. However, I forgot to specify that it should be innodb instead of myisam.

Can I just do the standard ALTER TABLE ... ENGINE=INNODB? Is there anything special I should be aware of with such an enormous table?

The data import operation is taking around 12 hours -- I'm loathe to do that again. Hence why I want to just convert it.

share|improve this question
    
With 300M rows, MyISAM is probably a faster and better fit for a table of that size anyway! –  Mark Henderson Aug 9 '09 at 21:39

2 Answers 2

up vote 7 down vote accepted

The following exerpt came out of the book "High Performance MySQL, Second Edition".

This is an excellent book and I would recommend it to anyone.

The short answer is:

With your table size and conditions, no matter what method you choose, I think you're potentially in for a long wait.


Table Conversions

There are several ways to convert a table from one storage engine to another, each with advantages and disadvantages.

ALTER TABLE

mysql> ALTER TABLE mytable ENGINE = Falcon;

This syntax works for all storage engines, but there’s a catch: it can take a lot of time. MySQL will perform a row-by-row copy of your old table into a new table. During that time, you’ll probably be using all of the server’s disk I/O capacity, and the original table will be read-locked while the conversion runs.

Dump and import

To gain more control over the conversion process, you might choose to first dump the table to a text file using the mysqldump utility. Once you’ve dumped the table, you can simply edit the dump file to adjust the CREATE TABLE statement it contains. Be sure to change the table name as well as its type, because you can’t have two tables with the same name in the same database even if they are of different types—and mysqldump defaults to writing a DROP TABLE command before the CREATE TABLE, so you might lose your data if you are not careful!

CREATE and SELECT

The third conversion technique is a compromise between the first mechanism’s speed and the safety of the second. Rather than dumping the entire table or converting it all at once, create the new table and use MySQL’s INSERT ... SELECT syntax to populate it, as follows:

mysql> CREATE TABLE innodb_table LIKE myisam_table;
mysql> ALTER TABLE innodb_table ENGINE=InnoDB;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;

That works well if you don’t have much data, but if you do, it’s often more efficient to populate the table incrementally, committing the transaction between each chunk so the undo logs don’t grow huge. Assuming that id is the primary key, run this query repeatedly (using larger values of x and y each time) until you’ve copied all the data to the new table:

mysql> START TRANSACTION;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table
-> WHERE id BETWEEN x AND y;
mysql> COMMIT;

After doing so, you’ll be left with the original table, which you can drop when you’re done with it, and the new table, which is now fully populated. Be careful to lock the original table if needed to prevent getting an inconsistent copy of the data!

share|improve this answer

The ALTER TABLE statement essentially does the same thing: the server creates a temporary table where it copies all the rows and then does a RENAME. On-disk formats are extremely different between InnoDB and MyISAM, so I don't expect you to find any shortcut to this.

Another remark (which you might be aware of, but it would help others reading this): on-disk format for InnoDB is highly dependent on the primary key, because it clusters records based on it. So when working with large InnoDB tables, think twice before picking the primary key, because changing it rebuilds the whole table, much like the problem at hand.

Anyway, I recommend doing a few tests first on a moderately sized table and time those.

share|improve this answer
1  
Any table altering operation in MySQL (and that includes adding indexes) rebuilds the table. –  David Pashley Aug 9 '09 at 18:00

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.