1

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).

Problem:
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
ABC_TempBolt1
ABC_TempBolt2
ABC_TempBolt3
ABC_TempBolt4
ABC_TempBolt5
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.

  • 1
    An interesting approach. How can you guarantee that the primary key constraint will not be violated when using multiple, mutually independent Bolts? – Vladimir Stokic Jul 25 '16 at 12:26
  • 1
    @VladimirStokic: Using UUIDs would solve that. – Robert Harvey Jul 25 '16 at 13:27
  • I don't understand why your last bolt is waiting for the other n-1 bolts to finish. Is each bolt writing to the database continuously? Is there no slack time whatsoever between inserts? – Robert Harvey Jul 25 '16 at 13:29
  • None of the bolts wait for each other. It's just that when one bolt is writing, all the other bolts won't be able to write. There is slack time. Each Bolt Processes something, then writes a batch to SQL and does the same again and again. So when one bolt has written a batch, another bolt gets the chance to write. The processing happens very fast, so most of the bolts will be in the inserting stage and since SQL can't do parallel inserts, they'll all be waiting. – Nav Jul 25 '16 at 14:12
  • 1
    How long will it take to consolidate your bolt tables when the bolts are done executing? – Robert Harvey Jul 25 '16 at 14:56
1

If you have the locking mechanism in place on the table in the database, then the concurrent writing in it is not possible, so there is no other way to parallelize the writing. Simply, the database is the bottleneck. The only gain that you get is that the processing done by bolts before the data is written into database is done in parallel.

Therefore, the approach you used is pretty much optimal. The only way I see that you could speed up the inserts is by using batch inserts and by tweaking the database so that it is optimized for what you are trying to do. Take a look at the following links:

Bulk Data Loading for InnoDB Tables

Insert Speeds for large batches

As far as concurrent inserts, this might help:

MySQL Concurrent Inserts

I believe this would be of interest to you:

If there are multiple INSERT statements, they are queued and performed in sequence

Basically, you cannot insert two records simultaneously. They will always be queued, which means that the locking mechanism is in place implicitly.

Hope this helps.

  • I don't have any locking mechanism. The columns of the table are just an autoincremented id, and 4 columns which can have any random number that aren't dependent on each other. My supervisor says that in such conditions, MySQL would have internal algorithms to perform concurrent inserts. Is that true? Because I couldn't find anything to confirm it. – Nav Jul 27 '16 at 7:28
  • Oh one problem about writing to multiple tables and later take all the rows of the temp tables and put it into the main table is, that it works out to be the same as doing the inserts directly into the main table. One thing I noticed was that having an index on the main table slowed the inserts. Removing the index and then doing inserts is a better option. – Nav Jul 29 '16 at 16:38
  • Removing the index will definitely help speed up the inserts, but it will drastically reduce the reading speed from the table, especially with it having more than million rows. Consider the trade-off and what the table will primarily be used for. Also, you could try something like this: insert into ABC values (select * from ABC_TempBolt1 union select * ABC_TempBolt2 union select * from ABC_TempBolt3) This way, you will have only one insert command and it should be somewhat quicker. Do give it a shot and let me know how it went. – Vladimir Stokic Aug 1 '16 at 7:15
1

If the processing time is much more than the time spend on writing to the db for each bolt, you could use an output queue. Each bolt can write it's results to the queue and continue processing.

You could use a separate process to write entries from the queue to the table.

  • This is a very interesting approach. I'll also need a way to synchronize the writing process with the spout (because I need to know if the writing process crashes) and check that the queue does not overload or crash. As of now, the processing time is much smaller than the write time so I can't use your approach, but it has been very helpful to know this technique. – Nav Jul 26 '16 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.