14.2.6.2. Converting MyISAM Tables to InnoDB
Important: Do not convert MySQL system tables in the
mysql database (such as
user or host) to the
InnoDB type. This is an unsupported
operation. The system tables must always be of the
MyISAM type.
If you want all your (non-system) tables to be created as
InnoDB tables, you can simply add the line
default-storage-engine=innodb to the
[mysqld] section of your server option file.
InnoDB does not have a special optimization
for separate index creation the way the
MyISAM storage engine does. Therefore, it
does not pay to export and import the table and create indexes
afterward. The fastest way to alter a table to
InnoDB is to do the inserts directly to an
InnoDB table. That is, use ALTER
TABLE ... ENGINE=INNODB, or create an empty
InnoDB table with identical definitions and
insert the rows with INSERT INTO ... SELECT * FROM
....
If you have UNIQUE constraints on secondary
keys, you can speed up a table import by turning off the
uniqueness checks temporarily during the import operation:
SET UNIQUE_CHECKS=0;
... import operation ...
SET UNIQUE_CHECKS=1;
For big tables, this saves a lot of disk I/O because
InnoDB can then use its insert buffer to
write secondary index records as a batch.
To get better control over the insertion process, it might be
good to insert big tables in pieces:
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey <= somethingelse;
After all records have been inserted, you can rename the tables.
During the conversion of big tables, you should increase the
size of the InnoDB buffer pool to reduce disk
I/O. Do not use more than 80% of the physical memory, though.
You can also increase the sizes of the InnoDB
log files.
Make sure that you do not fill up the tablespace:
InnoDB tables require a lot more disk space
than MyISAM tables. If an ALTER
TABLE operation runs out of space, it starts a
rollback, and that can take hours if it is disk-bound. For
inserts, InnoDB uses the insert buffer to
merge secondary index records to indexes in batches. That saves
a lot of disk I/O. For rollback, no such mechanism is used, and
the rollback can take 30 times longer than the insertion.
In the case of a runaway rollback, if you do not have valuable
data in your database, it may be advisable to kill the database
process rather than wait for millions of disk I/O operations to
complete. For the complete procedure, see
Section 14.2.8.1, “Forcing InnoDB Recovery”.
