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

2011-10-28 Thread Eric Butera
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



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

2011-10-28 Thread Jim Long
On Fri, Oct 28, 2011 at 01:21:36PM -0400, Eric Butera wrote:
 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.

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: Re: [PHP] Why does this script run out of memory?

2011-10-28 Thread James
 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.




--
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?

2011-10-28 Thread Ashley Sheridan
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




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

2011-10-28 Thread Jim Long
On Fri, Oct 28, 2011 at 01:32:32PM -0400, James wrote:
 
 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 )) {
 
 
 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: [PHP] Why does this script run out of memory?

2011-10-28 Thread Jim Giner
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?

2011-10-28 Thread Daniel Brown
On Fri, Oct 28, 2011 at 14:05, Jim Long p...@umpquanet.com 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().

-- 
/Daniel P. Brown
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?

2011-10-28 Thread Daniel Brown
On Fri, Oct 28, 2011 at 13:25, Jim Long p...@umpquanet.com 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.

-- 
/Daniel P. Brown
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?

2011-10-28 Thread Jim Long
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?

2011-10-28 Thread Eric Butera
On Fri, Oct 28, 2011 at 3:29 PM, Daniel Brown danbr...@php.net wrote:
 On Fri, Oct 28, 2011 at 13:25, Jim Long p...@umpquanet.com 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.

 --
 /Daniel P. Brown
 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?

2011-10-28 Thread Daniel Brown
On Fri, Oct 28, 2011 at 16:21, Jim Long p...@umpquanet.com 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.

-- 
/Daniel P. Brown
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?

2011-10-28 Thread Jim Long
On Fri, Oct 28, 2011 at 03:42:48PM -0400, Eric Butera wrote:
 On Fri, Oct 28, 2011 at 3:29 PM, Daniel Brown danbr...@php.net wrote:
  On Fri, Oct 28, 2011 at 13:25, Jim Long p...@umpquanet.com 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.
 
  --
  /Daniel P. Brown
  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?

2011-10-28 Thread Tommy Pham
On Fri, Oct 28, 2011 at 9:38 AM, Jim Long p...@umpquanet.com 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?

2011-10-28 Thread Paul Halliday
On Fri, Oct 28, 2011 at 1: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


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



[PHP] mysql_fetch_array() vs mysql_fetch_assoc() WAS: Re: [PHP] Why does this script run out of memory?

2011-10-28 Thread Daniel Brown
On Fri, Oct 28, 2011 at 18:13, Paul Halliday paul.halli...@gmail.com 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().

-- 
/Daniel P. Brown
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?

2011-10-28 Thread Jim Long
On Fri, Oct 28, 2011 at 02:57:02PM -0700, Tommy Pham wrote:
 On Fri, Oct 28, 2011 at 9:38 AM, Jim Long p...@umpquanet.com 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] Re: mysql_fetch_array() vs mysql_fetch_assoc() WAS: Re: [PHP] Why does this script run out of memory?

2011-10-28 Thread Paul Halliday
On Fri, Oct 28, 2011 at 7:19 PM, Daniel Brown danbr...@php.net wrote:
 On Fri, Oct 28, 2011 at 18:13, Paul Halliday paul.halli...@gmail.com 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().

 --
 /Daniel P. Brown
 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] mysql_fetch_array() vs mysql_fetch_assoc() WAS: Re: [PHP] Why does this script run out of memory?

2011-10-28 Thread Jim Long
On Fri, Oct 28, 2011 at 06:19:56PM -0400, Daniel Brown wrote:
 On Fri, Oct 28, 2011 at 18:13, Paul Halliday paul.halli...@gmail.com 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



Re: [PHP] mysql_fetch_array() vs mysql_fetch_assoc() WAS: Re: [PHP] Why does this script run out of memory?

2011-10-28 Thread Daniel Brown
On Fri, Oct 28, 2011 at 18:48, Jim Long p...@umpquanet.com 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

-- 
/Daniel P. Brown
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?

2011-10-28 Thread shiplu
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 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 );

$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