Page 1 of 1

MySQL Performance Issues

Posted: Tue Oct 11, 2005 6:23 pm
by fdesensi
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

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

Posted: Wed Oct 12, 2005 10:52 am
by pickle
Well, you're doing both of these queries when you could probably get away with one:

Code: Select all

$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).

Posted: Wed Oct 12, 2005 11:10 am
by redmonkey

Posted: Wed Oct 12, 2005 12:01 pm
by deltawing
ahar, I could use that. Anything to get things running that little bit more efficiently.

Posted: Wed Oct 12, 2005 1:16 pm
by fdesensi
redmonkey,

Does that mean that every time I execute the loop another query
is stored in memory until the program stops execution?

And its that build up that slows down the system?

Further we have noticed that, with more and more customers in the
db, even if they are not selected we get even more performance
issues...

Posted: Wed Oct 12, 2005 1:30 pm
by redmonkey
That's about the size of it yes.

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.

Posted: Wed Oct 12, 2005 1:33 pm
by fdesensi
I will give that I try tonight after my classes, and will post the results.

Thanks

Posted: Wed Oct 12, 2005 10:37 pm
by fdesensi
I combined pickles comments with redmonkeys and went from process 10 items a second
to processing 600 a second!

Here is the new 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 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];
		}

		$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)
		EMAIL_SPOOLER($duedate, $subscrID, $strArea, "N/A", $body, "N/A", "N/A");
		
		$bSpooled = true;
		
	
		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);
	
	// Free MySQL result memory
	mysql_free_result();

}//loop

Posted: Thu Oct 13, 2005 4:09 am
by deltawing
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.

Posted: Thu Oct 13, 2005 10:07 am
by fdesensi
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.

Posted: Thu Oct 13, 2005 10:13 am
by pickle
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!