PHP Memory Issue - Basic MYSQL Query Question

patsfans

I have a server that keeps running out of memory, and I'm trying to pinpoint the issue. It's a pretty large database made up of a product catalog being queried, and one thing I read was that storing the results of a database query in a variable was generally the culprit, and using -> or => vs just the variable was one best practice but I haven't found a good example explaining the benefits or its usage.

For arguments sake, let's say I have a basic query like this:


$sql= ("SELECT variable1, variable2, variable3 FROM table1");

while ($row = mysql_fetch_array($sql)) {

$variable1 = ($row ['variable1']);
$variable2 = ($row ['variable2']);
$variable3 = ($row ['variable3']);

}

Is there a better way to write this for the sake of not taking up as much memory? Thanks in advance, I appreciate it.

NogDog

For the code you've shown and assuming none of those DB fields is a huge BLOB or TEXT object, memory should not be an issue. Where it might be would be if your while() loop was adding elements to an array, such that by the time the loop was complete, all the query result data would now be stored in RAM (pretty inefficiently, by the way) in a PHP array. If that's the case, then one way to approach it is to never put it into an array in the first place -- assuming you are just going to loop through that array anyway, you might as well just loop through the query result set. (This gets ugly, though, in situation where you're working with a template system or such that is better able to cope with an array than with a query result set.)

All that aside, the first thing to do is to get some metering into your code to find out for sure what part of it is, in fact, chewing up all the memory.

bradgrafelman

patsfans;11019781 wrote:
$variable1 = ($row ['variable1']);
$variable2 = ($row ['variable2']);
$variable3 = ($row ['variable3']);[/code]

Why not use $row['variable1'] (et al.) inside the loop rather than duplicating the content in a different variable?

EDIT: And besides, if you're trying to locate memory hogs, why not profile your code rather than guess?

patsfans

NogDog;11019789 wrote:
For the code you've shown and assuming none of those DB fields is a huge BLOB or TEXT object, memory should not be an issue. Where it might be would be if your while() loop was adding elements to an array, such that by the time the loop was complete, all the query result data would now be stored in RAM (pretty inefficiently, by the way) in a PHP array. If that's the case, then one way to approach it is to never put it into an array in the first place -- assuming you are just going to loop through that array anyway, you might as well just loop through the query result set. (This gets ugly, though, in situation where you're working with a template system or such that is better able to cope with an array than with a query result set.)

All that aside, the first thing to do is to get some metering into your code to find out for sure what part of it is, in fact, chewing up all the memory.

That's where I'm baffled. The result is pretty basic and typically outputted as:


$output .= "<tr><td>$variable1 - $variable2: $variable3</td></tr>";

and then echoed in between "<table></table>" outside on the page.

Now there are a few instances where some large tables are joined together to obtain results, but again, they're outputted as shown above. Other than that there's not any real intricate queries that should be chewing it up the way it is. I thought maybe because it's storing all the results into a variable was the issue, but I haven't been able to pinpoint it.

I thought maybe it was the fact that newer versions of PHP seem to be using different methods in querying results that make better use of the server resources and that I was missing something?

bradgrafelman

patsfans;11019795 wrote:
I thought maybe because it's storing all the results into a variable was the issue

I suppose it wouldn't help, but you'd probably only need to be concerned with this for very large result sets.

patsfans;11019795 wrote:
I thought maybe it was the fact that newer versions of PHP seem to be using different methods in querying results that make better use of the server resources and that I was missing something?

Something like Stop Using the MySQL Extension!?

patsfans;11019795 wrote:
but I haven't been able to pinpoint it.

I'll refer back to my previous statement:

bradgrafelman;11019791 wrote:
if you're trying to locate memory hogs, why not profile your code rather than guess?

If you don't use a full-blown profiler/debugger (e.g. Xdebug) - even though this is probably what you should be doing - note you can still use the brute force technique of sprinkling print statements that output [man]memory_get_usage/man throughout your code; the ones that show large deltas will indicate the boundaries that mark sections of code that are large contributors to memory usage.

patsfans

bradgrafelman;11019797 wrote:
I suppose it wouldn't help, but you'd probably only need to be concerned with this for very large result sets.

There occasionally are, which is why I was trying to see about modifying the way I've been doing this since I know PHP has evolved quite a bit and there are some newer methods I'm not familiar with.

Something like Stop Using the MySQL Extension!?

That's interesting - I hadn't seen that, so I appreciate you pointing that out.

I'll refer back to my previous statement:

If you don't use a full-blown profiler/debugger (e.g. Xdebug) - even though this is probably what you should be doing - note you can still use the brute force technique of sprinkling print statements that output [man]memory_get_usage/man throughout your code; the ones that show large deltas will indicate the boundaries that mark sections of code that are large contributors to memory usage.

The early debugging didn't find any errors, I just keep seeing a jump in memory use and once a week it seems like we have to reboot the server when it runs out of memory. So that's why I figured I'd ask to see if there was something I was potentially doing wrong. I've run memory_get_usage throughout on one script but it seems like it stays pretty stable and doesn't climb - which is why I've been somewhat confused. During some queries there are Joins which process a lot of data (LEFT JOINS) but they're outputted and written like above - so as I said I didn't understand what the problem is.

Bonesnap

As a temporary fix you can increase the memory limit of your PHP scripts in your php.ini file.

NogDog

You can even set the limit within the script via ini_set() and the memory_limit setting.

dalecosp

patsfans;11019801 wrote:
once a week it seems like we have to reboot the server

Ah, that's the rub ... a Windows® Server! 😃