MySQL Performance Issues

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
fdesensi
Forum Newbie
Posts: 11
Joined: Tue Oct 11, 2005 6:17 pm
Location: Pittsburgh

MySQL Performance Issues

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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).
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

deltawing
Forum Commoner
Posts: 46
Joined: Tue Jun 14, 2005 2:55 pm

Post by deltawing »

ahar, I could use that. Anything to get things running that little bit more efficiently.
User avatar
fdesensi
Forum Newbie
Posts: 11
Joined: Tue Oct 11, 2005 6:17 pm
Location: Pittsburgh

Post 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...
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post 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.
User avatar
fdesensi
Forum Newbie
Posts: 11
Joined: Tue Oct 11, 2005 6:17 pm
Location: Pittsburgh

Post by fdesensi »

I will give that I try tonight after my classes, and will post the results.

Thanks
User avatar
fdesensi
Forum Newbie
Posts: 11
Joined: Tue Oct 11, 2005 6:17 pm
Location: Pittsburgh

Post 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
deltawing
Forum Commoner
Posts: 46
Joined: Tue Jun 14, 2005 2:55 pm

Post 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.
User avatar
fdesensi
Forum Newbie
Posts: 11
Joined: Tue Oct 11, 2005 6:17 pm
Location: Pittsburgh

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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!
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply