On Fri, 2011-10-28 at 13:32 -0400, James wrote:
> >---- Original Message ----
> >From: Eric Butera <eric.butera@xxxxxxxxx>
> >To: "Jim Long" <php@xxxxxxxxxxxxx>
> >Cc: php-general@xxxxxxxxxxxxx
> >Sent: Fri, Oct 28, 2011, 1:22 PM
> >Subject: Re: Why does this script run out of memory?
> >
> >On Fri, Oct 28, 2011 at 12:38 PM, Jim Long <php@xxxxxxxxxxxxx> 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
