在等待长时间运行的 MySQL 查询时，如何使 PHP 脚本优雅地超时？
我有一个运行大量数据库查询的 PHP 站点。使用某些参数组合，这些查询最终可能会运行很长时间，从而触发丑陋的超时消息。我想根据我网站的其余部分风格将其替换为一个漂亮的超时消息主题。
预期此类问题的常见答案：
“优化您的查询，使它们不会运行太长时间” - 我正在记录长时间运行的查询并优化它们，但我只有在用户使用后才知道这些
“增加 PHP 超时设置（例如 set_time_limit、max_execution_time），以便长时间运行的查询可以完成” - 有时查询可能会运行几分钟。我想告诉用户在此之前（例如 30 秒后）存在问题。
“使用 register_tick_function 监视脚本运行了多长时间” - 这只在我的脚本中的代码行之间执行。当脚本等待数据库的响应时，tick 函数不会被调用。
如果有帮助的话，该网站是使用 Drupal（具有大量自定义功能）构建的，并在 PHP 5.2 和 MySQL 5 上的虚拟专用 Linux 服务器上运行。
I have a PHP site which runs quite a lot of database queries. With certain combinations of parameters, these queries can end up running for a long time, triggering an ugly timeout message. I want to replace this with a nice timeout message themed according to the rest of my site style.
Anticipating the usual answers to this kind of question:
"Optimise your queries so they don't run for so long" - I am logging long-running queries and optimising them, but I only know about these after a user has been affected.
"Increase your PHP timeout setting (e.g. set_time_limit, max_execution_time) so that the long-running query can finish" - Sometimes the query can run for several minutes. I want to tell the user there's a problem before that (e.g. after 30 seconds).
"Use register_tick_function to monitor how long scripts have been running" - This only gets executed between lines of code in my script. While the script is waiting for a response from the database, the tick function doesn't get called.
In case it helps, the site is built using Drupal (with lots of customisation), and is running on a virtual dedicated Linux server on PHP 5.2 with MySQL 5.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
没有异步 mysql 调用，也没有分叉轻量级线程的范围。
虽然您可以将 PHP 代码分成两层，并在它们之间使用可以异步调用的连接，但这种方法的问题是，在上层放弃获取结果后，数据库层仍会尝试运行查询- 可能会阻止其他用户使用 DBMS。 （您更有可能收到更频繁的超时页面请求）。
如果您将超时处理推入位于网络服务器前面的反向代理，您也会遇到同样的问题。
实现超时的最明智的地方是数据库本身 - 但据我所知，mysql不支持这一点。
因此，下一个选择是在 PHP 和数据库之间构建一个代理 - 这可以是独立的 - 为每个请求生成 2 个轻量级线程（1 个用于运行查询，第二个作为看门狗，如果需要太长时间则杀死第一个线程） -但这不仅需要用支持轻量级线程的语言编写代码，还需要定义与 PHP 通信的协议。
然而，对代理模型采取不同的方法 - 您可以使用 proc_open 生成一个单独的 PHP 进程并将 stdout 流设置为非阻塞 - 这样您的 PHP 可以继续运行并检查代理是否已运行查询。如果超时，那么作为代理的父级，它可以向其发出关闭信号（proc_terminate()），这应该停止在数据库上运行的查询。
当然，这意味着大量的开发工作。
事实证明，设置一个或多个从属 DBMS 来运行慢速查询可能要简单得多 - 可能具有智能负载平衡。或者看看其他使缓慢的查询变得更快的方法 - 比如预合并。
HTH
C.
There is no asynchronous mysql calls and no scope for forking lightweight threads.
While you could split your PHP code into two tiers and use a connection between them which you can invoke asynchronously, the problem with this approach is the DB tier will still try to run the query after the upper tier has given up on getting the results back - potentially blocking the DBMS for other users. (you're more likely to get more frequent requests for pages which are timing out).
You'll have the same problem if you push the timeout handling up into a reverse proxy sitting in front of the webserver.
The most sensible place to implement the timeout is the database itself - but AFAIK, mysql does not support that.
So next option is building a proxy between the PHP and the database - this could be self-contained - generating 2 lighweight threads for each request (1 to run the query, 2nd as a watchdog to kill the first if it takes too long) - but this not only requires writing code in a lnaguage which supports lightweight threads, but also defining a protocol for communications with the PHP.
However taking a different approach to the proxy model - you could spawn a separate PHP process using proc_open and set the stdout stream to be non-blocking - that way your PHP can continue to run and check to see if the proxy has run the query. If it times out, then as the parent of the proxy, it can signal it to shutdown (proc_terminate()) which should stop the query running on the database.
Certainly, it's going to mean a lot of development work.
It may prove a lot simpler to set up one or more slave DBMS to run your slow queries against - potentially with smart load balancing. Or look at other ways of making the slow queries go faster - like pre-consolidation.
HTH
C.
连接处理文档就是您所需要的。
基本上，您需要使用 register_shutdown_function()< 注册关闭函数/a>.每当脚本完成时都会调用此函数，无论它是否已成功完成、被用户取消（ESC 键）或已超时。
然后，该关闭函数可以调用 connection_status() 函数。如果connection_status()返回2（超时）并且上一页是运行麻烦查询的页面，您可以将用户重定向到一个页面，显示“抱歉，我们现在服务器负载很高。”< /em> 或其他什么。
The connection handling docs are what you need.
Basically, you need to register a shutdown function using register_shutdown_function(). This function will be called whenever a script is finished, regardless of whether it has completed successfully, been cancelled by the user (ESC key), or has timed out.
That shutdown function can then call the connection_status() function. If connection_status() returns 2 (TIMEOUT) and the previous page was the one that runs the troublesome query, you can redirect the user to a page saying "Sorry, but we're experiencing high server load right now." or whatever.
您的服务器是否使用 APC、Memcache、Boost 和 Drupal Cache 进行了调整？这些是非常有效的替代路线。
否则，Drupal 中运行什么样的脚本会导致这种情况？只是出于好奇，您正在运行视图和面板吗？
Is your server tuned with APC, Memcache, Boost and Drupal Cache? Those are alternate routes that work very well.
Otherwise, what kind of scripts are running in Drupal that would cause this? Just out of curiosity, are you running Views and Panels?