Mysql – Simpler way to convert all tables from InnoDB to MyISAM

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!

MySQL use of DiskSpace is quite predictable.

The information_schema can quickly give away how much space is used by both storage engines. However, it is far better to configure InnoDB with innodb_file_per_table. That way, you can micromanage the diskspace of individual InnoDB tables. If you do not have innodb_file_per_table, the ibdata1 will grow and NEVER, EVER SHRINK.

I wrote nice articles about Cleaning Up InnoDB once and for all.

• MySQL InnoDB - innodb_file_per_table cons?
• https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

As for MyISAM, you need to periodically run one of the following:

• OPTIMIZE TABLE myisam-tablename;
• ALTER TABLE myisam-tablename ENGINE=MyISAM; ANALYZE TABLE myisam-tablename;

These will compress MyISAM so that there are not unused data and index pages in the MyISAM table components (.MYD and .MYI)

You can manually monitor the disk space usage with this query:

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;

This will tell how much space is occupied by data and index for engine storage engine. Notice at the end of the query the clause: (SELECT 3 pw). Change the number to generate the report in different units

(SELECT 0 pw) for Bytes
(SELECT 1 pw) for KiloBytes
(SELECT 2 pw) for MegaBytes
(SELECT 3 pw) for GigaBytes
(SELECT 4 pw) for TeraBytes
(SELECT 5 pw) for PetaBytes (Write me if you every get numbers this high)

UPDATE 2012-05-26 22:29 EDT

When it comes to InnoDB, if you use innodb_file_per_table, you may find that there is a difference between the file size of the .ibd file and the sum of data_length + index_length.

For an InnoDB table mydb.mytable, here is the comparison you should make:

• Get the size of file by running ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print %5}'
• Get the size of the table from the information schemna's point of view: SELECT data_length+index_length FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable';
• If the filesize > (data_length+index_length) * 1.1, then you should drag the table like this: ALTER TABLE mydb.mytable ENGINE=InnoDB;

This will make a temp table, copy only real data pages and index pages into the temp table, delete the original, and rename the temp table back to mydb.mytable. Instant table compression with one command. Please plan all table compressions during off hours.