Need to make MySQL's LOAD DATA LOCAL INFILE load large data faster

I need to load 4 million rows of data into a MySQL InnoDB table using LOAD DATA INFILE and would like to know if there are server configuration options I can tweak to get faster load.

It took me 15 minutes to loaded 2 million rows, a performance I thought was disappointing for the LOAD DATA INFILE. My statement looks like this


LOAD DATA LOCAL INFILE 'path/file.csv'
INTO TABLE table FIELDS
TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES (column1, column2, etc);

I'll bet that you are currently I/O bound. This means that nothing can speed it up. (And Rolando's suggestions may be futile.)

Let's look deeper. Is this LOAD a recurring task? If so, how often? Is everything blocked waiting for table to be reloaded? Simple solution: Load into a different table, then do a double RENAME TABLE to swap it in. Only milliseconds of downtime.

Is the data coming from another machine? Use the network for the "input" side of the LOAD rather than having the one disk fighting for reads versus writes.

Do you have a lot of indexes? There are several directions to take this question. Let's see SHOW CREATE TABLE before barking up these tree(s).

Does the entire load need to be a single transaction? Multiple transactions may be faster because of not overflowing the log file. (I've seen 2x.)

StackExchange Database Administrators Q#98384, answer score: 2