memory leak in php script

Question

I have a php script that runs a mysql query, then loops the result, and in that loop also runs several queries:

    $sqlstr = "SELECT * FROM user_pred WHERE uprType != 2 AND uprTurn=$turn ORDER BY uprUserTeamIdFK";
    $utmres = mysql_query($sqlstr) or trigger_error($termerror = __FILE__." - ".__LINE__.": ".mysql_error());
    while($utmrow = mysql_fetch_array($utmres, MYSQL_ASSOC)) {
// some stuff happens here    
//  echo memory_get_usage() . " - 1241<br/>\n";
        $sqlstr = "UPDATE user_roundscores SET ursUpdDate=NOW(),ursScore=$score WHERE ursUserTeamIdFK=$userteamid";
        if(!mysql_query($sqlstr)) {
            $err_crit++;
            $cLog->WriteLogFile("Failed to UPDATE user_roundscores record for user $userid - teamuserid: $userteamid\n");
            echo "Failed to UPDATE user_roundscores record for user $userid - teamuserid: $userteamid<br>\n";
            break;
        }
    unset($sqlstr);
    //  echo memory_get_usage() . " - 1253<br/>\n";
// some stuff happens here too
}

The update query never fails.

For some reason, between the two calls of memory_get_usage, there is some memory added. Because the big loop runs about 500.000 or more times, in the end it really adds up to alot of memory. Is there anything I'm missing here?
could it herhaps be that the memory is not actually added between the two calls, but at another point in the script?

Edit: some extra info: Before the loop it's at about 5mb, after the loop about 440mb, and every update query adds about 250 bytes. (the rest of the memory gets added at other places in the loop). The reason I didn't post more of the "other stuff" is because its about 300 lines of code. I posted this part because it looks to be where the most memory is added.

Answer 1

This memory leak would only be a problem if it's killing the script with a "memory exhausted" error. PHP will happily garbage collect any unusued objects/variables on its own, but the collector won't kick until it has to - garbage collection can be a very expensive operation.

It's normal to see memory usage climb even if you're constantly reusing the same objects/variables - it's not until memory usage exceeds a certain level that the collector will fire up and clean house.

I suspect that you could make things run much faster if you batched userIDs into groups and issued fewer updates, changing more records with each. e.g. do the following:

UPDATE user_roundscores SET ursUpdDate=NOW() WHERE ursUserTeamIdFK IN (id1, id2, id3, id4, id5, etc...)

instead of doing it one-update-per-user. Fewer round-trips through the DB interface layer and more time on the server = faster running.

As well, consider the impact of now expanding this to millions of users, as you say in a comment. A million individual updates will take a non-trivial amount of time to run, so the NOW() will not be a "constant". If it takes 5 minutes to do the full run, then you're going to get a wide variety of ursUpdDate timestamps. You may want to consider cacheing a single NOW() call in a server-side variable and issue the updates against that variable:

 SELECT @cachednow :p NOW();
 UPDATE .... SET ursUpDate = @cachednow WHERE ....;