looping eating 100 per cpu

[kaptain kernel]

i've got a while loop thats iterating through a text file and pumping the
contents into a database. the file is quite large (over 150mb).

the looping causes my CPU load to race up to 100 per cent. Even if i remove
the mysql insert query and just loop through the file , it still hits 100
per cent CPU. This has the knock on effect of slowing my script down so
that mysql inserts are occuring every 1/2 second or so.

here's my script:

$fd=fopen("bigtextfile.txt","r");

while (!feof($fd) )
{
$line=fgets($fd,4096);
$linecontents=explode(":",$line);
mysql_query("INSERT INTO MyDatabase VALUES
'','$linecontents[0]','$linecontents[1]'");
}

[Richard Grove]

"kaptain kernel" <no****@nospam.gov> wrote in message
news:3f***********************@news.easynet.co.uk. ..

i've got a while loop thats iterating through a text file and pumping the
contents into a database. the file is quite large (over 150mb).

the looping causes my CPU load to race up to 100 per cent. Even if i remove the mysql insert query and just loop through the file , it still hits 100
per cent CPU. This has the knock on effect of slowing my script down so
that mysql inserts are occuring every 1/2 second or so.

here's my script:

$fd=fopen("bigtextfile.txt","r");

while (!feof($fd) )
{
$line=fgets($fd,4096);
$linecontents=explode(":",$line);
mysql_query("INSERT INTO MyDatabase VALUES
'','$linecontents[0]','$linecontents[1]'");
}

Thats because you are asking it to do alot.

Regards
Richard Grove

http://shopbuilder.org - ecommerce systems
Become a Shop Builder re-seller:
http://www.affiliatewindow.com/affil...ls.php?mid=611
http://www.affiliatewindow.com/a.pl?590

[André Næss]

kaptain kernel:

i've got a while loop thats iterating through a text file and pumping the
contents into a database. the file is quite large (over 150mb).

the looping causes my CPU load to race up to 100 per cent. Even if i
remove the mysql insert query and just loop through the file , it still
hits 100 per cent CPU. This has the knock on effect of slowing my script
down so that mysql inserts are occuring every 1/2 second or so.

here's my script:

$fd=fopen("bigtextfile.txt","r");

while (!feof($fd) )
{
$line=fgets($fd,4096);
$linecontents=explode(":",$line);
mysql_query("INSERT INTO MyDatabase VALUES
'','$linecontents[0]','$linecontents[1]'");
}

That's very strange, 1/2 second between each insert is crazy. Is it possible
that you're running out of memory? (It shouldn't, as you only read 4KB on
each iteration). The 100% CPU usage isn't surprising though.

What if you remove the query and don't do anything with the data? What if
you write a perl or python script which does the same (again, without the
query)?

André Næss

[Shawn Wilson]

kaptain kernel wrote:

i've got a while loop thats iterating through a text file and pumping the
contents into a database. the file is quite large (over 150mb).

the looping causes my CPU load to race up to 100 per cent. Even if i remove
the mysql insert query and just loop through the file , it still hits 100
per cent CPU. This has the knock on effect of slowing my script down so
that mysql inserts are occuring every 1/2 second or so.

here's my script:

$fd=fopen("bigtextfile.txt","r");

while (!feof($fd) )
{
$line=fgets($fd,4096);
$linecontents=explode(":",$line);
mysql_query("INSERT INTO MyDatabase VALUES
'','$linecontents[0]','$linecontents[1]'");
}

I wonder if it would be faster with fread()? If the average line is very small
it should drastically reduce the number of times the thing has to read from the
disk. This is UNTESTED code, so if you use it, please be careful. I don't read
from huge files often enough to be confident that it will work.

$line = "";

while ($line.=fread($fd,4096))
{
$insertlist = "";
$linecontents=explode("\n",$line);
$line = $linecontents[count[$linecontents]]; // remember beginning of last
(incomplete) data set to be used in next loop
unset($linecontents[count[$linecontents]]); // delete last (incomplete) data
set
foreach($linecontents as $val) {
$insertlist .= "('','$val[0]','$val[1]'),"; // build the data portion of the
mysql query statement
}

// insert up to 4K worth of data at once while removing trailing comma
mysql_query("INSERT INTO MyDatabase (col1, col2, col3) VALUES " .
rtrim($insertlist, ","));
}

Regards,
Shawn

--
Shawn Wilson
sh***@glassgiant.com
http://www.glassgiant.com

[Juha Suni]

kaptain kernel wrote:

i've got a while loop thats iterating through a text file and pumping
the contents into a database. the file is quite large (over 150mb).

the looping causes my CPU load to race up to 100 per cent. Even if i
remove the mysql insert query and just loop through the file , it
still hits 100 per cent CPU. This has the knock on effect of slowing
my script down so that mysql inserts are occuring every 1/2 second or
so.

here's my script:

$fd=fopen("bigtextfile.txt","r");

while (!feof($fd) )
{
$line=fgets($fd,4096);
$linecontents=explode(":",$line);
mysql_query("INSERT INTO MyDatabase VALUES
'','$linecontents[0]','$linecontents[1]'");
}

You should try using the built-in functions of PHP to simplify this,
could propably speed up the process quite a bit. (see
http://www.php.net/file). I am assuming that doing it your way means
reading the file _very_ many times, whereas with the example below the
whole file is read once, and then processed line by line from memory.

$bigfile = file("bigtextfile.txt");

foreach ($bigfile as $line_num => $line) {
$linecontents=explode(":",$line);
mysql_query("INSERT INTO MyDatabase VALUES
'','$linecontents[0]','$linecontents[1]'");
}

HTH

--
Suni

[André Næss]

Juha Suni:

kaptain kernel wrote:

i've got a while loop thats iterating through a text file and pumping
the contents into a database. the file is quite large (over 150mb).

the looping causes my CPU load to race up to 100 per cent. Even if i
remove the mysql insert query and just loop through the file , it
still hits 100 per cent CPU. This has the knock on effect of slowing
my script down so that mysql inserts are occuring every 1/2 second or
so.

here's my script:

$fd=fopen("bigtextfile.txt","r");

while (!feof($fd) )
{
$line=fgets($fd,4096);
$linecontents=explode(":",$line);
mysql_query("INSERT INTO MyDatabase VALUES
'','$linecontents[0]','$linecontents[1]'");
}

You should try using the built-in functions of PHP to simplify this,
could propably speed up the process quite a bit. (see
http://www.php.net/file). I am assuming that doing it your way means
reading the file _very_ many times, whereas with the example below the
whole file is read once, and then processed line by line from memory.

Ehm... that means reading a 150MB file into memory, even though you don't
need it there. Not a very good idea! His code only reads the file once, and
only 4KB a time, so there is very little wasted memory.

André Næss

[Shawn Wilson]

Juha Suni wrote:

kaptain kernel wrote:

i've got a while loop thats iterating through a text file and pumping
the contents into a database. the file is quite large (over 150mb).

the looping causes my CPU load to race up to 100 per cent. Even if i
remove the mysql insert query and just loop through the file , it
still hits 100 per cent CPU. This has the knock on effect of slowing
my script down so that mysql inserts are occuring every 1/2 second or
so.

here's my script:

$fd=fopen("bigtextfile.txt","r");

while (!feof($fd) )
{
$line=fgets($fd,4096);
$linecontents=explode(":",$line);
mysql_query("INSERT INTO MyDatabase VALUES
'','$linecontents[0]','$linecontents[1]'");
}

You should try using the built-in functions of PHP to simplify this,
could propably speed up the process quite a bit. (see
http://www.php.net/file). I am assuming that doing it your way means
reading the file _very_ many times, whereas with the example below the
whole file is read once, and then processed line by line from memory.

$bigfile = file("bigtextfile.txt");

foreach ($bigfile as $line_num => $line) {
$linecontents=explode(":",$line);
mysql_query("INSERT INTO MyDatabase VALUES
'','$linecontents[0]','$linecontents[1]'");
}

Excerpt from http://ca.php.net/manual/en/function.file.php:

//------------------------------------------
Note: Now that file() is binary safe it is 'much' slower than it used to be. If
you are planning to read large files it may be worth your while using fgets()
instead of file() For example:

$fd = fopen ("log_file.txt", "r");
while (!feof ($fd))
{
$buffer = fgets($fd, 4096);
$lines[] = $buffer;
}
fclose ($fd);

The resulting array is $lines.

I did a test on a 200,000 line file. It took seconds with fgets() compared to
minutes with file().
//------------------------------------------

My guess is, for a 150Mb file, file() would take forever, if it worked at all.

Shawn
--
Shawn Wilson
sh***@glassgiant.com
http://www.glassgiant.com

[Juha Suni]

> //------------------------------------------

Note: Now that file() is binary safe it is 'much' slower than it used
to be. If you are planning to read large files it may be worth your
while using fgets() instead of file() For example:

$fd = fopen ("log_file.txt", "r");
while (!feof ($fd))
{
$buffer = fgets($fd, 4096);
$lines[] = $buffer;
}
fclose ($fd);

The resulting array is $lines.

I did a test on a 200,000 line file. It took seconds with fgets()
compared to minutes with file().
//------------------------------------------

My guess is, for a 150Mb file, file() would take forever, if it
worked at all.

Shawn

Thank you for the info, I stand corrected.

I guess You really have no good options on this front. a 150 MB
text-file is a huge load of data, and looping through it just seems to
be too much for your system.

First I would consider the source of the text-file, where does it come
from? Is there no way the application creating the file could push the
data directly to mysql? I would also consider benchmarking with the
original data in different formats. How fast is the script freezing,
i.e. is it slow from the start or only after reading the first x lines?
Could it be faster to process 10 x 15Mb text-files than a large single
file?

Other than that, I cant really guess. I suppose a script with another
(compiled) language might be faster, so you could try C. A PHP-optimizer
could also fasten the process quite a bit.

If nothing else helps, a system hardware upgrade might be your best
option, considering the importance of the script, of course.

--
Suni

[Chung Leong]

Just out of curiosity I tried looping through a 150 meg text files with 100
byte records (i.e. ~1,500,000 lines) using fgets(). My computer cut through
it in less than 2 minutes.

My thinking is that the SQL statements generated by the script are malformed
most of the time. That would explain why it takes so long for an actual
insert to occur. If an insert occurs on every iteration through the loop,
the MySQL process would be using up most of the CPU time instead, with
Apache/PHP sitting there waiting for it to finish each query.

Uzytkownik "Shawn Wilson" <sh***@glassgiant.com> napisal w wiadomosci
news:3F***************@glassgiant.com...

kaptain kernel wrote:

i've got a while loop thats iterating through a text file and pumping the contents into a database. the file is quite large (over 150mb).

the looping causes my CPU load to race up to 100 per cent. Even if i remove the mysql insert query and just loop through the file , it still hits 100 per cent CPU. This has the knock on effect of slowing my script down so
that mysql inserts are occuring every 1/2 second or so.

here's my script:

$fd=fopen("bigtextfile.txt","r");

while (!feof($fd) )
{
$line=fgets($fd,4096);
$linecontents=explode(":",$line);
mysql_query("INSERT INTO MyDatabase VALUES
'','$linecontents[0]','$linecontents[1]'");
}
I wonder if it would be faster with fread()? If the average line is very

small it should drastically reduce the number of times the thing has to read from the disk. This is UNTESTED code, so if you use it, please be careful. I don't read from huge files often enough to be confident that it will work.

$line = "";

while ($line.=fread($fd,4096))
{
$insertlist = "";
$linecontents=explode("\n",$line);
$line = $linecontents[count[$linecontents]]; // remember beginning of last (incomplete) data set to be used in next loop
unset($linecontents[count[$linecontents]]); // delete last (incomplete) data set
foreach($linecontents as $val) {
$insertlist .= "('','$val[0]','$val[1]'),"; // build the data portion of the mysql query statement
}

// insert up to 4K worth of data at once while removing trailing comma
mysql_query("INSERT INTO MyDatabase (col1, col2, col3) VALUES " .
rtrim($insertlist, ","));
}

Regards,
Shawn

--
Shawn Wilson
sh***@glassgiant.com
http://www.glassgiant.com