3

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?

2

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

2

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.

7
  • 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 '14 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 '14 at 19:37
  • I see... that makes sense! – ktm5124 Apr 10 '14 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 '14 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 '14 at 21:31
1

Currently, I am working on a similar task of importing a CSV file of 101GB. My table uses MyISAM engine and has fixed row format. Here is part of the script that I am using to import the file.

for f in $dumpDir/*.csv ; do
  table=`basename $f|cut -f1 -d'.'`
  echo "`date` Restoring table $table" 
  echo "SET foreign_key_checks = 0; LOAD DATA INFILE '$f' INTO TABLE project_commits CHARACTER SET UTF8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' " |$mysql || exit 1
done
  1. In the beginning, I was trying with load data LOCAL infile. But due to the storage limitation in my root directory, I was not able to use the LOCAL option. Because LOCAL option copies everything to a temporary place in the system root / then reads from there.

  2. Then I moved the CSV file into MySQL's data directory (which is moved to another HDD) to avoid LOCAL. However, running the script overnight (in total 12-15 hours) didn't finish the data import.

  3. Then I decided to split the 101GB file and split it using the following Linux command with 1 million rows in each split.

split -d -l 1000000 project_commits.csv file_part_ --filter='cat > $FILE.csv'

This results in more than 950 splits, each having about 180MB. At this point, when I ran the LOAD DATA INFILE script, I was experiencing problems similar to you. The first few splits took only a couple of seconds. Then it started increasing and loading split 46 took 40 minutes and I still had 900 more splits. This was clearly going nowhere.

  1. I adjusted the following things:

From Rolando:

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 1024;
SET bulk_insert_buffer_size = 1024 * 1024 * 1024;

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

[mysqld]
bulk_insert_buffer_size=1G

In addition to my FK check off, I also disabled the keys as suggested by Thomas like below:

alter table table_name disable keys;

Then I re-split the 101GB CSV file taking 100 million rows in each split resulting about 1.8GB for each part.

Now when I am running the LOAD DATA INFILE script, each part is taking about 30 seconds and the whole thing (all splitted files totalling 101GB) finished in less than 30 minutes.

2
  • Looks like you had some fun with MyISAM. BTW Welcome to the DBA StackExchange – RolandoMySQLDBA Apr 2 '18 at 0:59
  • Looks like the fun is not yet over. I was running the command ALTER TABLE table_name enable KEYS; Somehow the connection was lost and the whole table crashed. I am seeing the comment (in MySQL workbench) "Table './database/table_name' is marked as crashed and last (automatic?) repair failed". But I am not so concerned this time. I was curious to know the difference between the buffer size vs disable key options while keeping FK check off for both cases. Now I have a chance to do it. BTW. having an experienced yet helpful and responsive member like you is a huge blessing for any group. – Narnia_Optimus Apr 2 '18 at 4:38
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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