# Slow bulk loads innodb

**URL:** <https://forums.percona.com/t/slow-bulk-loads-innodb/1759>\
**Category:** Other MySQL® Questions\
**Created:** [February 13, 2012, 9:57am UTC](https://forums.percona.com/t/slow-bulk-loads-innodb/1759 "2012-02-13T09:57:13Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![adphillips](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/adphillips/32/863_2.png) [@adphillips](https://forums.percona.com/u/adphillips)\
**Post date:** [February 13, 2012, 9:57am UTC](https://forums.percona.com/t/slow-bulk-loads-innodb/1759/1 "2012-02-13T09:57:13Z")

</div>

Hi,

I am running out of ideas how to speed up bulk loads of my data (approx 30m rows) into a single innodb table. The load is running at about 200r/s. The same bulk load in an otherwise identical MyISAM table is running at about 3k r/s.

Here is what I am running (I am using mk-fifo-split script to load in chunks of various sizes, currently at 10k per chunk so I can see progress readily)

ALTER TABLE cdr\_test2 DISABLE KEYS;  
set foreign\_key\_checks=0;  
set sql\_log\_bin=0;  
set unique\_checks=0;  
LOAD DATA LOCAL INFILE ‘/tmp/mk-fifo-split’ INTO TABLE cdr\_test2 CHARACTER SET ‘UTF8’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ (CallID,ParentCallID,SessionID,ParentSessionID,SipSessionID, AccountID,ApplicationID,PPID,StartTime,EndTime,Duration,Outb ound,Status,Network,Channel,StartUrl,CalledID,CallerID,Servi ceID,PhoneNumberSid,Disposition,RecordingDuration,DateCreate d,BrowserIP,ScriptThrowable,applicationType)

The box is on ec2 (ebs volume) with 15G ram and 1TB disk with 4 (HT) cores. The source data and database files are both on the same ebs volume.

I will add that I have also made the following variable changes which have not improved the load speed much at all, if they have it’s not been very noticeable.

innodb\_buffer\_pool\_size = 5125M  
innodb\_log\_file\_size = 1024M  
innodb\_log\_buffer\_size = 8M

I have also tried setting this prior to a load:

set global innodb\_flush\_log\_at\_trx\_commit=0;

Any ideas or help would be much appreciated,  
Thanks!  
Aaron

---

<div class="post-metadata">

**Author:** ![MattK](https://avatars.discourse-cdn.com/v4/letter/m/76d3ee/32.png) [@MattK](https://forums.percona.com/u/MattK)\
**Post date:** [February 13, 2012, 11:54am UTC](https://forums.percona.com/t/slow-bulk-loads-innodb/1759/2 "2012-02-13T11:54:40Z")

</div>

What data type is the PK column?

It appears that InnoDB requires a PK even for bulk loads (?), and if the clustered key is non-sequential, then engine could be performing a lot of maintenance work.

---

<div class="post-metadata">

**Author:** ![adphillips](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/adphillips/32/863_2.png) [@adphillips](https://forums.percona.com/u/adphillips)\
**Post date:** [February 13, 2012, 12:03pm UTC](https://forums.percona.com/t/slow-bulk-loads-innodb/1759/3 "2012-02-13T12:03:58Z")

</div>

Well actually i did not specify a PK for this table (nor does it have any indexes). Reading on innodb is telling me I should be explicit about the PK. If I were it would be a GUID which is probably not ideal. I’m going to try another more sensible column for a PK and see what happens