15

I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done.

I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file growth, file_per_table is turned on).

But after about 5GB of data it slows down to the 2-4MB/sec range, as I get over 20GB it was down around 2MB/sec.

InnoDB buffer pools size is 8G. And I've done the following prior to running the LOAD DATA INFILE command:

SET @@session.sql_log_bin=0;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
alter table item_load disable keys;
//Run LOAD DATA INFILE....

I can't see the reason why it's starting off well and slowing down over time.

Also, using the same settings, I ran the same LOAD DATA INFILE command with the table using InnoDB and MyISAM and a 5GB test dataset, MyISAM was 20x faster:

InnoDB:

mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
Query OK, 2630886 rows affected, 6 warnings (21 min 25.38 sec)
Records: 2630886  Deleted: 0  Skipped: 0  Warnings: 6

MyISAM:

mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
Query OK, 2630886 rows affected, 6 warnings (1 min 2.52 sec)
Records: 2630886  Deleted: 0  Skipped: 0  Warnings: 6

Anything else I should consider trying? The MyISAM engine is able to keep up the load rate much better.


Additional details:

  • I've tried loading the files individually, no difference.

  • Incidentally, I have 150 files of 500MB each, within each file the keys are sorted.

  • After getting 40GB in overnight, 12h later, the load rate was down to 0.5MB/sec, meaning the operation is, practically speaking, impossible.

  • I haven't found any other answers to similar questions on other forums, it's seeming to me that InnoDB doesn't support loading large amounts of data into tables over a few GB in size.

0

4 Answers 4

7

OBSERVATION #1

I noticed you turned off autocommit. That will pile up so much data in ibdata1. Why?

There are seven(7) classes of information that is stored in ibdata1:

  • Data Pages for InnoDB Tables
  • Index Pages for InnoDB Tables
  • Data Dictionary
  • Double Write Buffer
    • Safety Net to Prevent Data Corruption
    • Helps Bypass OS for Caching
  • Insert Buffer (Streamlines Changes to Secondary Indexes)
  • Rollback Segments
  • Undo Logs
  • Click Here to see a Pictorial Representation of ibdata1

Some of this info is made visible to certain transactions depending on the isolation level. Such actions could produce unintended primary key locks and lots of phantom data. As these two things increase, you should expect a fair slow down.

Recommendation: Leave autocommit on

OBSERVATION #2

I see you have this:

alter table item_load disable keys;

DISABLE KEYS does not work with InnoDB. Here is why:

  • MyISAM : DISABLE KEYS simply shuts off Secondary Index updating for the MyISAM table. When you mass INSERT into a MyISAM table with keys disabled results in a fast table load along with a building of the PRIMARY KEY and all unique indexes. When you run ENABLE KEYS, all Secondary Indexes are built linearly on the table and appended to the .MYD.
  • InnoDB : As shown in the internals picture of InnoDB, the system tablespave ibdata1 has a structure dedicated to Secondary Index Insertions. At present, there is no provision to handle indexes the same as MyISAM.

To illustrate this, note my attempt to run DISABLE KEYS on an InnoDB table in MySQL

mysql> show create table webform\G
*************************** 1. row ***************************
       Table: webform
Create Table: CREATE TABLE `webform` (
  `nid` int(10) unsigned NOT NULL,
  `confirmation` text NOT NULL,
  `confirmation_format` tinyint(4) NOT NULL DEFAULT '0',
  `redirect_url` varchar(255) DEFAULT '<confirmation>',
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  `teaser` tinyint(4) NOT NULL DEFAULT '0',
  `allow_draft` tinyint(4) NOT NULL DEFAULT '0',
  `submit_notice` tinyint(4) NOT NULL DEFAULT '1',
  `submit_text` varchar(255) DEFAULT NULL,
  `submit_limit` tinyint(4) NOT NULL DEFAULT '-1',
  `submit_interval` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table webform disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'webform' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.00 sec)

mysql>

OBSERVATION #3

You noticed that MyISAM loads 20x faster than InnoDB. Would you like that to be more like 24-25 times faster? Then run the following:

ALTER TABLE item_load ROW_FORMAT=Fixed;

This will speed up INSERTs times 20-25% without any other DDL Changes. Side effect: The MyISAM table can grow 80%-100% in size, possibly larger.

You could run this on an InnoDB table as well, but the ACID-compliant behavior and MVCC of InnoDB would still be the bottleneck of its performance, especially if VARCHAR fields increase significantly are are written to ibdata1.

1
  • The first 2 observations were things I tried to add on to fix the problem after I first noticed it, my first attempt was naturally to leave innodb alone (just turn off bin logging). On the 3rd observation, my data size is highly variable in length, I assume this will be a problem? I'm feeling like I just need to keep this table myisam. Commented Jan 12, 2013 at 1:38
6

The final answer to this question was to not use InnoDB for a massive reference table. MyISAM is screaming fast, near full throughput of the disk speed for the entire load, InnoDB bogs down. MyISAM is simple, but in this case so are the requirements of this table. For a simple reference table with bulk loads over LOAD DATA INFILE, MyISAM is the way to go, so far so good.

But note that if you run MyISAM and InnoDB tables both you're going to need to consider memory allocation for 2 caching mechanisms, each engine has it's own unique caching that needs separate memory allocation.

0
5

You could try to split your input files into smaller chunks.

I personally use http://www.percona.com/doc/percona-toolkit/2.1/pt-fifo-split.html for this.

What happens if you get a table lock for table during the import? Maybe the rowlevel locking of InnoDB slows it down (MyISAM uses a table lock).

You could also read here for further ideas: http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql

3
  • My files are already in 500MB chunks, I was piping them all through a single named pipe to make the load easier, but I'll try this approach now. Commented Jan 12, 2013 at 1:39
  • Not seeing any difference here, pretty quickly I see the speed drop from 11MB/sec expansion of the DB file to 6MB (after about 2GB) of data and it's continuing to drop. I'm loading all the files in a for loop, separate mysql calls. Commented Jan 12, 2013 at 2:12
  • The first file loaded in 54s, 2nd in 3m39s, 3rd in 3m9s, 4m7s, 5m21s, and so on. all files aprox the same size. Commented Jan 12, 2013 at 2:40
2

If your PK is not AUTO_INCREMENT or data in csv file not sorted on PK than it might be affecting performance of dataload. Since table in MySQL is an index hence all data is stored in sorted order, if PK value is not on AUTO_INCREMENT than MySQL has to do lot of data shifting to get the data stored in sorted order. This is the reason for slower data load when table size start growing.

I am loading 91GB csv file with PK on AUTO_INCREMENT using LOAD DATA INFILE and I do not see any drop in my throughput. I am getting 140K to 145K inserts per seconds. Using Percona MySQL 5.6.38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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