MySQL Performance Issues
Posted: Tue Oct 11, 2005 6:23 pm
Hey,
We are working on a massive application (there will be millions of rows throughout a few dozen tables) using a PHP/MySQL back-end. The issue we are having is that there are times when the program automatically will use a loop to insert say 50,000 rows to the database. The loop includes a number of database queries, and then it finally inserts into the DB. At first, the rows are inserted very quickly, but it begins to slow down, and eventually only a handful of rows are inserted each minute and then the script times out. Our server's specs aren't very impressive (2.0 GHz Celeron with 512 MB DDR) and we can't afford to upgrade it for now. Any suggestions on what may be causing the problem or what we could do to optimize performance? We realize that the specs of the server are definitely impacting the situation, but it's all we can do for now. Any help would be greatly appreciated.
Here is a sample of the code
We are working on a massive application (there will be millions of rows throughout a few dozen tables) using a PHP/MySQL back-end. The issue we are having is that there are times when the program automatically will use a loop to insert say 50,000 rows to the database. The loop includes a number of database queries, and then it finally inserts into the DB. At first, the rows are inserted very quickly, but it begins to slow down, and eventually only a handful of rows are inserted each minute and then the script times out. Our server's specs aren't very impressive (2.0 GHz Celeron with 512 MB DDR) and we can't afford to upgrade it for now. Any suggestions on what may be causing the problem or what we could do to optimize performance? We realize that the specs of the server are definitely impacting the situation, but it's all we can do for now. Any help would be greatly appreciated.
Here is a sample of the code
Code: Select all
while( $objRS = mysql_fetch_array($connect) )
{
if($_GET[id] == "personal")
{
$subscrID = $objRS[ID];
}
else
{
$subscrID = $objRS[SubscriberID];
}
$subscribers = mysql_query("SELECT ID,EmailAddress FROM SUBSCRIBERS WHERE ID =" . $subscrID);
$subscribers = mysql_fetch_array($subscribers);
// If the user does not have an email address add them to a queue for later processing
if( $subscribers[EmailAddress] == "N/A" )
{
if($strLetterQueue == "")
{
$strLetterQueue = $subscribers[ID];
$bEmail = false;
}
else
{
$strLetterQueue = $strLetterQueue . ",$subscribers[ID]";
$bEmail = false;
}
}
if( $bEmail == true )
{
if( $strArea == "New Customer Follow Ups" )
{
$subscrID = $objRS[ID];
}
else
{
$subscrID = $objRS[SubscriberID];
}
// Check to see what info to pull from the SUBSCRIBERS table
// if( the querystring isn//t for a personal thank you then
if( $_GET[id] != "personal" )
{
$subscribers = mysql_query("SELECT ID FROM SUBSCRIBERS WHERE ID =" . $subscrID);
$subscribers = mysql_fetch_array($subscribers);
}
elseif( $_GET[id] == "personal" )
{
$subscribers = mysql_query("SELECT ID FROM SUBSCRIBERS WHERE ClientName ='" . $_SESSION['clientname'] . "' AND DateEntered <= '" . $str3Date . "' AND FirstContact != 'yes'");
$subscribers = mysql_fetch_array($subscribers);
}
$message = $subscrID;
$key = "secret";
$eID = @mcrypt_encrypt(MCRYPT_CAST_256, $key, $message, MCRYPT_MODE_OFB);
//add opt-out to $strBody
$strBody .= "<p align=\"center\"><font size=\"1\" face=\"Arial, Helvetica\">We are in full compliance with the Can Spam Act.<br>You have received this email because you have subscribed to our mailing list. If you wish to unsubscribe, please click <a href=\"http://www.ultimadesigns.com/CIRA/unsubscribe/unsubscribe.php?id=" . $eID . "\">here</a>.</font></p>";
$body = stripslashes($_POST['body']);
$body = str_replace("\n","<br>",$body);
$body = str_replace("'","''",$body);
$body = $body . "<br><br>" . $_POST[citation] . "<br><br>" . $_POST[signature];
//($duedate, $strList, $type, $subcode, $body, $subject, $group)
//THIS DOES AN INSERT INTO THE DB
EMAIL_SPOOLER($duedate, $subscrID, $strArea, "N/A", $body, "N/A", "N/A");
$bSpooled = true;
if( $strArea == "New Customer Follow Ups" )
{
$subscrID = $objRS[ID];
}
else
{
$subscrID = $objRS[SubscriberID];
}
if( $strColumn == "Status" )
{
mysql_query("UPDATE NOTICE SET " . $strColumn . "= 'Completed'" . " WHERE clientname ='" . $_SESSION['clientname'] . "' AND Action_Code ='" . $strActionCode . "' AND SubscriberID =" . $subscribers[ID]);
}
elseif( $strColumn == "FirstContact" )
{
mysql_query("UPDATE SUBSCRIBERS SET " . $strColumn . "= 'yes'" . " WHERE clientname ='" . $_SESSION['clientname'] . "' AND ID =" . $subscribers[ID]);
}
else
{
mysql_query("UPDATE PURCHASE SET " . $strColumn . "='yes' WHERE clientname ='" . $_SESSION['clientname'] . "' AND SubscriberID =" . $subscribers[ID]);
} //end if
} //$bEmail if
$bEmail = true;
$emailCount += 1;
// To avoid the page from timing out at the end of each iteration of the loop
// set the timeout counter to 0
set_time_limit(0);
}//loop