B4J Question jRDC2 execute statement from SQL trigger (MySQL)

[Alexander Stolte]

Hello,

my Problem is, i use firebase push (send push messages, subscribe to topics, unsubscribe) and this must act with my database (MySQL)...
Currently I'm doing this in a SQL Trigger, where I call a B4J script, but the problem is that it can come that I need to call 3 scripts in a trigger. The statement is only finished when the scripts are finished and so the user has to wait 10 seconds until his INSERT is finished, for example ...

That's why I need an alternative to it.
And since you can expand the jRDC, I wanted to ask how you can approach this thing.

since the scripts were all written with B4J I can just create classes in the jRDC, but I'm unsure about the parameters I have to pass and how can I control this in retrospect via the database.

greetings

 

[keirS]

One way of doing it is to use polling instead. What you do is create copy of the table structure of the table you are inserting in to as an in memory table using CREATE TABLE ENGINE=MEMORY. In your trigger you insert the same data into the memory table as the main table.

You then need a B4J server app that loops through the in memory table and does the notifications. When it has sent the notification for a row it deletes the row from the in memory table. This way your are only ever looking at newly inserted rows and the table remains small and because it's an in memory table the CRUD and select operations are very fast.

 

[Alexander Stolte]

this sounds very Interesting but can this also work if I update rows?

 

[keirS]

Yes just do exactly the same thing with an update trigger. You should be able to use the same in memory table to do both given a bit tinkering with the structure of the table.

 

[Alexander Stolte]

loops through the in memory table

do you have an example or an tutorial what helped you?

 

[keirS]

do you have an example or an tutorial what helped you?

There are plenty of tutorials on B4J server. Nothing specific to what you want to do though. If you look at the JRDC2 source code that pretty much tells you how to setup a server app.

 

[Alexander Stolte]

i know, that was not the question, but does not matter.

So I have to spam the database to look for new lines in the in memory table right?

 

[keirS]

You just need a loop that does a select on the memory table. Then goes down each row and does the notifications an delete the row once done.

 

[Alexander Stolte]

I have my concern that this does not work if per second 1000 new records are written

 

[OliverA]

You could also use the INSERT triggers to insert information into a "queue" table. You can use a timer to check the queue table and process any information contained within. This way, your INSERT should finish quickly and the data is already contained in you database. If you use the IN MEMORY table, then your database would only be updated after you process the IN MEMORY table. If something goes wrong, you may loose all the INSERTS from the MEMORY table. Look at CloudKVS for a queue processing mechanism (look at the client code).

I have my concern that this does not work if per second 1000 new records are written

If you really have that much activity and the processing of each push message takes 10 seconds (as per your first post), you're going to never catch up with your push messaging. For each batch of 1000 records/second, the push messaging would take almost 3 hours to process. 9 seconds of such inserts would take 24 hours of push message processing. Something does not add up.

 

[Alexander Stolte]

The main goal is extremely good performance, but meanwhile I have to keep in mind that this is impossible with a notification system... but without notifications a social network is only half a network.
So I have to find a solution that does not affect performance or slow down processes.

What can you recommend?

 

[OliverA]

Can you batch the notifications? In other words, can the notification contain information on more than one insert event? The you could still use the queue system and sent out all related information in one notification. That would then lower the notification frequency/amount as compared to the incoming data.

 

[Alexander Stolte]

This is not possible because the notifications are sent to different rooms. The notifications must already be sent in RealTime.

 

[keirS]

You could also use the INSERT triggers to insert information into a "queue" table. You can use a timer to check the queue table and process any information contained within. This way, your INSERT should finish quickly and the data is already contained in you database. If you use the IN MEMORY table, then your database would only be updated after you process the IN MEMORY table. If something goes wrong, you may loose all the INSERTS from the MEMORY table. Look at CloudKVS for a queue processing mechanism (look at the client code).

This is just plain wrong. The trigger is for the insert into the main table. The secondary insert from the trigger is to the memory table. The trigger would be an AFTER_INSERT trigger which means it would be called after the main table has already been updated.

If you really have that much activity and the processing of each push message takes 10 seconds (as per your first post), you're going to never catch up with your push messaging. For each batch of 1000 records/second, the push messaging would take almost 3 hours to process. 9 seconds of such inserts would take 24 hours of push message processing. Something does not add up.

He is running a shell script from the trigger at the moment. A lot of that 10 seconds is loading up a JVM and running the app. Trying to execute 1000 shell execute's a second to load a JVM is going to kill the server very very quickly.

 

[keirS]

The main goal is extremely good performance, but meanwhile I have to keep in mind that this is impossible with a notification system... but without notifications a social network is only half a network.
So I have to find a solution that does not affect performance or slow down processes.

What can you recommend?

Use Amazon Aurora and Amazon SNS. You can send SNS notifications directly from a trigger and it's scalable to a massive size.

 

[Alexander Stolte]

Use Amazon Aurora and Amazon SNS.

I have to look, since my project is very large and I really want to stay with jRDC2, I would have to go away now from jRDC2, then it throws me back a few months.

I have tested a little and if I stop the time and send a message via button, this takes between 400-600 milliseconds. (on my home PC with a 8K internet connection) For this reason, I will program a service that can handle this (without starting a script), with your tips, I now know how to start. Thanks @keirS and @OliverA

 

[keirS]

I have to look, since my project is very large and I really want to stay with jRDC2, I would have to go away now from jRDC2, then it throws me back a few months.

I have tested a little and if I stop the time and send a message via button, this takes between 400-600 milliseconds. (on my home PC with a 8K internet connection) For this reason, I will program a service that can handle this (without starting a script), with your tips, I now know how to start. Thanks @keirS and @OliverA

You can use JRDC2 with Aurora. It's compatible with MySql so either MySQL or MariaDB drivers should work.