csv upload is much slower in InnoDB than MyiSAM

Question

I am using MySQL 5.5.34.

We have an environment where we daily upload around 1GB of csv file in out DB and all the tables are MyISAM till now. Now we are planning to switch to InnoDB as many a time we experience table locks.

To be frank every second is worth many dollers for us.

We created same tables in InnoDB & tried to upload the csv files. To our suprise InnoDB took hours more than MyISAM. So, again to test this I created a test environment on my machine. I have 64 bit, 4 GB RAM, Core i3 @ 3.20GHz processor.

I create below two tables.

DROP TABLE `myisam_upload_test`;
CREATE TABLE `myisam_upload_test` (
  `ID` INT(10) NOT NULL,
  `FIRST_NAME` VARCHAR(255) DEFAULT NULL,
  `LAST_NAME` VARCHAR(255) DEFAULT NULL,
  `TEAM` VARCHAR(20) DEFAULT NULL,
  DESIGNATION VARCHAR(50) DEFAULT NULL,
  `COMPANY` VARCHAR(50) DEFAULT NULL,
  ADDRESS_LINE_1 VARCHAR(50) DEFAULT NULL,
  ADDRESS_LINE_2 VARCHAR(50) DEFAULT NULL,
  CITY VARCHAR(50) DEFAULT NULL,
  POST_CODE INT(5) DEFAULT NULL,
  STATE VARCHAR(50) DEFAULT NULL,
  COUNTRY VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;


DROP TABLE `innodb_upload_test`;
 CREATE TABLE `innodb_upload_test` (
  `ID` INT(10) NOT NULL,
  `FIRST_NAME` VARCHAR(255) DEFAULT NULL,
  `LAST_NAME` VARCHAR(255) DEFAULT NULL,
  `TEAM` VARCHAR(20) DEFAULT NULL,
  DESIGNATION VARCHAR(50) DEFAULT NULL,
  `COMPANY` VARCHAR(50) DEFAULT NULL,
  ADDRESS_LINE_1 VARCHAR(50) DEFAULT NULL,
  ADDRESS_LINE_2 VARCHAR(50) DEFAULT NULL,
  CITY VARCHAR(50) DEFAULT NULL,
  POST_CODE INT(5) DEFAULT NULL,
  STATE VARCHAR(50) DEFAULT NULL,
  COUNTRY VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

I create a 150 MB csv file and uploaded in both the tables. Below are the results.

InnoDB: 
Execution Time : 15.077 sec
Transfer Time  : 0 sec
Total Time     : 15.077 sec

MyISAM
Execution Time : 4.139 sec
Transfer Time  : 0 sec
Total Time     : 4.139 sec

MyISAM is almost 4 time faster than InnoDB which is not acceptable in the environment we are working as I mentioned earlier that every second is worth many dollers for us.

Below is my my.cnf file

[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=160

table_cache=320

query_cache_size=50M
query_cache_type=1
query_cache_limit=1M

tmp_table_size=33M

thread_cache_size=8

#*** MyISAM Specific options

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=49M

key_buffer_size=45M

read_buffer_size=64K
read_rnd_buffer_size=256K

sort_buffer_size=256K


#*** INNODB Specific options ***
innodb_additional_mem_pool_size=25M

innodb_flush_log_at_trx_commit=2

innodb_log_buffer_size=64M

innodb_buffer_pool_size=3G

innodb_file_per_table=1

innodb_log_file_size=256M

innodb_thread_concurrency=10
innodb_read_io_threads=5
innodb_write_io_threads=5
innodb_use_sys_malloc=1

Also I changed innodb_flush_log_at_trx_commit to both 1 & 2 but the result is same.

Also I tried to remove some overhead from InnoDB engine using below commands.

SET autocommit=0;
SET unique_checks=0;
SET FOREIGN_KEY_CHECKS=0;

If I set autocommit=1 then also results are the same.

Execution Time : 14.698 sec
Transfer Time  : 0 sec
Total Time     : 14.698 sec

I am using below command to load the data.

LOAD DATA LOCAL INFILE 'D:\\path\\Upload test.csv' 
INTO TABLE    test_db.innodb_upload_test FIELDS ESCAPED BY '\\' 
TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
(ID, FIRST_NAME, LAST_NAME, TEAM, DESIGNATION, COMPANY, ADDRESS_LINE_1, 
ADDRESS_LINE_2, CITY, POST_CODE);

Please suggest me how can we achieve faster csv upload using InnoDB.