Re: Re: [PHP] Why does this script run out of memory?

On Fri, 2011-10-28 at 13:32 -0400, James wrote: > >---- Original Message ---- > >From: Eric Butera <eric.butera@gmail.com> > >To: "Jim Long" <php@umpquanet.com> > >Cc: php-general@lists.php.net > >Sent: Fri, Oct 28, 2011, 1:22 PM > >Subject: Re: [PHP] Why does this script run out of memory? > > > >On Fri, Oct 28, 2011 at 12:38 PM, Jim Long <php@umpquanet.com> wrote: > >> I'm running PHP 5.3.8 on FreeBSD 8.2 with MySQL 5.1.55. > >> > >> The script below is designed to be able to WHILE it's way through > >> a MySQL query result set, and process each row. > >> > >> However, it runs out of memory a little after a quarter million > >> rows. The schema fields total to about 200 bytes per row, so > >> the row size doesn't seem very large. > >> > >> Why is this running out of memory? > >> > >> Thank you! > >> > >> Jim > >> > >> <?php > >> > >> $test_db_host = "localhost"; > >> $test_db_user = "foo"; > >> $test_db_pwd = "bar"; > >> $test_db_name = "farkle"; > >> > >> $db_host = $test_db_host; > >> $db_user = $test_db_user; > >> $db_name = $test_db_name; > >> $db_pwd = $test_db_pwd; > >> > >> if (!($db_conn = mysql_connect( $db_host, $db_user, $db_pwd ))) > >> die( "Can't connect to MySQL server\n" ); > >> > >> if (!mysql_select_db( $db_name, $db_conn )) > >> die( "Can't connect to database $db_name\n" ); > >> > >> $qry = "select * from test_table order by contract"; > >> > >> if ($result = mysql_query( $qry, $db_conn )) { > >> > >> $n = 0; > >> while ($row = mysql_fetch_assoc( $result )) { > >> // process row here > >> $n++; > >> } // while > >> > >> mysql_free_result($result); > >> echo "$n\n"; > >> > >> } else { > >> > >> die( mysql_error() . "\n" ); > >> > >> } > >> > >> ?> > >> > >> > >> PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate > >> 20 bytes) in xx3.php on line 24 > >> > >> Line 24 is: > >> > >> 24 while ($row = mysql_fetch_assoc( $result )) { > >> > >> > >> -- > >> PHP General Mailing List (http://www.php.net/) > >> To unsubscribe, visit: http://www.php.net/unsub.php > >> > >> > > > >Not sure what is happening inside "process row here," but I'm sure > >that is where your issue is. Instead of building some giant structure > >inside of that while statement you should flush it out to the screen. > > > >-- > >PHP General Mailing List (http://www.php.net/) > >To unsubscribe, visit: http://www.php.net/unsub.php > > Try unsetting the $row variable, you may be fetching extremely large rows but that's a big > if, because your script is allowed to allocate 128MB of memory before puking. Are you dealing with > very large data sets from the database? If you are dealing with large data sets, then try redefining > your query. > > > > I don't think that will help, as it gets reset each time it iterates the loop when it's given a new value. Have you tried narrowing down your query to only the fields that you need in your script? Instead of "SELECT * FROM test_table" try something like "SELECT field1, field2, etc FROM test_table" Another thing to try is running this in over the command line if you can. I've had a lot of success with memory intensive scripts like this. Although it may not help in your specific case, it's worth noting at least. -- Thanks, Ash http://www.ashleysheridan.co.uk