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

Question

I want to speed up 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 LOAD DATA INFILE speed for InnoDB?

Thanks.

Answer 1

I can recommend these settings for longer boot times:

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

Besides the settings, there are some things you can do yourself:

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

Answer 2

Try deleting indexes and triggers. You can recreate them after loading. Also consider using some high-load options in my-huge.cnf instead of the default values.

Some innodb performance tweaks:

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

Answer 3

It may not be exactly what you are looking for, but it is a trick I used in the past

ALTER TABLE TABLE_NAME DISABLE KEYS; LOAD DATA INFILE ... ; ALTER TABLE TABLE_NAME ENABLE KEYS; 

Hope this helps.

Answer 4

Also make sure binary logging is disabled if possible.

Answer 5

If you are in a hurry because you are replacing the contents of a live table, do it as follows:

 CREATE TABLE new LIKE live; LOAD DATA ... INTO new; RENAME TABLE live TO old, new TO live; DROP TABLE old; 

RENAME is instantaneous and atomic, so you should never, no matter the size of the table.

(Therefore, you don’t need to worry so much about LOAD acceleration.)