How to speed up a data loading into InnoDB (LOAD DATA INFILE)?

I can recommend these settings to improve load time.

Up vote 1 down vote favorite 2 share g+ share fb share tw.

I want to speed up a data loading. I use MySQL 5.5, InnoDB and have 1M rows of data (65Mb file). It takes 5 minutes.

What mysql settings and commands affect the speed of LOAD DATA INFILE for InnoDB? Thank you. Mysql import innodb load-data-infile link|improve this question asked Feb 16 '11 at 19:12Jean Louis309210 88% accept rate.

I can recommend these settings to improve load time: innodb_doublewrite = 0 log-bin = 0 innodb_support_xa = 0 innodb_buffer_pool_size = (50-80% of system memory) innodb_log_file_size = (a large number - 256M etc) innodb_flush_log_at_trx_commit = 0 Other than settings, there are some things you can do yourself: Create indexes after loading (this is a new optimization with 5.5 / InnoDB plugin). Sort the data file before loading. Split the data file, and load in parallel.

Thank you. It's great working! – Jean Louis Feb 20 '11 at 10:12.

Try removing indexes and triggers. You can re-create them after the load. Also look into using some of the high-load settings in my-huge.

Cnf instead of the defaults. Some more innodb performance settings: mysqlperformanceblog.com/2007/11/01/inno....

This might not be exactly what you're looking for but is a trick I've used in the past ALTER TABLE TABLE_NAME DISABLE KEYS; LOAD DATA INFILE ... ; ALTER TABLE TABLE_NAME ENABLE KEYS; Hope it helps.

1 This trick only works for MyISAM. – Morgan Tocker Feb 19 '11 at 20:24 You're right, sorry. – Sean Milheim Feb 24 '11 at 14:38.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.