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

mysql import innodb load-data-infile
Jean Louis picture Jean Louis · Feb 16, 2011 · Viewed 9.6k times · Source

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.

Answer

Morgan Tocker picture Morgan Tocker · Feb 19, 2011

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.

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I import a database with MySQL from terminal?

How can I import a database with mysql from terminal? I cannot find the exact syntax.

Read More
Import SQL file into mysql

I have a database called nitm. I haven't created any tables there. But I have a SQL file which contains all the necessary data for the database. The file is nitm.sql which is in C:\ drive. This file has …

Read More