Long Running Php Script Starts To Really Slow Down Over The Course Of Its Execution.

[Unavailable]

I have a script that grabs data from the database and inserts it into a file.

 

The problem that I am running to is that the script starts off at a decent pase, but over the course of its execution it starts to slow down to a crawl. I have tried numerous ways of improving it, however I have had no luck.

 

As far as the database goes, there are no issues with selecting and retreving the data, since I have access to the sql logs.

 

Here is the code:

 

do {

    $rows = leads_query($total_rows);
    $total_rows +=  sizeof($rows);
    echo "Currently at Row: " . sprintf('%07d',$total_rows);
		    $fp = fopen($file_path, 'a');
    foreach ($rows as $row) {
	    fputcsv($fp, $row);
    }

    fclose($fp);
    unset($fp);

    if(!isset($start)) {
		    $start = memory_get_usage();
    }
    echo "\t\t".'Memory used ' .  memory_get_usage() . "\t\t Change:" . (memory_get_usage() - $start) . "\n\r";
    clearstatcache();
    gc_collect_cycles();
    sleep(.5);

   } while($rows !== false && sizeof($rows) >= 1);

 

Here is a sample of the log :

 

Currently at Row: 0000750 Memory used 7246592 Memory usage Change:112 Total Time to Process:0.184604ms

Currently at Row: 0001500 Memory used 7246264 Memory usage Change:-216 Total Time to Process:-0.419575ms

Currently at Row: 0002250 Memory used 7246376 Memory usage Change:-104 Total Time to Process:0.064959ms

Currently at Row: 0003000 Memory used 7246360 Memory usage Change:-120 Total Time to Process:0.072996ms

Currently at Row: 0003750 Memory used 7246408 Memory usage Change:-72 Total Time to Process:0.076072ms

.

.

.

.

.

Currently at Row: 0090000 Memory used 7270952 Memory usage Change:24472 Total Time to Process:-0.149417ms

Currently at Row: 0090750 Memory used 7270136 Memory usage Change:23656 Total Time to Process:-0.181204ms

Currently at Row: 0091500 Memory used 7270168 Memory usage Change:23688 Total Time to Process:-0.141491ms

Currently at Row: 0092250 Memory used 7267944 Memory usage Change:21464 Total Time to Process:-0.1634ms

Currently at Row: 0093000 Memory used 7271944 Memory usage Change:25464 Total Time to Process:-0.161227ms

.

.

.

.

.

Currently at Row: 0177000 Memory used 7270648 Memory usage Change:24168 Total Time to Process:0.540715ms

Currently at Row: 0177750 Memory used 7272696 Memory usage Change:26216 Total Time to Process:-0.436532ms

Currently at Row: 0178500 Memory used 7272408 Memory usage Change:25928 Total Time to Process:-0.413038ms

Currently at Row: 0179250 Memory used 7268152 Memory usage Change:21672 Total Time to Process:0.568742ms

Currently at Row: 0180000 Memory used 7273016 Memory usage Change:26536 Total Time to Process:-0.419238ms

 

I would really appreciate any help, thanks.

[Christian F.]

I'd probably use implode ("\n", $rows) and file_put_contents () instead of that entire block.

 

It cuts down on the number of operations quite drastically, by cutting out all of that code, and that should increase the efficiency your code quite a bit; Internal functions are (almost) always faster than what you write yourself in PHP. Especially when we're talking about a loop within a loop.

However, the biggest performance gain from that is that it'll remove all of the unnecessary file opening and closings. Considering that you open and close the file inside the loop. So instead of opening and closing the file once, you're doing it at least 240 times! (Using the numbers you've provided above.)

Edited November 3, 2012 by Christian F.

[DavidAM]

The OP is using fputcsv() so there would be a little bit more to do than just implode() and file_put_contents().

 

I agree though, the file open should be moved to before the do loop and the file close should be moved to after the do loop. I don't see any reason in that code to open and close it each time.

 

I also do not see any reason to call clearstatcache or gc_collect_cycles. Since we don't know what "leads_query()" is doing, I could be wrong, but I see nothing in the presented code implying the need for it.

 

Also, sleep requires an integer, you are passing it a floating point value. I don't see a need to sleep here, but if you want it, you need to use a 1 (one) not .5 (one-half).

 

Most of those times are negative, but you don't show how you generated that value, so I don't understand what is going on there.

 

I would be concerned about the consistent increase in memory use.

 

By the way, you do realize that if leads_query() returns false, you are still going to try to process it as an array of data, right? I think I would rewrite that as:

 

$fp = fopen($file_path, 'a');
$total_rows = 0;

$start = memory_get_usage();

while ($rows = leads_query($total_rows)) {
   $total_rows +=  sizeof($rows);
   echo "Currently at Row: " . sprintf('%07d',$total_rows);

   foreach ($rows as $row) {
       fputcsv($fp, $row);
   }

   echo "\t\t".'Memory used ' .  memory_get_usage() . "\t\t Change:" . (memory_get_usage() - $start) . "\n\r";
}

fclose($fp);

 

You might try profiling the leads_query() function to see if there is a bottleneck there. Does it open and close the database connection each time? (it should not). Do you free the query resource after retrieving it? (you should).