Search code examples
mysqlimportinnodbload-data-infile

How to speed up a data loading into InnoDB (LOAD DATA INFILE)?

I want to speed up a data loading.

I use MySQL 5.5, InnoDB and have 1M rows of data (65Mb file). It takes 5 minutes.

What mysql settings and commands affect the speed of LOAD DATA INFILE for InnoDB?

Thank you.

Solution

  • I can recommend these settings to improve load time:

    • innodb_doublewrite = 0
    • innodb_support_xa = 0
    • innodb_buffer_pool_size = (50-80% of system memory)
    • innodb_log_file_size = (a large number - 256M etc)
    • innodb_flush_log_at_trx_commit = 0

    Other than settings, there are some things you can do yourself:

    • Create indexes after loading (this is a new optimization with 5.5 / InnoDB plugin).
    • Sort the data file before loading.
    • Split the data file, and load in parallel.