MySQL Forums
Forum List  »  InnoDB

Bulk Insert
Posted by: Sam Mathews
Date: June 11, 2017 07:26AM

Hi, I am using MYSQL community edition 5.6 with INNODB storage engine.
Everyday there is a bulk insert of approximate 300-600gb data in one table.
What are best possible ways to insert data in minimum time.

Server configuration :
CPU xeon e5 2697A dual socket.
RAM 1.5 TB total 64 GB x24 DDR4 2133Mhz.
HDD 8 HDD 8TB each 12 Gbps NLSas 7.2 RPM.
NIC 10Gbps DA/SFP+ QLOGIC.
OS Centos 7 64 bit.
RAID 10 is being used.
CPU is multi-threaded

Currentry we are using insert statements which are taking very much time.

table structure

CREATE TABLE capt ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, datetime_col1 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', datetime_col2 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', txt_col1 varchar(128) DEFAULT NULL, txt_col2 varchar(128) DEFAULT NULL, txt_col3 varchar(6) DEFAULT NULL, txt_col4 varchar(90) DEFAULT NULL, txt_col5 varchar(90) DEFAULT NULL, txt_col6 varchar(255) DEFAULT NULL, num_col1 bigint(20) DEFAULT NULL, num_col2 bigint(20) DEFAULT NULL, txt_col7 varchar(500) DEFAULT NULL, txt_col8 varchar(255) DEFAULT NULL, PRIMARY KEY (id), KEY INDEX_txt_col1 (txt_col1), KEY INDEX_txt_col2 (txt_col2), KEY INDEX_datetime_col1 (datetime_col1), KEY INDEX_datetime_col2 (datetime_col2) ) ENGINE=InnoDB AUTO_INCREMENT=5724641293 DEFAULT CHARSET=utf8 |

Options: ReplyQuote


Subject
Views
Written By
Posted
Bulk Insert
1774
June 11, 2017 07:26AM
819
June 11, 2017 01:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.