mysql - Bulk INSERTs from multiple hosts preformace optimization -
i have 15 amazon aws ec2 t1.micro inctances simultaneusly populate amazon rds mysql d2.m2.xlarge database data using large inserts
(40000 rows in query).
the queries send continuously. table innodb, 2 int columns, there index both columns. cpu utilization of rds instance 30% during data receiving.
when have 1 ec2 instance, speed in orders faster run 15 instances simultaneusly. , 15-instances group work slower , slower until speed becomes totally unsatisfactory.
how can optimize performance of process?
upd: show create table
results following:
create table `userdata` ( `uid` int(11) not null, `data` int(11) not null, primary key (`uid`,`data`), key `uid` (`uid`), key `data` (`data`) ) engine=innodb default charset=latin1
i need 2 indexes cause nessecary me fetch data uid
, data
value.
i insert data insert userdata (uid, data) values (1,2),(1,3),(1,10),...
40000 (uid,data)
pairs.
15 parallel instances insert ~121 000 000 rows in 2 hours, sure can more faster.
i can sense problem in these of areas :
point 1) t1.micro burst instance , spikey compute workloads. since had mentioned using continuously db queries/transactions, can show reduced overall performance. please change instance type better predictable ones.
point 2) m2.xlarge , t1.micro comes limited nw bandwidth capacity. in case if volume of data (in mb/sec) transacting/querying db above nw bandwidth capacity can observe reduced throughput. imagine if 15 t1.micro trying push 100mb/sec , more nw pipe exists between t1.micros , db instance type
Comments
Post a Comment