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.
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
$subscribers = mysql_query("SELECT ID,EmailAddress FROM SUBSCRIBERS WHERE ID =" . $subscrID);
$subscribers = mysql_query("SELECT ID FROM SUBSCRIBERS WHERE ID =" . $subscrID);
You might want to consider calling unset() on all your variables you create in the loop. If it's being looped through 50K times, that could use up a lot of memory (although I'm unsure how PHP re-uses memory space and how garbage collection would work).
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
It's hard to say without actually looking at your data etc... but judging by what your saying, it's possible you are running out of physical memory and moving onto swap files etc... so you end up reading/writing to swap files and your database is still trying to read/write it's files so you end up with an acceleration of the slow down process. But it's only a guess.
Awsome. So obviously that stuff really does help then. I'll be using it too methinks when I have to do multiple queries. It probably won't help too much with only a few queries, but it'll be good for expandability.
That code did help, however the biggest thing that I changed was the database structure....
I forgot to mention that haha. I indexed some of the fields that are the subject to many
queries, but rarely change, and this made a big difference in speed.
The mysql_free_result() did free memory that was being consumed. Otherwise the large
number of queries I was doing would have began to eat up all of the memory and
slow down the script execution.
Ya, I've noticed indexing usually dramatically speeds up queries as well. It affects inserts negatively, but selects perform way, way faster. Should have mentioned it earlier.
Congrats on a 6000% increase in speed!
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.