How to improve performance of Bulk Inserts in MYSQL

Question

env: windows 10 
version mysql 5.7
Ram 32GB
ide : toad mysql

i have sufficient hardware requirement but issue is the performance of insert into simple table that does not have any relation ships. i need to have index on the table.

table structure

CREATE TABLE `2017` (
  `MOB_NO` bigint(20) DEFAULT NULL,
  `CAF_SLNO` varchar(50) DEFAULT NULL,
  `CNAME` varchar(58) DEFAULT NULL,
  `ACT_DATE` varchar(200) DEFAULT NULL,
  KEY `2017_index` (`MOB_NO`,`ACT_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I am using above for inserting the records into table. with out index it took around 30 min where as with indexing it took 22 hrs still going on.

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOAD DATA LOCAL INFILE 'D:/base/test/2017/2017.txt' 
INTO TABLE 2017COLUMNS TERMINATED BY '|';
commit;

i have seen suggestion to change cnf file, Could not find any in my machine.

it takes 39min and 22 sec with out index and with indexing it take 22 hrs still on. — raghavendra prasad gudipalli, May 23 '18 at 21:17
You still haven't answered the question: how many records are you trying to insert? — Barmar, May 23 '18 at 21:19
Count 11,308,147. i have 5 table with this count and more than this — raghavendra prasad gudipalli, May 23 '18 at 21:23
it should be quite obvious, but please don't use Indian words here — James Z, May 24 '18 at 03:44
You can try to split file into 2-4 chunks and load them in parallel. And definitely turn off inexes when loading. — Yuri Lachin, May 24 '18 at 11:47
I am able to achieve it finally to 2 hrs by adding the following in ini file innodb_autoinc_lock_mode =2 sync_binlog=1 bulk_insert_buffer_size=512M key_buffer_size=512M read_buffer = 50M — raghavendra prasad gudipalli, May 26 '18 at 18:11

score 1 · Accepted Answer · answered May 26 '18 at 18:13

By adding following lines in my.ini. I am able to achieve it.

innodb_autoinc_lock_mode =2
sync_binlog=1
bulk_insert_buffer_size=512M
key_buffer_size=512M
read_buffer = 50M

and innodb_flush_log_at_trx_commit=2, i have seen in another link where it said that it increase speed to 160x. Output performance :more than 24hr to 2 hrs

score 0 · Answer 2 · answered May 26 '18 at 18:32

0

If you begin with an empty table, create it without any indexes. Then, after fully populating the table, adding an index is reported to be faster than inserting with the index already in place.

See:

MySQL optimizing INSERT speed being slowed down because of indices
Is it better to create an index before filling a table with data, or after the data is in place?

Possibly helpful: Create an index on a huge MySQL production table without table locking

answered May 26 '18 at 18:32

wallyk

56,922
16
83
148

I have tried few suggestion mentioned in other questions 1. Alter table after adding data 2 create table dump and then create index 3. Create table with index and dump data. 4 change in initiative file which worked for me as the insertion rate increased – raghavendra prasad gudipalli May 26 '18 at 19:21

How to improve performance of Bulk Inserts in MYSQL

2 Answers2