PHP 持续检查数据库中的变化的最佳实践是什么? [英] What's the best practice for PHP to continuously check for changes in the database?

问题描述

我正在构建一个后端模块(用 PHP 编写)，用于监视 [正好] 300 秒(5 分钟)没有活动的私人聊天室.如果是，脚本将更新数据库(将最大用户数设置为特定数量，以及其他内容).我正在通过 now() 和最后一条消息发送的时间差来监控空闲时间的跨度.

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.

我做了什么:设置一个 cron 作业，每分钟或 60 秒运行一次(通过 php-cli)我的监控脚本.监控脚本内部:

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);
}

<小时>

闲置300秒后立即设置最​​大用户数的条件无法讨价还价.所以我不能真正遵循这样的建议:避免做任何事情，直到真正要求它为止"，即使它很有意义.

---

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.

检查需要在服务器端进行，仪表板使用 ajax 更新自身，每秒轮询一次.

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

当我将监控代码附加到我的 ajax 调用所请求的页面时，我认为它比我当前的实现更占用资源(如果我错了，请纠正我)

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:

• 包括版主在内的聊天数量:~800
• 聊天室数量:~250
• (x) 聊天室版主数量:~50
• (x) 我的老板和他的员工:

(x) - 可以查看仪表板

(x) - can view the dashboard

有没有更好的办法?我做得对吗?

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

推荐答案

这个循环有点矫枉过正.即使在中等服务器上，它也可能每分钟运行数千次，并且即使对于实时应用程序也会产生高 CPU 使用率.添加一个计数器，并查看迭代计数.我认为这比处理每个 AJAX 请求会产生更多的负载.

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.

首先，确定您需要信息的粒度.假设您选择了 3 秒的粒度(例如每 3 秒扫描一次数据库)——这个数字对您来说可能太高了，但它说明您不会损失太多.随着 AJAX 每秒拉动一次，您可以看到一些应该连续向上爬行的计数器会向后爬行一两次.(你是否真的会看到这样的东西取决于你的计数器的性质.)

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

如果您的计数器基于秒范围内的数据(例如显示经过的秒数的总和，或基于 $/sec 的数量)，那么二次 AJAX 拉取将不会提供连续的计数器.(有时会错过一秒或两次更新到该秒；由于网络原因).

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.

如果使用第二次 AJAX 轮询来实现平滑计数器，那么您可以做的比这更好:计数应该在客户端运行(例如，发送带有第二次增量的值:revenue: <span data-inc="25">14432</span> 并用 JS 计数).唯一实现 AJAX 来监视停止/重置计数器的条件.然后，您只需要确定通知可能延迟多长时间(例如 10 秒)，然后计数器将最大滚动.10s 回落到预期值.在这种情况下，您不应该更频繁地运行数据库清理(例如间隔的一半).这允许例如在您的周期中睡 3 秒，这将大大减少负荷.

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

这篇关于PHP 持续检查数据库中的变化的最佳实践是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！