Link to home
Create AccountLog in
Avatar of blink10
blink10

asked on

Linux box killing my script...

I trying to run a while loop to process my product's records.

However....I have about 8 million records and every time I try to run it, it just does nothing for 3 minutes and then just says killed.

Why does this while loop kill it? Below I have the code will is causing the problems....ideas on how to modify it? (when i limit it to 10 it runs ok)

I have a feeling it a memory issue, by why cant it process this number of records?
//LINKSHARE - ALL 

$resultp = mysql_query("SELECT * FROM LinkshareProducts WHERE P_Id<>''");

while($row = mysql_fetch_array($resultp)) {

$UPC=$row['UPC'];

//special scripting to get isbn number


$tt=$row['ClassID'];
if($tt==10){
$isbn=$row['Attribute4'];
$isbnLENGTN=strlen($isbn); // should be 10 or 13 long
}


$Name=$row['ProductName'];
$Name = mysql_real_escape_string($Name);

$mid=$row['AdvertiserID'];
$pn = mysql_real_escape_string($mid);
$resultp1 = mysql_query("SELECT * FROM Stores WHERE MID='$pn'");
$row1 = mysql_fetch_array($resultp1);

$source=$row1['P_Id'];

if($source==""){
$source=$pn;
}

$upcLENGTH=strlen($UPC); // should be 12 long


if($UPC!=""&&$UPC!="0"&&$UPC!="NONE"&&$UPC!="none"&&$upcLENGTH=="12"){
$id="upc-".$UPC;
}
elseif($isbn!=""&&$isbn!="0"&&$isbn!="NONE"&&$isbn!="none"&&($isbnLENGTN=="10"||$isbnLENGTN=="13")){
$id="isbn-".$isbn;
}
else{
$id="ML-".$row1['P_Id']."-".$Name;
}
 
$Description=$row['DescriptionLong'];
if($Description==""){
$Description=$row['DescriptionShort'];
}

$Brand=$row['Brand'];
$Image=$row['ProductImageURL'];
$NewUsed='New';
$Instock=$row['Availability'];

$a1=$row['Attribute1'];
$a2=$row['Attribute2'];
$a3=$row['Attribute3'];
$a4=$row['Attribute4'];
$a5=$row['Attribute5'];
$a6=$row['Attribute6'];
$a7=$row['Attribute7'];	
$a8=$row['Attribute8'];
$a9=$row['Attribute9'];
$a10=$row['ClassID'];	
$t1=$row['PrimaryCategory'];
$t2=$row['SecondaryCategory'];

$Description = mysql_real_escape_string($Description);
$a1 = mysql_real_escape_string($a1);
$a2 = mysql_real_escape_string($a2);
$a3 = mysql_real_escape_string($a3);
$a4 = mysql_real_escape_string($a4);
$a5 = mysql_real_escape_string($a5);
$a6 = mysql_real_escape_string($a6);
$a7 = mysql_real_escape_string($a7);

$InStock = mysql_real_escape_string($InStock);


$t1 = mysql_real_escape_string($t1);
$t2 = mysql_real_escape_string($t2);


$network="LS";
if ($Instock!="no"){
$Description = mysql_real_escape_string($Description);
$Brand = mysql_real_escape_string($Brand);
$Image = mysql_real_escape_string($Image);
$NewUsed = mysql_real_escape_string($NewUsed);
$manid = mysql_real_escape_string($manid);


mysql_query("INSERT INTO products (id, name, description, brand, image, NewUsed, tier1, tier2, source, a1, a2, a3, a4, a5, a6, a7, network) VALUES ('$id', '$Name', '$Description', '$Brand', '$Image', '$NewUsed', '$t1', '$t2', '$source', '$a1', '$a2', '$a3', '$a4', '$a5', '$a6', '$a7', '$network')")or die(mysql_error());
}
}

Open in new window

Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

There are always memory and time limits.  If your rows have only 100 characters, that's 800MegaBytes of data.

Why don't you break it up into smaller batches like 100,000 rows?  Add an 'ORDER BY' clause and keep track of the last value in each batch and use that to start the next batch.
Avatar of blink10
blink10

ASKER

can u show me how i would break it up, i am not getting exactly what you are saying
The first is similar to what you've done.  The rest of the time, you take the last P_Id from the previous run and get 100000 items that are greater than it.  This does assume that P_Id is unique so that the ORDER BY sort works properly.  Note that sorting an alphanumeric column automatically puts '' at the beginning.
// first time
$resultp = mysql_query("SELECT * FROM LinkshareProducts WHERE P_Id<>'' ORDER BY P_Id LIMIT 100000");
// the rest of the times
$resultp = mysql_query("SELECT * FROM LinkshareProducts WHERE P_Id > 'thelastP_Id' ORDER BY P_Id LIMIT 100000");

Open in new window

Avatar of blink10

ASKER

dumb question but how do i get the last pid and make the rest of the times while loop to keep running as results are available?
ASKER CERTIFIED SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
CHeck that table 'stores' has an index defined on column 'MID'. If not then add one and you will see a vast improvement in performance, but DaveBaldwin's comment about repeatedly re-running SELECTs is still relevant even if there is an index problem.