Load Data Infile and InnoDB

from the Artful MySQL Tips List


MyISAM has no transaction logic so it's way faster to Load huge input files into MyISAM.

If bulk inserts into InnoDB tables cannot be avoided, there's wide agreement that disabling unique_checks and foreigh_key_checks for the duration of the bulk insert helps performance significantly.

Turning off log_bin for the duration of the Load Data process cuts execution time by 20-25%. Is that safe? All transactions involved are already documented in the Load Data input file, so if the server isn't a replication master, you can probably live without the redundancy of duplicating every command to the binary log, and just save the input file as an external record of the operation.To do it before MySQL version 5.6, you had to unset log_bin in my.cnf/ini and restart the server, and of course reverse that with another server reset afterwards. Since 5.6, it's a dynamic variable:

set session sql_log_bin=0;

For other options see ...

https://nbsoftsolutions.com/blog/optimizing-innodb-bulk-insert

http://dba.stackexchange.com/questions/31752/mysql-load-data-infile-slows-by-80-after-a-few-gigs-of-input-with-innodb-engine

on partitioning and InniDB and Load Data, see http://www.ajaydivakaran.com/mysql-innodb-are-inserts-slowing-down

... and on the benefits of chunking, see Baron Schwarz's pt-fifo_split.



Return to the Artful MySQL Tips page