Optimise Load Data Infile

from the Artful MySQL Tips List

With MyISAM, a spectacular improvement in Load Data Infile performance can be got from ALTER TABLE ...ROW_FORMAT=Fixed. By itself this will speed up insert times enormously, but a side-effect will be that the table may double in size.

The other main points with MyISAM are disabling keys and setting session bulk_insert_buffer_size, myisam_sort_buffer_size and key_buffer_size; see http://venublog.com/2007/11/07/load-data-infile-performance.

InnoDB has to transactionally process each Insert. With huge tables, the process can slow to a crawl---so much so that some DBAs advocate simply avoiding InnoDB for huge reference tables.

Apart from that, with InnoDB the main points are disabling foreign key checks and setting innodb_additional_mem_pool_size, innodb_buffer_pool_size, innodb_log_buffer_size, and innodb_log_file_size; see http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/.

Last updated 16 Aug 2019

Return to the Artful MySQL Tips page