You are here

Apache PHP MySQL and Runaway Scripts

MySQL Performance Blog - Wed, 21/05/2008 - 5:55am

Sometimes due to programming error or due to very complex query you can get your PHP script running too long, well after user stopped waiting for the page to render and went browsing other sites.
Looking at Server-Status I've seen scripts executing for hours sometimes which is obviously the problem - they take Apache Slot, MySQL Connection and other resources.

I had discussion today who was thinking Apache would kill the script after "Timeout" specified in Apache configuration is reached - this was not my experience so I decided to run couple of tests to check it.

I wrote couple of very simple scripts which demonstrate different behavior for scripts running very long time. For simplicity I did not use MySQL, but other system call - sleep() which has similar behavior for sake of experiment:

Script which simply runs in tight loop "forever"

PLAIN TEXT PHP:
  1. <?
  2. echo("Hello");
  3. $k=0;
  4. for($i=0;$i<100000000;$i++)
  5.   for($j=0;$j<10000;$j++)
  6.     $k=$k+1;
  7. ?>

Script which runs in tight loop but doing a lot of sleep() calls - so it takes a lot of time but does not really use any CPU time:

PLAIN TEXT PHP:
  1. <?
  2. echo("Hello");
  3. for($i=0;$i<10000;$i++)
  4.     sleep(1);
  5. ?>

Script which is same as previous one but which also outputs some data in the loop:

PLAIN TEXT PHP:
  1. <?
  2. echo("Hello");
  3. for($i=0;$i<10000;$i++)
  4. {
  5.     sleep(1);
  6.     echo('.');
  7.     ob_flush();
  8.     flush();
  9. }
  10. ?>

Script which checks connection status in the loop and exits if connection is not 0 (NORMAL)
Check connection handling manual for details.

PLAIN TEXT PHP:
  1. <?
  2. echo("Hello");
  3. for($i=0;$i<10000;$i++)
  4. {
  5.     sleep(1);
  6.     if(connection_aborted())
  7.         exit;
  8. }
  9. ?>

The testing I did was as follows - I would run the script and let it run checking "/Server-Status" checking if script dies after specified timeout. As the next step I pressed "STOP" button in the browser to abort connection and check if script continues to run.
ignore_user_abort was set to FALSE for running these tests.

Tests were done with PHP 5.2.5 and Apache 2.0.59.

Results were a bit surprising:

Running "TightLoop" script terminated after 30 seconds with "Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/html/tightloop.php on line 5" just as expected.

For rest of the scripts I could see them running for as long time as I wished and even if script did not output anything the 120 "Timeout" set on Apache size did not seem to work.

The STOP button discovery (and so script abortion) worked only in case script was outputing something. If script is just doing something like scripts or MySQL calls and does not output anything to the browser neither automatic termination not connection_aborted() check seems to work. More over you need both ob_flush() and flush() together with IO for it to work reliably as if no low level network send is attempted connection abort from client will not be discovered.

This is unfortunately very serious limitation in practice when you're using MySQL with PHP as you can have single runaway query which takes a lot of time and which does not allow you to do any output (to see if user is still waiting) while it happens.

Happily there is pcntl extension which you can enable and which gives you access to process control system calls including pcntl_alarm. Using this function you can set an alarm and have script terminated after certain amount of seconds:

PLAIN TEXT PHP:
  1. <?
  2. pcntl_alarm(5);
  3. echo("Hello");
  4. for($i=0;$i<10000;$i++)
  5.     sleep(1);
  6. ?>

Unlike set_time_limit() which specifies cpu time, pcntl_alarm() specifies number of wall clock seconds and so it is much more useful for scripts which spend most of their time waiting.

There is also pcntl_signal function which you can use to install your own timeout handling function which can be used for example to print nice error message as well as to do some cleanup work - for example you can open another MySQL connection to kill the query which was running (otherwise query may well continue to run even if PHP script is aborted).

Entry posted by peter | One comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks