On Fri, Oct 28, 2011 at 01:32:32PM -0400, James wrote:
> >
> >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 )) {
> >>
> >
> >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.
James:
Thanks for taking time to help.
The row size is small by my standards (see below). The query result
has just under 300,000 records, and it's puking about 90% of the way
through.
Changing the while loop to:
while ($row = mysql_fetch_assoc( $result )) {
$n++;
echo sprintf( "%7d %12d\n", $n, memory_get_peak_usage() );
} // while
the tail end of the output becomes:
274695 134203084
274696 134203524
274697 134203964
PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 240 bytes) in xx3.php on line 26
Changing the while loop further to:
while ($row = mysql_fetch_assoc( $result )) {
unset( $row );
$n++;
echo sprintf( "%7d %12d\n", $n, memory_get_peak_usage() );
} // while
the tail end of the output becomes:
274695 134202232
274696 134202672
274697 134203112
274698 134203552
274699 134203992
PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 240 bytes) in xx3.php on line 27
So it does get a little farther through the dataset, but not much.
Jim
mysql> describe test_table;
+----------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------+------+-----+---------+-------+
| contract | int(11) | YES | | NULL | |
| A | int(8) unsigned | NO | | 0 | |
| B | datetime | YES | | NULL | |
| C | int(8) unsigned | YES | | 0 | |
| D | char(8) | YES | | NULL | |
| E | char(8) | YES | | 0000 | |
| F | int(4) | YES | | 0 | |
| G | int(1) | YES | | 0 | |
| H | char(8) | YES | | 00:00 | |
| I | varchar(100) | YES | | XXX | |
+----------+-----------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
