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.

We have a really large file (26GB) that we need to upload to MySQL. In order to do so we split it into nine 3GB files.

What's confusing is that the first file took only 2 minutes to load, whereas the second file took 16 minutes and the third file took 40 minutes.

We're loading these files using load data local infile syntax, without any indexes or keys. The table engine is MyISAM.

Does anyone know why MySQL slows down on each successive load?

share|improve this question

3 Answers 3

You might need to LOCK the tables before you insert the data into the database and then you unlock after the LOAD statement.

There are some tips on MySQL manual as well, check it out:

http://dev.mysql.com/doc/refman/5.6/en/optimizing-myisam-bulk-data-loading.html

share|improve this answer

ASPECT #1

This sounds like you are suffering from a classic case of bulk insert buffering. (Forgive me if I sound like a doctor). LOAD DATA INFILE takes advantage of a tree-structured bulk insert buffer. The size is set by the option bulk_insert_buffer_size.

Please note what that part of the Documentation says:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

This being the case, the setting for bulk_insert_buffer_size is too small.

Increase it on the server dynamically with

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 1024;

for all incoming sessions.

For you current session, run

SET bulk_insert_buffer_size = 1024 * 1024 * 1024;

That's a 1G maximum setting.

Then, go add this to /etc/my.cnf

[mysqld]
bulk_insert_buffer_size=1G

ASPECT #2

Since the table is MyISAM, why make mysqld jump through hoops to regenerate the table ???

If the table is mydb.mytable and your datadir is /var/lib/mysql, copy these

  • /var/lib/mysql/mydb/mytable.frm
  • /var/lib/mysql/mydb/mytable.MYD
  • /var/lib/mysql/mydb/mytable.MYI

to the other server's /var/lib/mysql/mydb

After copying it, remember to change the file ownership

cd /var/lib/mysql/mydb
chown mysql:mysql mytable.*

ASPECT #3

Since you are loading a MyISAM table, there may be indexes to account for. You should do this

use mydb
ALTER TABLE mytable DISABLE KEYS;
LOAD DATA INFILE ... INTO mytable ... ;
ALTER TABLE mytable ENABLE KEYS;

This will disable non-unique indexes, load the data and fill the .MYI with the Primary Key and Unique Indexes. The ENABLE KEYS phase will linearly build all non-unique indexes. That should speed things up.

share|improve this answer
    
Thanks! But I still wonder why the first file loaded so quickly. If the problem is bulk_insert_buffer_size, wouldn't that have affected the first upload? –  ktm5124 Apr 10 at 19:33
    
Look at the Documentation line that says LOAD DATA INFILE when adding data to nonempty tables. From that line, my guess is that there is no bulk insert buffer in use for the first pass. –  RolandoMySQLDBA Apr 10 at 19:37
    
I see... that makes sense! –  ktm5124 Apr 10 at 19:44
    
Hm, I tried this on the fourth file upload, and it's going slower than the previous ones. I think it's already taking 2+ hours, whereas the first file (same size) took only 2 minutes. –  ktm5124 Apr 10 at 23:00
1  
Hi Rolando, thanks for all your help. It turns out the problem was that the database didn't have enough disk space. –  ktm5124 Apr 15 at 21:31

In some cases disabling keys can help. Complex keys can slow down inserts.

alter table xxx disable keys;

Don't forget to enable keys after importing. And don't do complicated selects while importing.

alter table xxx enable keys;

But as Rolando says, LOAD DATA INFILE is faster. The advantage of my solution is the easier implementation.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.