I'm working on Apache Storm (but anyone who knows MySQL well could help too). Topology is like this:
Single spout ----emit---> Multiple instances of a Bolt
Each instance of the Bolt inserts a batch of rows to table ABC. The database table is located on a single server. The Bolt instances could be distributed across multiple servers.
Objective: To be able to speed up and scale the program by increasing the number of Bolt instances (ie: if processing x amount of data and writing to MySQL took 1 hour with 5 Bolts, it should take maybe 35 minutes if I use 10 Bolts).
1. No matter how many Bolts I use, the processing time will reduce, but each Bolt will have to wait for the other n-1 Bolts to finish inserting, before it can insert (not because the program logic makes it wait, but because SQL does not allow a process/bolt to write to SQL while another process/bolt is writing to the same table). This waiting time does not allow the program to scale.
2. A long wait time will cause Zookeeper to timeout.
3. Before this topology starts, the table ABC already has existing data, and whatever new inserts are done will eventually have to be added to table ABC.
A solution I thought of:
To let each Bolt write to its own temporary table
and when the Spout is finished, use SQL's
INSERT INTO command to take contents of the Temp tables and insert it into ABC.
Is this the most efficient && fast && scalable way to accomplish this or is there a better design || technique?
ps: If there are other databases (even noSQL DB's) which can help speed up the application by allowing concurrent inserts, I'm open to the idea.