(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