On Fri, 2011-10-28 at 13:32 -0400, James wrote:
> >---- Original Message ----
> >From: Eric Butera <eric.but...@gmail.com>
> >To: "Jim Long" <p...@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 <p...@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
