How can I make PHP scripts timeout gracefully while waiting for long-running MySQL queries?

Question

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:

1. "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.

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

3. "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.

Answer 1

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.

Answer 2

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.

Answer 3

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?