Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

An old site I've inherited has a script that users can run from the CMS that basically pulls out all the products from the database table and exports them as a xls file. There aren't that many records, around 5000, and up until recently it's been working fine. As the amount of products has increased, the script execution time has also increased, and recently it's started exceeding the servers max_execution_time of 30 seconds.

When I look at the process running on the server, I notice that as soon as the TIME+ hits 60 seconds, the script dies.

If I limit the SQL results to say 4500, the script executes within 60 seconds, there are no problems.

The SQL Query seems to be fine. I checked the execution time and it's just under 40 seconds.

I also tried extending the scripts execution time by using both set_time_limit (120); and ini_set('max_execution_time', 120);, However when doing so, instead of running one time for 120 seconds, the script will run 2-3 times, each time for 60 seconds, dying, and coming back as a new process.

Any help would be greatly appreciated.

share|improve this question
2  
what do the logs say when it dies? apache error_log, php log? –  lunixbochs Jul 19 '12 at 4:15
    
Unfortunately, the apache error_log doesn't show anything for the times the script was executed.The php.ini shows that error_log isn't defined, and I can't find anything that looks like a php log on the server. I was hoping to solve this without rebooting the server, but It seems that I may not be that lucky. –  Jeemusu Jul 19 '12 at 5:04
    
It's definately the while loop in the php thats timing out. With the original behavior, the while loops takes the output of the database and loops through each of the 5000 odd records adding them all into 1 xls file, and timing out before finishing. However if I change it to run two consecutive while loops (building 2 files of 2500 records each), it executes without timing out. –  Jeemusu Jul 19 '12 at 5:43
2  
I would advice to enable php logging, if not you will just keep guessing the cause of the timeout. Howeever there is no need to restart the server. You could just gracefully restart apache (apachectl -k graceful). That will causes the parent process to advise the children to exit after their current request (or to exit immediately if they're not serving anything). The parent re-reads its configuration files and re-opens its log files. As each child dies off the parent replaces it with a child from the new generation of the configuration, which begins serving new requests immediately. –  golja Jul 19 '12 at 5:56
    
Enabled logging, and found that it wasn't the scripts timing out at all. It was the PEAR extension the previous developer had implemented choking when the xls file sizes got big. Thanks for the help :) –  Jeemusu Jul 19 '12 at 9:59
show 1 more comment

1 Answer

If the problem is on the database side, I suspect an index missing. Try to

EXPLAIN ANALYSE SELECT ...

the query that is sent to the server. It should tell you which parts are taking long. For a detailed explanation of the EXPLAIN, you could use http://explain.depesz.com/

Can you offload the xls-generation into a server-script in the background? The result could then either be linked on the site or mailed to the requesting person. This is the usual approach in the Rails-world (even though there is seldomly a timeout set).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.