Re: Efficient way to LOAD large amount of DATA.
Posted by: Peter Brawley
Date: May 18, 2016 08:04PM

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/.

Options: ReplyQuote


Subject
Written By
Posted
Re: Efficient way to LOAD large amount of DATA.
May 18, 2016 08:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.