I have a process that opens 60 Iframe windows per server, that each does part of the task. The mysql tables are not indexed, the data is being inserted 1000 rows at a time in a bulk insert (first written to a temp memory created in each frame then bulk offloaded to the hd table). This is processing 1.5 billion rows of data per table, that is being written to by the 60 frames running of a 32 core 128g ram server, it maxes out the server cpu to 99-100% for 6 - 7 hrs. I have 3 servers processing this task, and 1 more with 24 drives on raid as the db server, that it's only running 6 - 15% cpu load to manage mysql (5.0.67) . Where could the bottleneck be happening that going from 1 to 3 servers processing the data, doesn't increase the "total" data written? Each of the 3 is writing to 1 db table, table locks are minimal, that I could flip it to each frame writes to 1 table eliminating locks but I don't think this is where the issue is considering 180 connections continual write only amounts to 5 - 10 locks per second. Removing indexing did speed up the write speed considerably, (I'll have to deal with that once all data is written). Is there a mysql value / option / ? that would improve this, I have 160 more tables to create ? I wish I could use Ram tables, but the data alone is 33 gig for each table and mysql 5.0.67 wont do that, does anyone thing that switching to 5.6 be a huge difference ? I'm stumped, mysql says it's writing on avg 47 mb / s, key efficiency = 100%, query cache hitrate = 93 - 100%. Any help is much appreciated thank you all for any input.
Do you open 60 iframes at the same time?
This means all processes are parallel. Things do not get faster like this, instead they slow down.
Somewhere the bottleneck is related to read the table from the disk. Multiplying concurrent disk access is slowing down everything. Not even to talk about simultaneaus longrunning queries, that may block each other in the DB server.
Compare the 47 Mb/s written to these values:
You are close to both