Import Big InnoDB Tables
- turn off the logs;
- turn off unique key check if the table has;
- turn off foreign key check;
-
When importing data into InnoDB, make sure that MySQL does not have
autocommit mode enabled because that requires a log flush to disk for
every insert. To disable autocommit during your import operation,
surround it with SET autocommit and COMMIT statements:
SET autocommit=0; ... SQL import statements ... COMMIT; - If you use the mysqldump option --opt, you get dump files that are fast
to import into an InnoDB table, even without wrapping them with the SET
autocommit and COMMIT statements.
- If you have UNIQUE constraints on secondary keys, starting from
MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily
turning off the uniqueness checks during the import session:
SET unique_checks=0; ... SQL import statements ... SET unique_checks=1; - For big tables, this saves a lot of disk I/O because InnoDB can use its
insert buffer to write secondary index records in a batch. Be certain
that the data contains no duplicate keys.
- #
If you have FOREIGN KEY constraints in your tables, starting from MySQL
3.23.52 and 4.0.3, you can speed up table imports by turning the foreign
key checks off for a while in the import session:
SET foreign_key_checks=0; ... SQL import statements ... SET foreign_key_checks=1; - For big tables, this can save a lot of disk I/O.
-
If the above solution still can not quick your import process. Try mysqlimport. You can use mysqldump to dump a sql file and a text file. There is an example, look like this:
No comments:
Post a Comment