CODEWITHSUNDEEP
mysqldatabasemyisam
cardician
cardician

Reputation: 2491

LOAD INFILE of 200 million plus records speed

I apologize if this question is a duplicate but most of the ones I've found are regarding InnoDB and I'm using MyISAM.

I'm trying to create a process that will be loading anywhere between 200-300 million records into tables. I'm using LOAD DATA INFILE and for one file that has 178 million records it took approximately 30 minutes to load the data. Can anyone suggest any ways to speed this up? Or is this fairly expected performance? I've tried disabling keys before the load but that didn't seem to really create any noticeable speed improvement. What other settings could I try?

I'm on a linux server with 24 fairly modern cores. Is there some way I can take advantage of that fact?

I will add that I attempted loading 30 million records and it took 5 minutes, which seems to be about the same level of performance as the 178 million records. However, if I attempt to load 30 million records into a table that already has 30 million records, the time took 8 minutes. Anything I can do about that since I believe this scenario will also be occurring a number of times.

Any help is appreciated but please know that I'm fairly database ignorant so links to examples or docs would be very helpful.

Upvotes: 0

Views: 213

Answers (1)

CShannon
CShannon

Reputation: 135

To some degree this depends on the width of a given record, but it sounds like 178 million records in 30 minutes is already a fairly good speed to me. I have done loads of 46 million records in 25 minutes and been quite happy, but there was also some processing going on for each record along the way in my case.

There are almost always ways to improve performance but cost-benefit can sometimes make that prohibitive. If you are getting files to load more frequently than every 30 minutes then clearly something else may be needed. But in that case I believe a re-think of the larger system and what you are storing/why you are storing it might be warranted as well.

As for the difference between loading 30 million records to an empty versus a non-empty table this is not unexpected for an indexed table.

Upvotes: 1

Related Questions