...

View Full Version : Script is maxing out memory. WHY ???? (painfully urgent)



mOrloff
03-05-2010, 12:27 AM
I have a script which was working wonderfully for a few weeks, then suddenly it started maxing out the memory.
We bumped up the memory_limit in the php.ini, it worked for a wink, then re-exibited the same symptoms. Just to crutch it along, I re-upped the mem limit to 128m, and it didn't take long to hit that.

I don't think anyone changed anything in the script, and the only change I can come up with (and it doesn't even seem all that related to me) is that I added another Table to one of the DB's which this script accesses (prior to this, it was a single-table db).

I don't make any SELECT * queries.
I only select the columns which I need.

What are some likely culprits ??
Where should I look ??

HELP !!

FYI, here's the error from the log:
[04-Mar-2010 22:41:31] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 74 bytes) in blah/blah/script.php on line 147
The line referenced there is simply the opening of a while loop


while($row = mysql_fetch_assoc($result)) {


If you would like additional info, please just let me know.


The script is over 300 lines of code so I'm hoping to see if there are any "usual suspects" to eliminate prior to asking anyone to go through all that. ;)


~ Mo

skywalker2208
03-05-2010, 12:36 AM
Try checking the query that you are trying to run on that line. If you have phpmyadmin or any other query browser software then try adding EXPLAIN to the front of the query to figure out what the query is doing.

Example


EXPLAIN SELECT id, name, other_field FROM mytable WHERE ....


If you don't understand what the explain is returning when you run it then take a screenshot or if you can copy and paste the info. It will look something like the image below.
http://farm4.static.flickr.com/3373/3654622374_c580b67590_o.png

JAY6390
03-05-2010, 12:45 AM
The number of lines of code are not important really. The real question is why do you need to select out all this data at once, is there a more streamlined query you can use instead. Also, have you considered paginating your output (if outputting large amounts of results). The full script would be beneficial (up to that point at least)

mOrloff
03-05-2010, 01:39 AM
Thanks-a-BUNCH for your responses.
I didn't realize how much time I had burned trying to get it going, and I had to leave for an appointment.
It looks like the sales manager just has to wait until the morning now :( (My development machine is at the office)

I'll run your suggestion first thing, and post my position.

~ Mo

mOrloff
03-05-2010, 01:54 AM
... why do you need to select out all this data at once, is there a more streamlined query you can use instead? ...
Only pertinent records are being grabbed (not all the data at once), and this is how:


$fields='pn, AVG(pricing) AS pricing'; // query component: all the needed columns
$table='vend1'; // query component: look in this table
$condition='pn LIKE "'.$word.'%"'; // query component: get anything that starts with the word
$orderBy=NULL; // query component
$groupBy='pn'; // query component
$result=qryFunc($fields,$table,$condition,$orderBy,$groupBy); // run it again to include any partial matches
...
while($row = mysql_fetch_assoc($result)) ...

mOrloff
03-05-2010, 04:11 PM
Alright.
I ran this query:


SELECT pn, AVG(pricing) AS pricing
FROM vend1
WHERE pn LIKE "1n4148%"
GROUP BY pn

And it went smooth as silk. 28 records were returned.

Here's a screencap from the EXPLAIN:
http://www.voyagercomponents.com/download/scrnCap_explainedSelectQry.jpg

Any ideas on what could be chewing through sooo much ram??

~ Mo

JAY6390
03-05-2010, 04:17 PM
To use that amount of memory, you're either loading in a lot of data, or looping excessively. As I originally said, it would be easier if you posted the code to diagnose this. The code you have provided so far is highly vague and not of any great use on its own

abduraooft
03-05-2010, 04:23 PM
SELECT pn, AVG(pricing) AS pricing
FROM vend1
WHERE pn LIKE "1n4148%"
GROUP BY pn Since you are using LIKE to match the records, mysql can't use any indexes and thus it loops though all the 716166 records in your table. This might be the cause. A good DB design and proper indexing would be the only solution.

mOrloff
03-05-2010, 04:41 PM
... it would be easier if you posted the code to diagnose this...
Alright, you asked for it :rolleyes:
If there aren't any usual suspects for me to knock off first, then I guess the next step is to lay it ALL out for you guys.

This is a script to which I have emails piped.
It chew through the subject and msg grabbing anything that has potential to be a part number, then compares those strings to a lexicon of part numbers.
For those which come back with a match prices are then gathered, and a quote email is returned to the sender.

I tried to attach the file, but the "attachments" feature isn't working for some reason, so I zipped it and posted it for download here:
http://www.voyagercomponents.com/download/quoter.zip (http://www.voyagercomponents.com/download/quoter.zip)

Thanks-a-bunch.

mOrloff
03-05-2010, 05:46 PM
... using LIKE ... might be the cause. A good DB design and proper indexing would be the only solution.
Thanks, I'll see what I can figure out.

Would that problem match the symptoms where it evidently just keeps adding to the memory storage ??
It runs successfully several times until it finally hits the limit, and it was running on 32 MB for weeks, then suddenly maxed it out.
Then, maxed out 64MB after just being run a few times, then 128MB after just another handful of run cycles.

???

Thanks-a-bunch,
~ Mo

Fumigator
03-05-2010, 05:52 PM
Are you using persistent connections to MySQL?

mOrloff
03-05-2010, 06:13 PM
You know, I'm not sure.
How do I find out?

~ Mo

Fumigator
03-05-2010, 06:31 PM
You would be calling mysql_pconnect() rather than mysql_connect().

mOrloff
03-05-2010, 06:36 PM
In that case, no I am not.

Fumigator
03-05-2010, 06:50 PM
That's a puzzler... PHP does its own garbage collection and each request is independant of the one before so why wouldn't the memory be freed?

If you put a counter in your while() loop and echo it, how many times does it loop through and fetch data before it errors out?

(Edit) One other question, is this quoter.php called inside a loop from another script? It just sounds like a runaway script to me but I can't really see why it would be running away based on the code.

mOrloff
03-05-2010, 07:02 PM
... If you put a counter in your while() loop and echo it, how many times does it loop through and fetch data before it errors out? ...
I'll try to check that out.


... One other question, is this quoter.php called inside a loop from another script? ...
Nope. It's all by its lonesome :)

mOrloff
03-05-2010, 07:33 PM
Two things:
1) I can't even get it to run because (I presume) the memory is so plugged up.
Is there a way to see what's in the memory??
Is there a way to force a memory dump??
If so, would that also dump any memory allocated to other operations?

2) Looking at the error log, it's says it maxed out the ram on that line when it tried to allocate 74 bytes.
Would that mean that the ram is all getting gobbled up prior to that line ??
How likely is it that this WHILE loop is just an innocent bystander ?

~ Mo

Fumigator
03-05-2010, 08:12 PM
Bounce Apache, that'll reset everything. You've definitely got your own little memory leak there, somehow. I thought PHP was pretty good about managing memory but you've managed to find a way! :p

Fou-Lu
03-05-2010, 08:41 PM
Did this error indicate that it failed to allocate memory because there was no more ram available, or that it failed to allocate memory because it had exhausted the memory provided for it?
To me there sounds like two potential possibilities on this one: the first is PHP is for whatever reason crashing, resulting in unfree'd memory. And two, that you're mysql is causing the memory leak. Querying the data itself shouldn't be an issue, regardless of the data you're retrieving since the SQL engine itself only caches a certain amount of data before paging it.

This all said, if you're experiencing memory issues, the first thing I'd do is monitor you're running process to see if it is indeed apache, php, or mysql causing the problem. If it is, look and upgrading any of these versions to correct the issues. Oracle extension is the last changelog that I read indicating a memory leak correction which wasn't all that long ago.

You should be able to find applications that will allow you to monitor processes to see if potential memory leaks may exist. Its also possible you just don't have a lot of ram on the server in question.

Daniel Israel
03-05-2010, 08:48 PM
Since it's a loop on a query result, why don't you output the counts of rows you've returned? Since you're creating arrays of arrays, each time through it's using more memory. It's a *LOT* of 74 byte requests to max out 128M.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum