Why does MySQL slow down in loading data?

Question

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?

Answer 1

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

Answer 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.

Answer 3

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.

4. 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.

Answer 4

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.