php memory usage while loop

Question

I'm using a while loop to pull records from mysql. this is to reset a log that was jacked up on accident, so I'm fixing the previous records by pulling the ID and time-stamp from a backup of the DB and updating the production table with those values.

I was hoping to limit memory usage and thought this solution would use much less, as there are 12,545,628 records in this log table. Pull just the ID and time-stamp fields it busts through 1GB of memory pretty quick.

Is this limiting the memory usage to one record at a time, or is it using the same as a straight pull of all ID's and time-stamps and foreaching over those?

(i've setup a database class so the PDO might look weird)

$sql = 'SELECT id, timestamp FROM log_people WHERE 1';
$Test->sqlQuery($sql);

while ($testdb = $Test->sth->fetch(PDO::FETCH_ASSOC)) { 
    $Prod->beginTransaction();

    try {
         $sql2 = 'UPDATE log_people SET timestamp = "'.$testdb['timestamp'].'" WHERE id = '.$testdb['id'];
         $Prod->sqlQuery($sql2);


    } catch (exception $err) {
        $Prod->rollback();
        echo $err->getMessage();
    }
}

Should I be doing the code below to just pull and modify one record at a time?

while ($count < 12545628){
    $sql = 'SELECT id, timestamp FROM log_people WHERE id ='.$count;
    $Test->sqlQuery($sql);
    $testdb = $Test->sth->fetch(PDO::FETCH_ASSOC)

$Prod->beginTransaction();

try {
    $sql2 = 'UPDATE log_people SET timestamp = "'.$testdb['timestamp'].'" WHERE id = '.$testdb['id'];
    $Prod->sqlQuery($sql2);
     $count++;


} catch (exception $err) {
        $Prod->rollback();
        //return $err->getMessage();
        echo $err->getMessage();
    }

}

So tell us - why do you need to select all records? If all records should be updated - why select them? — u_mulder
– u_mulder, Commented Aug 2, 2016 at 18:35
Couldn't you do this much more quickly and easily by putting a copy of the backup table in the same DB and using SQL to update the production table? Why do it in PHP? — Ryan Russon
– Ryan Russon, Commented Aug 2, 2016 at 21:07

Lokesh Pandey · Accepted Answer · 2016-08-02 18:59:01Z

I also don't understand why you need to select all records when you are updating those records. Anyway there are few tips which will help you to take care of the memory usage.
First point There is a functions which will tell you where your script is running out of memory and you can check it using this function memory_get_peak_usage(); .
Second point You can also reassign null to vars along with un-setting them. The unset(); function is useful when the garbage collector is doing its rounds however until then the unset(); function simply destroys the variable reference to the data, the data still exists in memory and PHP sees the memory as in use despite no longer having a pointer to it. The solution: Assign null to your variables to clear the data, at least until the garbage collector gets a hold of it. $var = null; You can also use unset(); to unset the variable pointer, however there is little difference in memory usage, as far as I can see:

unset($var);

Third point Another way you can do that by destruct your object references upon disposing of an object.

protected function __distruct()
{
  $this-&gt;childObject = null;
}

Ambika · Accepted Answer · 2016-08-02 18:48:00Z

0

In your problem, you are updating your table with same previous values. In terms of memory, memory limit of php can be defined in php.ini or by php code. And your second code is memory efficient as compared to first but time consuming because of separated queries for each id.

answered Aug 2, 2016 at 18:48

Ambika

5942 silver badges11 bronze badges

Collectives™ on Stack Overflow

php memory usage while loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related