1

For some reasons (that I think it is not the point of my question, but if it help, ask me and I can describe why), I need to check MySQL tables continuously for new records. If any new records come, I want to do some related actions that are not important now.

Question is, how I should continuously check the database to make sure I am using the lowest resources and getting the results, close to the realtime.

For now, I have this:

$new_record_come = false;

while(! $new_record_come) {
   $sql = "SELECT id FROM Notificatins WHERE insert_date > (NOW() - INTERVAL 5 SECONDS)";
   $result = $conn->query($sql);
   if ($result)
   {
      //doing some related actions...
      $new_record_come = true;
   }
   else
   {
      sleep(5); //5 seconds delay
   }
}

But I am worry that if I get thousands of users, it will make the server down, even if the server is a high price one!

Do you have any advice to make it better in performance or even change the way completely or even change the type of query or any other suggestion?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Reza Amya
  • 1,494
  • 2
  • 25
  • 40
  • @scaisEdge did you read my description? – Reza Amya Oct 07 '19 at 11:13
  • [How can I determine when an InnoDB table was last changed?](https://stackoverflow.com/questions/2785429/how-can-i-determine-when-an-innodb-table-was-last-changed) – Masivuye Cokile Oct 07 '19 at 11:13
  • @MasivuyeCokile why this way is better? because it will connect to DB and query again. then what is the benefit? – Reza Amya Oct 07 '19 at 11:15
  • 1
    @scaisEdge - 'cal trigger a procedure'? I am not clear what you are getting at , do you mean use a trigger to action on an insert? – P.Salmon Oct 07 '19 at 11:15
  • Depending what 'some related actions' are you may be able to use an after insert trigger which would mean that you only do work as and when required as opposed to searching 'continuously' to see of you need to do work and then doing it. – P.Salmon Oct 07 '19 at 11:18
  • A polling process conserves resources, a cron hook is more robust, and a trigger most timely but somewhat expensive. See also: [Invoking a PHP script from a MySQL trigger](//stackoverflow.com/q/1467369) – mario Oct 07 '19 at 11:29
  • @P.Salmon yes yes, it is a way that I can't use! because this loop is somewhere and insert/update tables will happen in somewhere else. – Reza Amya Oct 07 '19 at 11:50
  • it is somehow like the comments that will show here. you post your comment in your device and immediately it will alert me that there is a new comment. – Reza Amya Oct 07 '19 at 11:50
  • @mario, yes Mario, I have checked that way too. but please read my last comment. I even know that maybe here they are using Server-Sent Events, but even if they are using SSE, they need to check their database continuously. – Reza Amya Oct 07 '19 at 11:54
  • I second @scaisEdge's suggestion using triggers. I think they're the most efficient way, you can have the trigger insert on an update or insert, etc and store in a separate activity table so if there's nothing updated in that table there's no overhead on the database. https://dev.mysql.com/doc/refman/5.7/en/triggers.html – Aaron Belchamber Oct 07 '19 at 12:51
  • @AaronBelchamber did I understand correctly? You mean I need to create a table X, and using triggers on other tables to add update/insert notifications into the X, then in the loop, continuously check table X? – Reza Amya Oct 07 '19 at 12:57
  • @RezaAmya, I think this is the cleanest way. Unfortunately I guess because you still have to poll the database during your "heartbeat" increment of every 5 seconds the only performance increase would be that you could put all your monitoring in one place. It also looks like it's possible to execute a script upon a trigger, but it's not clear how that works. https://stackoverflow.com/questions/29820937/execute-shell-script-command-from-mysql-trigger-stored-procedure The ideal goal would be to avoid taxing the database by having to poll it continuously. – Aaron Belchamber Oct 07 '19 at 13:07

3 Answers3

3

Polling a database is costly, so you're right to be wary of that solution.

If you need to scale this application up to handle thousands of concurrent users, you probably should consider additional technology that complements the RDBMS.

For this, I'd suggest using a message queue. After an app inserts a new notification to the database, the app will also post an item to a topic on the message queue. Typically the primary key (id) is the item you post.

Meanwhile, other apps are listening to the topic. They don't need to do polling. The way message queues work is that the client just waits until there's a new item in the queue. The wait will return the item.

A comment suggested using a trigger to invoke a PHP script. This won't work, because triggers execute while the transaction that spawned them is not yet committed. So if the trigger runs a PHP script, which probably needs to read the record from the database. But an uncommitted record is not visible to any other database session, so the PHP script can never read the data that it was notified about.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I understood the structure of message queue. but I didn't understand what is the technology behind it! can you please give me a reference to read more about that – Reza Amya Oct 07 '19 at 12:44
  • http://blog.thecodepath.com/2013/01/06/asynchronous-processing-in-web-applications-part-2-developers-need-to-understand-message-queues/ – Bill Karwin Oct 07 '19 at 12:47
  • https://www.slideshare.net/mwillbanks/message-queues-a-primer-international-php-conference-fall-2012 – Bill Karwin Oct 07 '19 at 12:48
  • "But an uncommitted record is not visible to any other database session" - unless you set the isolation level to read uncommitted or use the trigger to send that information to the php script. However, calling a php script from a trigger still does not make the change visible to the client. – Shadow Oct 07 '19 at 13:20
  • Using read-uncommitted is a horrifying thought. If the system _requires_ using that isolation level to work, that's a red flag. – Bill Karwin Oct 07 '19 at 16:11
0

Another angle (much simpler than message queue I think):

I once implemented this on a website by letting the clients poll AND compare it to their latest id they received. For example: You have a table with primary key, and want to watch if new items are added. But you don't want to set up a database connection and query the table if there is nothing new in it.

Let's say the primary key is named 'postid'.

I had a file containing the latest postid.

I updated it with each new entry in tblposts, so it contains alsways the latest postid.

The polling scripts on the clientside simply retrieved that file (do not use PHP, just let Apache serve it, much faster: name it lastpostid.txt or something).

Client compares to its internal latest postid. If it is bigger, the client requests the ones after the last one. This step DOES include a query.

Advantage is that you only query the database when something new is in, and you can also tell the PHP script what your latest postid was, so PHP can only fetch the later ones.

(Not sure if this will work in your situation becuase it assumes an increasing number meaning 'newer'.)

Erwin Moller
  • 2,375
  • 14
  • 22
  • Nice solution, I had same Idea but I was thinking to check it from the PHP loop that you said don't. Then, I need to do some AJAX time to time to check that file. Are you sure that it is better than using a loop inside the PHP? – Reza Amya Oct 07 '19 at 13:05
  • You PHP request end after some timeout unless you let PHP jump through loops. With a clientside Ajax call to a static file you have very low overhead on the server. – Erwin Moller Oct 07 '19 at 13:34
0

This might not be possible with your current system design but how about instead of using triggers or a heartbeat to poll the database continuously that you go where the updates, etc happen and from there execute other code? This way, you can avoid polling the database continuously and code will fire ONLY IF somebody initiates a request?

Aaron Belchamber
  • 1,480
  • 1
  • 16
  • 20