Memory leak in PHP script

Question

I have a php script that launches a mysql query, then processes the result, and several queries are also executed in this loop:

$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 request never ends.

For some reason, some memory has memory_get_usage added between two calls to memory_get_usage . Since a large cycle runs about 500,000 or more times, after all, it really adds to a lot of memory. Is something missing here?
Could it be possible that memory is not actually added between two calls, but at a different point in the script?

Edit: additional information: Before the cycle, about 5 MB, after the cycle, about 440 MB, and each update request adds about 250 bytes. (the rest of the memory is added to other places in the loop). The reason I no longer wrote about "other things" anymore is that there are about 300 lines of code. I posted this part because it looks where most of the memory is added.

Answer 1

From the php.net memory_get_usage manual :

Parameters

real_usage Set this to TRUE to get the real size of the memory allocated from the system. If not set or FALSE, only emalloc () memory is used.

If this parameter is set to true, the script did not show an increase in memory, as I expected.

Answer 2

This memory leak will only be a problem if it destroys the script with an "exhausted memory" error. PHP will be happy to collect any unusual objects / variables on its own, but the collector will not kick until it is needed - garbage collection can be a very expensive operation.

It is normal to see a rise in memory usage, even if you constantly reuse the same objects / variables - this is not until the memory usage has exceeded a certain level that the collector will start and clean at home.

I suspect that you can do something a lot faster if you have grouped user IDs and released fewer updates, changing entries every time. For example, follow these steps:

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

instead of doing this one update to the user. Fewer rounds through the DB interface layer and more server time = faster startup.

We’ll also consider the impact of expanding this on millions of users now, as you say in the commentary. For millions of individual updates, it will take a non-trivial amount of time to run, so NOW() will not be a “constant”. If it takes 5 minutes to fully run, you will get a wide selection of ursUpdDate . You might want to consider caching a single NOW() call in a server-side variable and issue updates for this variable:

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

Answer 3

The best way, probably, is to get all userIds and delete them into a file. Then run a new script that forks with pipes on x number of working drones. Then just give them a small list of userIds to process as they finish. With multiple processors / cores / servers, you can complete the task faster. If one employee does not work, just start a new one. To use other servers as workers, you can call them using curl / fopen / soap / etc from the workflow.

Answer 4

I think you should try calling mysql_free_result() at some point in the loop. - From the comments

It is worth noting that mysql_query () returns a resource only for SELECT , SHOW , EXPLAIN and DESCRIBE .

Thus, there is no result for the update request.

In any case, your approach is not the best for a start. Instead, try mysqli paramterized statements or (even better) update the rows in the database directly. It seems that all of the SQL in the loop can be processed with a single UPDATE statement.

Answer 5

Part of the reason you can see the extra memory used at each iteration is that PHP has not yet collected unnecessary things that are no longer referenced.

Answer 6

The unset call is pointless / inactive. Try mysql_free_result , although this may have some effect.