I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done.
I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file growth, file_per_table is turned on).
But after about 5GB of data it slows down to the 2-4MB/sec range, as I get over 20GB it was down around 2MB/sec.
InnoDB buffer pools size is 8G. And I've done the following prior to running the LOAD DATA INFILE command:
SET @@session.sql_log_bin=0;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
alter table item_load disable keys;
//Run LOAD DATA INFILE....
I can't see the reason why it's starting off well and slowing down over time.
Also, using the same settings, I ran the same LOAD DATA INFILE command with the table using InnoDB and MyISAM and a 5GB test dataset, MyISAM was 20x faster:
InnoDB:
mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
Query OK, 2630886 rows affected, 6 warnings (21 min 25.38 sec)
Records: 2630886 Deleted: 0 Skipped: 0 Warnings: 6
MyISAM:
mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
Query OK, 2630886 rows affected, 6 warnings (1 min 2.52 sec)
Records: 2630886 Deleted: 0 Skipped: 0 Warnings: 6
Anything else I should consider trying? The MyISAM engine is able to keep up the load rate much better.
Additional details:
I've tried loading the files individually, no difference.
Incidentally, I have 150 files of 500MB each, within each file the keys are sorted.
After getting 40GB in overnight, 12h later, the load rate was down to 0.5MB/sec, meaning the operation is, practically speaking, impossible.
I haven't found any other answers to similar questions on other forums, it's seeming to me that InnoDB doesn't support loading large amounts of data into tables over a few GB in size.
