Pulling data from API, Allowed memory exhausted

Question

I'm working on a project where I pull data (JSON) from an API. I would like to manipulate this data and store this as useful information in my DB (MySQL). My code is working if I would like to make, for example 2000 requests.

My script step by step:

1. Get IDS from DB, store them in array urls.
2. Take a chunk
3. Set and execute multi cURL
4. Check for error
5. Manipulate data
6. Store Data
7. Merge urls that should be requested again

Now my problem, when I make around 4000+ request I get after 400 loops the fatal error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20268759 bytes) in C:... on line 586

Of course I could increase the allowed memory size, but that is just a temporary fix. Because I can't keep increasing the size when the number of request increase. I have been playing around with memory_get_usage() & memory_get_peak_usage() And see that both are increasing slowly. Until memory_get_peak_usage() almost reaches the allowed size and then I get the error.

As I see it the memory-usage shouldn't be increasing, every loop I do the same and the variables are overwritten. So my question is: What am I doing wrong? How can I find the problem and how do I fix it? Do you see any major error in my code?

<?php

include ('connect.php');

set_time_limit(0);

$api_key = '***';

$ids = mysqli_query($connect, "SELECT Id FROM `table` LIMIT 2000");

$urls = array();
while($result = mysqli_fetch_array($ids))
{
    $urls[] = 'https://website.com/api/json/' . $ids['id'] . '?api_key=' . $api_key;
}

//number of urls called simultaneously
$threads = 10;

//create the multiple cURL handle
$mh = curl_multi_init();

while($urls){

     //take the first chunk ($threads) of all urls
    $chunk = array_splice($urls, 0, $threads);  

    //create a cURL resource for all urls in this chunk
    foreach($chunk AS $k => $url){

        //create cURL resources
        $ch[$k] = curl_init();

        //set URL and other appropriate options
        curl_setopt($ch[$k], CURLOPT_URL, $url);
        curl_setopt($ch[$k], CURLOPT_RETURNTRANSFER, true );
        curl_setopt($ch[$k], CURLOPT_SSL_VERIFYPEER, false);

        //add the handles
        curl_multi_add_handle($mh,$ch[$k]);
    }

    //execute the handles
    do {
        curl_multi_exec($mh, $running);
    } while ($running);


    //Return the content for all urls
    foreach($chunk AS $k => $url){

        $result = curl_multi_getcontent($ch[$k]);

        //get info about the request
        $error = curl_getinfo($ch[$k], CURLINFO_HTTP_CODE);

        //error handling if not 200 ok code
        if($error != 200){

            //when its one of the following errors try again later.
            if($error == 429 || $error == 500 || $error == 503 || $error == 504){
                $again[] = $url;
            } else {
                $errors[] = array("Url" => $url, "errornbr" => $error);
            }

        //no errors
        } else {

            //parse the json
            $decoded = json_decode($result,true);

            //////////////////////////////
            //    Process/manipulate all data
            //////////////////////////////

            //query for the information
            $$sqlInformation[] =
                "('" .
                $id ."','" .                              
                $one . "','" .                          
                $two . "','" .                              
                $three . "','" .                                    
                ...             
                "')";

            $sql =  "INSERT INTO `Information` (`id`, `one`, `two`, `three` ...)
                    VALUES " . implode(",",$sqlInformation) . "";

            if (mysqli_query($connect, $sql)) {
                echo "New information is created successfully";
            } else {
                echo "Error: " . $sql . "<br>" . mysqli_error($connect);
            }

        curl_multi_remove_handle($mh, $ch[$k]);
        curl_close($ch[$k]);

        }
    }

if(isset($again)){
    $urls = array_merge($urls, $again);
    unset($again);
}

//sleep, requestrate
sleep(10);
}

curl_multi_close($mh);
?>

Answer 1

I have a few observations about your script:

1. You are executing DB queries in a loop: foreach(chunk...)...mysqli_query.... This means that for 4,000 urls, you will query the DB 4,000 times (assuming no unrecoverable error). This is expensive and will slow your script down considerably and lead to a lot of overhead. You're better off building and storing all the $sql commands you will want to execute in an array. Once you've finished your cURL operations, implode the array into one big INSERT operations (look up how to insert multiple records with one query).

2. The docs for mysql_query show that the default behavior is to store results in memory. I suspect that because you make so many queries without closing the connection, your memory is filling up (I'm unsure though since almost all your queries are INSERT rather than SELECT). I suggest you change that behavior by passing a MYSQLI_USE_RESULT as the 3rd argument. This means that after you've processed the result with your script you must execute mysql_free_result() before making the next query.

3. This has less to do with performance and more with maintainability and scalability; consider breaking out the functionality into several single purpose functions: fetchUrls() could return your $urls array, curlExec($chunk, &$results, &$errors, &$again) could make the cURL requests and add to the $results,$errors and $again arrays (notice I passed them by reference). You would iterate over that function, re-inserting the again urls at the back of the line until you have processed all urls into just $results and $errors array. Next, dbInsert($results) would implode all results into one big INSERT operation and a single request to the DB

In general my approach to finding the culprit would be to then comment out individual steps of the operation and run the script, watching the memory usage. Eventually you'll pinpoint the memory hog.