(mysql.info) converting-tables-to-innodb

Info Catalog

(mysql.info) innodb-transactions-with-different-apis

(mysql.info) using-innodb-tables

(mysql.info) innodb-auto-increment-column

 
 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  forcing-recovery.
 

Info Catalog

(mysql.info) innodb-transactions-with-different-apis

(mysql.info) using-innodb-tables

(mysql.info) innodb-auto-increment-column