What's the best practice for PHP to continuously check for changes in the database?

Question

I am building a backend module (written in PHP) that will be used for monitoring private chat rooms that has no activity for [Exactly] 300 seconds (5 minutes). If it is, the script will update the database (sets the max users to a certain number, and other stuffs). I am monitoring the span of idle time by the time difference of now() and last message sent.

---

What I did: set a cron job that will run (through php-cli) my monitoring script every minute or 60 seconds. Inside the monitoring script:

$expire_time = time() + 60;

//this loop will run for 60 seconds
while(time() < $expire_time)
{
  $idle_time = get_all_chatrooms_idle_time();
  foreach($idle_time as $s_time)
  {
    if($s_time >= 300)
    {
      update_changes();
    }
  }
  usleep(500000);
}

---

The condition of instantly setting max users after 300 seconds idle time can't be bargained. So I cant really follow advice like: "avoid doing anything until something actually asks for it" even though it makes a lot of sense.

Reason? The data of active and inactive chatrooms need to be real time because it will also be displayed on a dashboard. The chatroom moderators' pay depends on it.

---

Why not check them every dashboard load? I'm sorry but still not possible.

The checking needs to be server side and the dashboard updates itself with ajax, polling every second.

When I attach the monitoring code to the page being requested by my ajax calls I think it's more resource intensive than my current implementation (correct me if Im wrong)

Let me give you some rough estimate on the number of users so you can imagine the load/traffic we're getting:

• number of chatters including moderators: ~800
• number of chatrooms: ~250
• (x) number of chatroom moderators: ~50
• (x) my boss and his staff:

(x) - can view the dashboard

---

Is there a better way? Am I doing it right?

Answer 1

This loop is an overkill. It may run many thousand times a minute, even on a moderate server, and it generates high CPU usage even for a realtime-app. Add a counter, and see iteration count. I think this generates even more load than processing upon every AJAX request.

First of all, determine the granularity you need the information with. Suppose you choose to have 3 seconds of granularity (e.g. sweeping through the database every 3 seconds) - this number may be too high for you, but it illustrates that you don't lose much. With AJAX pulling every second you COULD see some counters that should crawl up continuously crawl back once or twice. (Whether you will really see such thing depends on the nature of your counters.)

If your counters are based on data in range of seconds (e.g. showing sums of elapsed seconds, or amounts based on $/sec) then second-wise AJAX pulling will not provide continuous counters. (It will sometimes miss a second or update to that second twice; for network reasons).

Regardless of the chosen granularity, your final statistics will be allright, because they are based on absolute timestamps - no matter how late they are evaluated.

If second-wise AJAX poll is used to implement smooth counter, than you can do much better than that: counting should run on the client side (e.g. sending values with their second-wise increment: revenue: <span data-inc="25">14432</span> and counting with JS). The only implement AJAX to monitor the condition of stopping/reseting counters. Then you only need to determine how long notification may be late (e.g. 10s) then counters will overscroll for max. 10s the drop back to the expected value. In this case you should not run DB cleanup much more often (e.g. half of the interval). That allows e.g. for a 3-second sleep in your cycle, which decreases load drastically.

If you can easily opt for adding the expiration timestamp of every chatroom to the database (either in-record or fixed) with an index that would speed up reading a bit (and additionally allow for per-room expiration rules).