How to Monitor and Speed up 'LOAD DATA INFILE' with InnoDB

The loading of large data sets in InnoDB table may be complex. Here are some hints how to maximize performance, when using the LOAD DATA INFILE with InnoDB tables.

1) Use the largest buffer pool possible for the load operation (assuming it is the only operation going at the time) some 80-90% of system memory could be good value. Basically this is almost only buffer you need for this operation.
2) If you have your primary key entries going in "random" order can drop performance a lot. This can be fixed by adding ORDER BY <primary_key> for SELECT * INTO OUTFILE. This will increase dumping time of course.
3) Make sure to disable UNIQUE key checks if you're sure they are satisfied. This is done by SET UNIQUE_CHECKS=0 before starting the load.

A few notes about progress monitoring. For InnoDB tables you can deliver a lot of information from SHOW INNODB STATUS.

Take a look at "inserts/sec" value, a while after load stated (it can be exceptionally high until buffer pool is filled). This is the load speed you can expect

Take a look at transactions list. It has such data as "undo entries". This is basically amount of rows already proceed. Knowing this value and total value of rows to be inserted you can estimate progress as well as completion time.