B4J Question jRDC2 execute statement from SQL trigger (MySQL)

Alexander Stolte

Expert
Licensed User
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

Well-Known Member
Licensed User
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.
 

keirS

Well-Known Member
Licensed User
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.
 

keirS

Well-Known Member
Licensed User
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.
 

keirS

Well-Known Member
Licensed User
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.
 

OliverA

Expert
Licensed User
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

Expert
Licensed User
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

Expert
Licensed User
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.
 

keirS

Well-Known Member
Licensed User
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

Well-Known Member
Licensed User
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

Expert
Licensed User
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

Well-Known Member
Licensed User
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.
 
Top