Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm importing around 44 GB (14 million rows) of information into an InnoDB MySQL database, and I'd like to do so in a reasonable timeframe. Since I'm doing this on a personal computer, I only have 8 GB of RAM available to me (and I'm using 1.5 GB for the MySQL database).

Right now, I'm using LOAD DATA INFILE. I've tweaked a lot of the InnoDB settings, including increasing the buffer pool size and buffer log size. However, the speed of this method seems to drop rapidly as the amount of information in the database increases - the first "chunk" of data (100 MB) loaded in 79 seconds, but by the time I reached the fourth chunk of data, it took 1004 seconds to load the same amount. Unfortunately, this precipitous drop in transfer rate makes it nearly impossible to load all of the data onto the MySQL server in a reasonable timeframe.

I had several questions about the scenario. Why does the transfer rate drop so drastically? I've heard that MyISAM is more efficient for loading data, so should I try to use a MyISAM engine to load this data instead? (I'll be accessing the data only locally) Are there any other techniques I can use to expedite the data loading process?

share|improve this question
    
you heard wrong on correct hardware/configuration InnoDB will blow alway MyISAM with performance ... oracle.com/partners/en/knowledge-zone/… .. –  Raymond Nijland Oct 15 '13 at 18:09
    
I probably don't have the best hardware of InnoDB, then, since I just converted the database and loaded the data in around 20 minutes with MyISAM. –  Draksis Oct 15 '13 at 20:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.