Re: [PHP] Why does this script run out of memory?
I had a spider written in PHP long ago. I had similar problems.
because there were millions of rows of urls and I was fetching them in
one single query. See inline, could this modification help you. Please
test.
On Fri, Oct 28, 2011 at 10:38 PM, Jim Long 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
>
>
> $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" );
>
$limit=10;
$offset=0;
while(1){
> $qry = "select * from test_table order by contract $offset, $limit";
>
> 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" );
// break the loop
break;
>
> }
$offset+=$limit;
}
> ?>
>
>
Its the same thing but you are fetching data in chunks.
Now this portion "order by contract" on quarter million rows is not a
good practice. It will slow down your query time and make the script
severely slow.
I had about 100 millions of rows in my table in the url and I was
sorting on last-visit column. Later I removed the order by and it was
much faster.
Try it and let us know.
Thanks
--
Shiplu Mokadd.im
Follow me, http://twitter.com/shiplu
Innovation distinguishes between follower and leader
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_fetch_array() vs mysql_fetch_assoc() WAS: Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 18:48, Jim Long wrote: > > I'm not seeing any numeric keys in my mysql_fetch_assoc() arrays. You're absolutely correct, that's my mistake: substitute mysql_fetch_row() for mysql_fetch_assoc(). Duh. Time to call it a week -- Network Infrastructure Manager http://www.php.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_fetch_array() vs mysql_fetch_assoc() WAS: Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 06:19:56PM -0400, Daniel Brown wrote:
> On Fri, Oct 28, 2011 at 18:13, Paul Halliday wrote:
> >
> > Whats the difference between fetch_assoc and fetch_row?
> >
> > I use:
> > while ($row = mysql_fetch_row($theQuery)) {
> > ? ?doCartwheel;
> > }
> >
> > on just under 300 million rows and nothing craps out. I have
> > memory_limit set to 4GB though. Although, IIRC I pushed it up for GD
> > not mysql issues.
> >
> > Same OS and php ver, MySQL is 5.1.48
>
> Please don't hijack other's threads to ask a question. I've
> started this as a new thread to address this question.
>
> mysql_fetch_array() grabs all of the data and places it in a
> simple numerically-keyed array.
>
> By contrast, mysql_fetch_assoc() grabs it and populates an
> associative array. This means that the column names (or aliases, et
> cetera) become the keys for the array. With mysql_fetch_assoc(), you
> can still call an array key by number, but it's not vice-versa with
> mysql_fetch_array().
I'm not seeing any numeric keys in my mysql_fetch_assoc() arrays.
However, mysql_fetch_row (by default) does both: the array will be
indexed numerically from 0 to N-1 corresponding to the table's N
columns, and the array will also have string key indices which
correspond to the query's column names. So by default,
mysql_fetch_row uses twice the amount of data, because each field
appears in the array twice.
var_dump( $row ) will show in graphic detail.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: mysql_fetch_array() vs mysql_fetch_assoc() WAS: Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 7:19 PM, Daniel Brown wrote:
> On Fri, Oct 28, 2011 at 18:13, Paul Halliday wrote:
>>
>> Whats the difference between fetch_assoc and fetch_row?
>>
>> I use:
>> while ($row = mysql_fetch_row($theQuery)) {
>> doCartwheel;
>> }
>>
>> on just under 300 million rows and nothing craps out. I have
>> memory_limit set to 4GB though. Although, IIRC I pushed it up for GD
>> not mysql issues.
>>
>> Same OS and php ver, MySQL is 5.1.48
>
> Please don't hijack other's threads to ask a question. I've
> started this as a new thread to address this question.
>
> mysql_fetch_array() grabs all of the data and places it in a
> simple numerically-keyed array.
>
> By contrast, mysql_fetch_assoc() grabs it and populates an
> associative array. This means that the column names (or aliases, et
> cetera) become the keys for the array. With mysql_fetch_assoc(), you
> can still call an array key by number, but it's not vice-versa with
> mysql_fetch_array().
>
> The difference in overhead, if you meant that (in which case, my
> apologies for reading it as a question of functional difference), is
> variable: it's based mainly on the difference between the bytes
> representing the integers used as keys in mysql_fetch_array() versus
> the size in bytes of the strings used as keys in mysql_fetch_assoc().
>
> --
>
> Network Infrastructure Manager
> http://www.php.net/
>
Sorry.
I was just throwing it out there with the hope that there might be a
tidbit that would help the OP.
I have a simliar setup and I can query far more than a 1/4 million
rows. What I offered is what I am doing differently.
--
Paul Halliday
http://www.squertproject.org/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 02:57:02PM -0700, Tommy Pham wrote: > On Fri, Oct 28, 2011 at 9:38 AM, Jim Long wrote: > > > I'm running PHP 5.3.8 on FreeBSD 8.2 with MySQL 5.1.55. > > > > > Jim, > > Installed from packages or standard port tree build? Did you do any tweak > for the ports build? Any special compiler parameters in your make.conf? > I've noticed that you used MySQL extensions. Have you tried MySQLi to see > if there's any difference? > > Regards, > Tommy MySQL server and PHP and extensions built from ports, without any local tweaks. Nothing very interesting in /etc/make.conf: MASTER_SITE_FREEBSD=1 CPUTYPE?=p3 USA_RESIDENT=YES NO_INET6=YES WITHOUT_IPV6=YES NO_I4B=true NO_BLUETOOTH=true NO_IPFILTER=true NO_KERBEROS=true NO_ATM=true # do not build ATM related programs and libraries NOUUCP=true # do not build uucp related programs NO_UUCP=true # do not build uucp related programs NO_GAMES=true NO_PROFILE=true PERL_VERSION=5.10.1 Port options for php5-extensions are: _OPTIONS_READ=php5-extensions-1.5 WITH_BCMATH=true WITHOUT_BZ2=true WITHOUT_CALENDAR=true WITH_CTYPE=true WITHOUT_CURL=true WITHOUT_DBA=true WITH_DOM=true WITHOUT_EXIF=true WITH_FILEINFO=true WITH_FILTER=true WITHOUT_FRIBIDI=true WITH_FTP=true WITHOUT_GD=true WITHOUT_GETTEXT=true WITHOUT_GMP=true WITH_HASH=true WITHOUT_ICONV=true WITHOUT_IMAP=true WITHOUT_INTERBASE=true WITH_JSON=true WITHOUT_LDAP=true WITHOUT_MBSTRING=true WITHOUT_MCRYPT=true WITHOUT_MSSQL=true WITH_MYSQL=true WITHOUT_MYSQLI=true WITHOUT_ODBC=true WITHOUT_OPENSSL=true WITHOUT_PCNTL=true WITH_PDF=true WITH_PDO=true WITHOUT_PDO_SQLITE=true WITH_PGSQL=true WITH_POSIX=true WITHOUT_PSPELL=true WITHOUT_READLINE=true WITHOUT_RECODE=true WITH_SESSION=true WITHOUT_SHMOP=true WITH_SIMPLEXML=true WITHOUT_SNMP=true WITHOUT_SOAP=true WITHOUT_SOCKETS=true WITHOUT_SQLITE=true WITHOUT_SQLITE3=true WITHOUT_SYBASE_CT=true WITHOUT_SYSVMSG=true WITHOUT_SYSVSEM=true WITHOUT_SYSVSHM=true WITHOUT_TIDY=true WITH_TOKENIZER=true WITHOUT_WDDX=true WITH_XML=true WITH_XMLREADER=true WITHOUT_XMLRPC=true WITH_XMLWRITER=true WITHOUT_XSL=true WITHOUT_YAZ=true WITHOUT_ZIP=true WITHOUT_ZLIB=true As Daniel suggested, using mysql_query_unbuffered works a treat, at the expense of a small amount of additional programming complexity. In my prior work with Postgres, I found that it would handle small or large datasets with equal ease, so I was surprised to find that MySQL blew up given a sufficient number of repeated calls to mysql_fetch_row(); Thank you for mentioning MySQLi. Although it is alphabetically adjacent in the documentation, it had never drawn my attention. I'll build the PHP extension and take a look when time permits. Jim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql_fetch_array() vs mysql_fetch_assoc() WAS: Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 18:13, Paul Halliday wrote:
>
> Whats the difference between fetch_assoc and fetch_row?
>
> I use:
> while ($row = mysql_fetch_row($theQuery)) {
> doCartwheel;
> }
>
> on just under 300 million rows and nothing craps out. I have
> memory_limit set to 4GB though. Although, IIRC I pushed it up for GD
> not mysql issues.
>
> Same OS and php ver, MySQL is 5.1.48
Please don't hijack other's threads to ask a question. I've
started this as a new thread to address this question.
mysql_fetch_array() grabs all of the data and places it in a
simple numerically-keyed array.
By contrast, mysql_fetch_assoc() grabs it and populates an
associative array. This means that the column names (or aliases, et
cetera) become the keys for the array. With mysql_fetch_assoc(), you
can still call an array key by number, but it's not vice-versa with
mysql_fetch_array().
The difference in overhead, if you meant that (in which case, my
apologies for reading it as a question of functional difference), is
variable: it's based mainly on the difference between the bytes
representing the integers used as keys in mysql_fetch_array() versus
the size in bytes of the strings used as keys in mysql_fetch_assoc().
--
Network Infrastructure Manager
http://www.php.net/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 1:38 PM, Jim Long 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
>
>
> $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
>
Whats the difference between fetch_assoc and fetch_row?
I use:
while ($row = mysql_fetch_row($theQuery)) {
doCartwheel;
}
on just under 300 million rows and nothing craps out. I have
memory_limit set to 4GB though. Although, IIRC I pushed it up for GD
not mysql issues.
Same OS and php ver, MySQL is 5.1.48
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 9:38 AM, Jim Long wrote: > I'm running PHP 5.3.8 on FreeBSD 8.2 with MySQL 5.1.55. > > Jim, Installed from packages or standard port tree build? Did you do any tweak for the ports build? Any special compiler parameters in your make.conf? I've noticed that you used MySQL extensions. Have you tried MySQLi to see if there's any difference? Regards, Tommy
Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 03:42:48PM -0400, Eric Butera wrote:
> On Fri, Oct 28, 2011 at 3:29 PM, Daniel Brown wrote:
> > On Fri, Oct 28, 2011 at 13:25, Jim Long wrote:
> >>
> >> Eric:
> >>
> >> Thanks for your reply.
> >>
> >> "process row here" is a comment. ??It doesn't do anything. ??The
> >> script, exactly as shown, runs out of memory, exactly as shown.
> >
> > ?? ??My response presumes that you're planning on placing something
> > into this comment area, in which the memory will only further
> > increase. ??If *presumed* should be replaced by *ASSumed* in this case,
> > skip mysql_unbuffered_query() and go straight for mysql_num_rows().
> > Do not pass GO. ??Do not collect $200.
> >
> > --
> >
> > Network Infrastructure Manager
> > http://www.php.net/
> >
>
> I was glad to learn what comments were.
Eric: Please forgive me if I was curt in my message to you. I
don't mean to bite the hands that are trying to help. As Daniel
rightly observed, my concern is just that if a pretty much empty
while loop runs out of memory when trying to step through each
record, I'm really going to be hosed if I try to start doing some
productive work within the while loop.
Daniel's memory trend analysis is helpful. I'm testing from the
command line, so there's no Apache overhead, but the memory usage
starts as:
1 12145496
2 12145976
3 12146408
4 12146804
5 12147200
6 12147596
...
I normally prefer to work in PostgreSQL, but the client has already
gone down the MySQL road. Just for edification's sake, I exported
the table in PostgreSQL and re-worked my code:
if (!($db_conn = pg_connect( "host=$db_host user=$db_user dbname=$db_name
password=$db_pwd" )))
die( "Can't connect to SQL server\n" );
$qry = "select * from test_table order by contract";
if ($result = pg_query( $db_conn, $qry )) {
$n = 0;
while ($row = pg_fetch_assoc( $result )) {
unset( $row );
$n++;
echo sprintf( "%7d %12d\n", $n, memory_get_peak_usage() );
} // while
pg_free_result($result);
echo "$n\n";
} else {
die( pg_last_error() . "\n" );
}
Using PostgreSQL (on a completely different machine), this runs
to completion, and memory consumption is nearly flat:
1 329412
2 329724
3 329796
4 329796
5 329796
...
295283 329860
295284 329860
295285 329860
295286 329860
295287 329860
295287
If one were to describe the memory consumption as a 'leak', then
PostgreSQL is leaking at a much slower rate than MySQL. Postgres
leaks as much over the entire run (329860-329412=448) as MySQL
does on each row. Put another way, the MySQL version leaks
memory almost 300,000 times faster.
My PostgreSQL machine also has MySQL installed, so I ran the
MySQL version of the code on that machine for testing, a second
opinion if you like. It leaked memory almost as bad as my
client's PHP/MySQL installation, but a little more slowly, 396
bytes or so per row. The slower memory consumption enabled the
code to run to completion, barely:
1 12149492
2 12149972
3 12150404
4 12150800
...
295284129087704
295285129088100
295286129088496
295287129088892
295287
So is this just a difference in the programming quality of the
database extensions for MySQL vs. PostgreSQL that one gobbles up
memory profusely, while the other one has only a slight, slow
leak?
I will try experimenting with Daniel's idea of unbuffered
queries, but my understanding is that while an unbuffered result
resource is in use, no other SQL transactions can be conducted.
Maybe I can get around that by using one MySQL connection for the
unbuffered query, and another separate MySQL connection for the
incidental SQL queries that I need to perform as I process each
record from the large dataset.
Jim
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 16:21, Jim Long wrote: > > I will try experimenting with Daniel's idea of unbuffered > queries, but my understanding is that while an unbuffered result > resource is in use, no other SQL transactions can be conducted. > Maybe I can get around that by using one MySQL connection for the > unbuffered query, and another separate MySQL connection for the > incidental SQL queries that I need to perform as I process each > record from the large dataset. Just for the sake of a kick-start, you could throw together a simple class and then call that quite easily. -- Network Infrastructure Manager http://www.php.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 3:29 PM, Daniel Brown wrote: > On Fri, Oct 28, 2011 at 13:25, Jim Long wrote: >> >> Eric: >> >> Thanks for your reply. >> >> "process row here" is a comment. It doesn't do anything. The >> script, exactly as shown, runs out of memory, exactly as shown. > > My response presumes that you're planning on placing something > into this comment area, in which the memory will only further > increase. If *presumed* should be replaced by *ASSumed* in this case, > skip mysql_unbuffered_query() and go straight for mysql_num_rows(). > Do not pass GO. Do not collect $200. > > -- > > Network Infrastructure Manager > http://www.php.net/ > I was glad to learn what comments were. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 03:24:37PM -0400, Jim Giner wrote: > If all you want to do is count the records, why are you not letting sql do > it for you instead of doing the while loop? That's all that script is > doing, if that is the exact code you ran. Hi, Jim. Thank you for replying. One of the key concepts of troubleshooting is that when you encounter a problem, you try to state the problem with as simple a test case as possible, so that testing will not be complicated by extraneous variables or code that may or may not have any impact on the problem. I don't want to just count the records, I want to get some work done for a client. That work involves processing every record in the result set. "Counting" is just a simple process to conduct for the purpose of debugging this loop algorithm. The problem is that this algorithm fails to process all the records in the result set. I appreciate any insights you have as to why that is happening. Jim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 13:25, Jim Long wrote: > > Eric: > > Thanks for your reply. > > "process row here" is a comment. It doesn't do anything. The > script, exactly as shown, runs out of memory, exactly as shown. My response presumes that you're planning on placing something into this comment area, in which the memory will only further increase. If *presumed* should be replaced by *ASSumed* in this case, skip mysql_unbuffered_query() and go straight for mysql_num_rows(). Do not pass GO. Do not collect $200. -- Network Infrastructure Manager http://www.php.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 14:05, Jim Long wrote: > > 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 Each row increases memory allocation by 440 bytes. 274,700 rows would equal 120,868,000 bytes of MySQL data in the buffer. Subtracting the 240 bytes from the 440 anticipated in the 274,700th row, that means the memory footprint of your script in execution (including PHP and its extensions, Apache, the MySQL connection, et cetera) is utilizing 13,349,928 (roughly 13.4MB), which is pretty average, depending on the configuration and bloat of the builds you're using. Ways around this: increase memory allocation and risk bogging-down your machine, or use mysql_unbuffered_query(). -- Network Infrastructure Manager http://www.php.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Why does this script run out of memory?
If all you want to do is count the records, why are you not letting sql do it for you instead of doing the while loop? That's all that script is doing, if that is the exact code you ran. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 01:32:32PM -0400, James wrote:
> >
> >On Fri, Oct 28, 2011 at 12:38 PM, Jim Long 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
> >>
> >> >>
> >> $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:
274695134203084
274696134203524
274697134203964
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:
274695134202232
274696134202672
274697134203112
274698134203552
274699134203992
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 | | | |
| 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
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
> >To: "Jim Long"
> >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 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
> >>
> >> >>
> >> $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
Re: Re: [PHP] Why does this script run out of memory?
> Original Message
>From: Eric Butera
>To: "Jim Long"
>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 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
>>
>> >
>> $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.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 01:21:36PM -0400, Eric Butera wrote:
> On Fri, Oct 28, 2011 at 12:38 PM, Jim Long 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
> >
> > >
> > $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.
Eric:
Thanks for your reply.
"process row here" is a comment. It doesn't do anything. The
script, exactly as shown, runs out of memory, exactly as shown.
Jim
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Why does this script run out of memory?
On Fri, Oct 28, 2011 at 12:38 PM, Jim Long 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
>
>
> $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
[PHP] Why does this script run out of memory?
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 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
