Mysql – Need to make MySQL’s LOAD DATA LOCAL INFILE load large data faster

innodbMySQL

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);

Best Answer

Although LOAD DATA INFILE can work against InnoDB, there are too many ways InnoDB gets tapped to its limits before swapping and bottlenecks takeover.

Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko)

InnoDB Plumbing

The bottlenecks would be goring through the following structures

InnoDB Buffer Pool
Transaction Logs (ib_lofile0, ib_logfile1)
Double Write Buffer
Insert Buffer
One Rollback Segment
Log Buffer

Here are some of my past posts where I discuss LOAD DATA INFILE with InnoDB

Feb 06, 2012 : LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?
Jan 11, 2013 : MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine
Jan 12, 2013 : What does 'system lock' mean in mysql profiling a LOAD DATA INFILE statement?

SUGGESTION #1

Break up the file into 20 smaller files.

Instead of one LOAD DATA INFILE against a 2 million row file, perform 20 LOAD DATA INFILE against 20 files, each with 100 thousand rows.

The Benefit : Less pressure against the InnoDB Plumbing

SUGGESTION #2 (Optional)

Increase the Log Buffer (innodb_log_buffer_size = 256M)
Increase the Write Threads (innodb_write_io_threads = 16)
Increase the InnoDB Buffer Pool Size (innodb_buffer_pool_size)

Related Solutions

MySQL – LOAD DATA INFILE – Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

Have you looked into the SELECT INTO OUTFILE syntax?

Here is a quote on the NULL string issue:

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

So make sure you indicate a FIELDS ESCAPED BY clause to get NULLs written as \N

Hope this helps

Thesqldump vs LOAD DATA INFILE

If you have a schema already in place, you should use mysqldump options to create the dump of the data to perform in the inserts only

mysqldump -h... -u... -p... --no-create-info --databases ... > MySQLData.sql

You should also make sure to raise the bulk_insert_buffer_size (default is 8M) on all DB Servers to accommodate large extended inserts. This will also help LOAD DATA INFILE if loading non-empty tables.

You are going to have to also adjust your max_allowed_packet (default is 1M).

Try these settings for starters:

[mysqld]
max_allowed_packet=256M
bulk_insert_buffer_size=256M

UPDATE 2011-10-11 06:53 EDT

Regardless of how beefy the hardware is, how much RAM you have allocated, how well tuned the OS is, and how current the version of MySQL is, MySQL will only perform as well as it is configured.

Example: If you have a swimming pool that can hold 10,000 gallons of water, you have a truck holding 10,000 gallons of water, and the hose on the truck is only as big as a straw, you can only push but so much water through the straw to fill the pool. Getting a bigger truck or getting a faster water pump simply will not improve things. You must exchange the hose for a much bigger hose to accommodate more water, thus, more throughput.

In like fashion, MySQL 5.5 out-of-the-box does not come fully tuned.

Example #1: MySQL 5.5 comes with semisynchronous replication. By default, it is disabled. You must do the two step process of starting mysql, runnning INSTALL PLUGIN on master and slave modules, shutdown mysql, add timing and activation options to my.cnf for the semisych features, then starting up mysql for the second time. Only then will semisync replication work.

Example #2: The bulk insert buffer by default is 8M. The bulk insert buffer will not grow because of the presence of any specific hardware or software. It stays 8M until you increase it. It is possible to increase it by either adding it to my.cnf and restarting mysql or running SET bulk_insert_buffer_size = 268435456; to set it to 256M within a session and then load the mysqldump within that same session.

Example #3: MySQL 5.5 is fully capable of engaging multiple CPUs. By default, the features for engaging multiple CPUs is disabled. They require tuning because although MySQL 5.5 is multicore ready, MySQL 5.5 is only as multithreaded as you configure it.

Conclusion: You must configure MySQL to recognize that it has beefy hardware, more RAM, and a cooperative OS at its disposal.