My “research” on clustering and replication was just timely. I had to find a fix for an overloaded server. I could’ve just rewritten the code but I wanted to try something new first. I found out that a MySQL cluster needs to have at least three servers to get full redundancy. Replication was my only choice because I only have two servers for this application and the queries that are producing the most load are select queries.

MySQL replication works by having a master server where all the inserts, updates and deletes (basically any writing done) and one or more slave servers that polls the master server to replicate the database. You can only issue select queries to the slave server. You can also have multiple master servers but it won’t be covered here. You can follow this article to setup replication.

I’ll be using Round Robin to balance the load since I’ll be load balancing for a separate portion only where the same queries are used. This will equally split the load to each server (…almost). To do this in PHP, I wrote a very simple script that opens a socket. Once a host connects, it tells which database server to connect to and immediately terminates the connection.

#!/usr/bin/php -q
<?php
// Bind and listen
socket_bind($stream, "127.0.0.1",3307);
socket_listen($stream, 100);

// Define DB login credentials in an array
// host|user|passwd
$hosts = array(
0 => array('db1.dbservers.net','someuser','somepass'),
1 => array('db2.dbservers.net','anotheruser','anotherpass'),
/***** if you add another host (just follow the drift) *****/ 
// 2 => array('db3.dbservers.net','yetanotheruser','yetanotherpass') 
);

// Loop forever
while(true)
{
	// Accept anyone
	$client = socket_accept($stream);
	$key = key($hosts);
	$reply = implode($hosts[$key], "|");

	// Move internal pointer to next host
	if(next($hosts) === FALSE)
		reset($hosts);

	// Push response then kill the connection
	socket_write($client, $reply);
	socket_close($client);
}
?>

This script should be called from the command line and run like a daemon. Then we modify how we connect to the database. We connect to the “daemon” and catch the login information.

<?php
// Connect to load balancer daemon
$fp = fsockopen("localhost", 3307);

// Fallback and use some host in case of failure
if(!$fp)
{
	$host = "localhost";
	$user = "someuser";
	$pass = "somepasswd";
}
else
{
	// Get DB login information
	$packet = fgets($fp);
	$account_details = explode("|", $packet);
	$host = $account_details[0];
	$user = $account_details[1];
	$pass = $account_details[2];
}

// Connect
$link = mysql_connect($host,$user,$pass);
if(!$link)
{
	die("Fatal Error: Can't connnect to database\n");
}
mysql_select_db("somedb",$link);
?>

The code above can be improved further to check if a host is still up, give weights on the server depending on its hardware and other bells and whistles.

The beauty of this is you can safely change to another algorithm like Weighted Round Robin or Job Informed and all of the code that has to be changed is in the daemon. You may learn more on other algorithms from the paper by Dennis Haney and Klaus S. Madsen.

I’m looking into venturing to a Job Informed algorithm once the whole application uses load balancing. Queries will have weights then some form of load estimation can be achieved. Query analysis is also a possibility (based on subqueries, query type, constraints ,etc).

This entry was posted on Monday, June 18th, 2007 at 11.27 and is filed under Geek Cred. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.