Large MySQL Data Imports

Published on Author craigzearfossLeave a comment

Disable MySQL indexes

  • MySQL can import many rows with a single disk write.
  • It makes the import go many times faster and minimizes the impact on other customers.
  • Database files created with phpMyAdmin or mysqldump already contain commands to disable the indexes.
  • To disable MySQL indexes:
    • Add these commands before the import starts:
      ALTER TABLE `table_name` DISABLE KEYS;
    • Then send these after the import:
      ALTER TABLE `table_name` ENABLE KEYS;

Tips for MyISAM tables

  • Use MySQL’s INSERT DELAYED command instead of the normal INSERT.

Tips for InnoDB tables

  • Add these extra commands at the top of the file to avoid a great deal of disk access:
    SET FOREIGN_KEY_CHECKS = 0;
    SET UNIQUE_CHECKS = 0;
    SET AUTOCOMMIT = 0;
  • Add these at the end:
    SET UNIQUE_CHECKS = 1;
    SET FOREIGN_KEY_CHECKS = 1;
    COMMIT;
  • For maximum effect, add this in addition to “DISABLE KEYS” and “ENABLE KEYS” described above.

Don’t swamp the servers

  • Don’t try to simply blast the data into MySQL as quickly as possible.
  • Try to make sure you use less than 25% of the server’s resources.

Reference:

Leave a Reply

Your email address will not be published. Required fields are marked *