CODEWITHSUNDEEP
phpmysqlmemory-managementpdo
tkINtenn
tkINtenn

Reputation: 3

php memory usage while loop

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();
    }

}

Upvotes: 0

Views: 818

Answers (2)

Lokesh Pandey
Lokesh Pandey

Reputation: 1789

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;
}

Upvotes: 1

Ambika
Ambika

Reputation: 594

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.

Upvotes: 0

Related Questions