(mysql.info.gz) Converting tables to InnoDB
Info Catalog
(mysql.info.gz) InnoDB transactions with different APIs
(mysql.info.gz) Using InnoDB tables
(mysql.info.gz) InnoDB auto-increment column
15.7.2 Converting `MyISAM' Tables to `InnoDB'
---------------------------------------------
Important: You should not convert MySQL system tables in the `mysql'
database (such as `user' or `host') to the `InnoDB' type. 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, starting from the MySQL 3.23.43, add the line
`default-table-type=innodb' to the `[mysqld]' section of your `my.cnf'
or `my.ini' 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 ...
TYPE=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, starting from MySQL
3.23.52, you can speed up a table import by turning off the uniqueness
checks temporarily during the import session: `SET UNIQUE_CHECKS=0;'
For big tables, this saves a lot of disk I/O because `InnoDB' can then
use its insert buffer to write secondary index records in 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 and the 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'
runs out of space, it will start 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. In 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 Forcing recovery.
Info Catalog
(mysql.info.gz) InnoDB transactions with different APIs
(mysql.info.gz) Using InnoDB tables
(mysql.info.gz) InnoDB auto-increment column
automatically generated byinfo2html