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 for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
I can recommend these settings to improve load time:
- innodb_doublewrite = 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.
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:
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.
Also make sure that binary logging disabled if possible.
If you are in a hurry because you are replacing the contents of a live table, then do it this way instead:
CREATE TABLE new LIKE live; LOAD DATA ... INTO new; RENAME TABLE live TO old, new TO live; DROP TABLE old;
RENAME is ‘instantaneous’ and atomic, so you are ‘never’ down, regardless of table size.
(Hence you don’t need to worry so much about speeding up the