Best Architectures for Separating PHP/MySQL Reads/Writes

[webbysanchez]

Hello - I'm looking for advice on the best practice for separating our MySQL read/writes. We're creating a 2nd MySQL server as the SLAVE for read queries and will keep our existing database as our MASTER for all writes. I've read a little bit about the MySQL-Proxy project but it doesn't look "production-ready". As we add more customers are reads/writes will also go up so I'm looking for the best way to balance the read loads across multiple SLAVEs as we add more SLAVEs. Ideally, it would be great to automate read/write separation and read loads but I realize this is not a perfect world. In summary, I have three specific questions:

 

1) What is the best practice for separating MySQL reads/writes in php? Is there a way to automate this?

2) How can the read load be balanced between multiple SLAVEs? Is there a way to automate this?

3) Are there a set of programs/libraries/tools that are commonly used as a best practice?

 

Background if needed:

We have a single Amazon EC2 large instance that stores our master MySQL database. Today, *all* reads/writes go through the master only. The bulk of our writes come from tracking our customers web pages. Every website visitor to each of our customers web pages send a write (keep-alive) every 5 seconds while on a page. The bulk of our reads come from a single page and our .NET desktop client which reads the DB to tell when one of our customers website visitors is online, what page they are on, etc. Reads occur every 5 seconds for every client (browser or desktop software).

 

My gut tells me we should send all writes to the master DB and start with one slave DB as the replication server. Then, programmatically, I could send clients # 1-20 (or whatever) to slave server #1, clients #21 – 40 to slave server #2, etc. as we need to scale; however, I'm new at this and don't know the best practices.

[ignace]

1) What is the best practice for separating MySQL reads/writes in php? Is there a way to automate this?

 

Write to the master, read from the slave. Or what do you mean?

 

2) How can the read load be balanced between multiple SLAVEs? Is there a way to automate this?

 

By using a load-balancer (a proxy that redirects traffic between slaves).

 

3) Are there a set of programs/libraries/tools that are commonly used as a best practice?

 

http://dev.mysql.com/downloads/mysql-proxy/

http://www.howtoforge.com/loadbalanced_mysql_cluster_debian

[webbysanchez]

Thanks for the reply. I realize the best practice is to send reads to the slave and writes to the master; however, I'm looking for the best way to do this *at the application level* (we write in php). Are there common libraries to separate reads/writes in our application? Should we do this manually by using simple connection groups (http://stackoverflow.com/questions/3633692/codeigniter-configure-different-ip-for-read-and-write-mysql-data) or should we use a more automated way which prevents us from changing our code? I don't know the best practice and/or set of tools/code/layers to use at the app-level and looking for help in this area.

 

Note, I did see MySQL-proxy as you pointed out in your reply post but that's in alpha???

 

 

[ignace]

Since you are already using a framework, I don't see what the problem is? Separating reads/writes should be easy but should not be done at the application level but at your model layer.

[webbysanchez]

Hello, I'm not clear on what you mean by "model layer". Let me ask more targeted questions to clarify:

 

1) Are you saying most applications which use MySQL replication (writing to the master and reading from the slave) use MySQL-proxy and do NOT change any application code?

2) Are you suggesting we do NOT create different connection groups as suggested by the link in my previous post?

3) If everyone uses MySQL-proxy (answer to #1 is yes), are there any issues with MySQL-proxy in a production environment since it's in the alpha stage?

 

Many thanks.

[thehippy]

If you take up the replicated MySQL environment you propose you will have some modification to do.  The Model Layer is part of your application and will have to be modified to utilize multiple database connections.  To scale it out you would have to load balance (like with ldirectord/heartbeat) a write and read pool, which would be transparent to the application apart from still needing read and write connections in your application.  Connection groups, or having your application select which pool member to use is problematic as its unaware of the load that node is under and if your application needs to scale more there may be multiple PHP/web/application servers that are selecting from the pool. 

 

If your application write heavy your setup probably wont scale very well because of data stagnation and high resource usage due to write replication.  A MySQL cluster (NDBCLUSTER) would be the preferable setup.  Be wary of info you read on MySQL cluster there is lots of misinformation and old tutorials with out of date comments around, the cluster has evolved quite a bit since the 5.x series.  One of the good things about a cluster is that its a transparent change to your application as load balancing is done transparently via DNS to the SQL nodes.

 

Note: Application level is not in an application, its the application as a whole. cat, ssh, mysql, bind all are applications, interacting with them is said to be happening at the application level.

[ignace]

1) Are you saying most applications which use MySQL replication (writing to the master and reading from the slave) use MySQL-proxy and do NOT change any application code?

 

No. I just gave it as an example. I also gave you http://www.howtoforge.com/loadbalanced_mysql_cluster_debian. These are some examples I found using Google, I have no personal experience with a cluster setup, although we use clustering extensively where I work, and IMO it's probably currently too expensive than what you currently actually need.

 

One of our applications has the same setup, it writes to the master, and reads from a slave (in our setup both masters and slaves are behind different load-balancers I think). Reading/writing is handled in our models, in CI it would look something like this:

 

class Customers extends Model {
    public function addCustomer(array $details) {
        $db = $this->load->database('mightymaster', true, true);
        $db->insert($this->tableName, $details);
    }
    
    public function getCustomer($id) {
        return $this->db->get($this->tableName, array('id' => $id));
    }
}

 

By default $this->db would point to the read load-balancer.

 

2) Are you suggesting we do NOT create different connection groups as suggested by the link in my previous post?

 

No.

 

3) If everyone uses MySQL-proxy (answer to #1 is yes), are there any issues with MySQL-proxy in a production environment since it's in the alpha stage?

 

I don't know, we probably use NDBCLUSTER or something like that.

[webbysanchez]

ignace - Thanks for the explanation. I saw that CodeIgniter (CI) had some nice features there. Unfortunately, I'm 4 years into creating our application and we didn't start off with a framework. I didn't know any better to be honest. So, none of that logic (database abstraction layer, etc.) is there. What do you suggest in this case? Just manually changing which db we connect to on the pages that do our read-intensive queries (and no writes of course)?

 

thehippy - I thought about clusters but it could be too expensive (cost, management) for us as we're a startup. Our app is not overly read-intensive. It probably an even split between reads/writes. Our writes are very short statements with the majority of them being UPDATEs (keep alives). We're a website tracking/analytics solution and need to track online status of our customer's website visitors.

[ignace]

So what is it that do you do then now? Just a plain mysql_connect()? If that's the case make sure you have 2 connections: one to the read database, and one to the write database.

 

$rdb = mysql_connect('sql-read.domain.top', 'reader', 'password');
$wdb = mysql_connect('sql-write.domain.top', 'writer', 'password');

Then look up all UPDATE/INSERT/DELETE statements you have in your code and change them to:

 

$res = mysql_query('UPDATE ..', $wdb);

All reads would look like:

 

$res = mysql_query('SELECT ..', $rdb);

Preferable you should only open a connection to the write database when you actually need it.

[webbysanchez]

Here's what we do today. Note, I am not sure if this is write/wrong or if there's a better way to do it. Also, we've got at least a thousand different queries across our application. It would be quite an undertaking to change each query individually. In theory, I guess we could just use a different connect file when we need to open access to the read database. But, I'm not sure if this is a "no-no". Perhaps you can educate me.

 

Finally, each script (page) may include other files that require db connection, but we don't have to include the mysql_connect file in the included files/script, since we open the database at the root of the parent script (page).

 

Note, I removed any credential secrets from the script below.

 

<?php
require_once ("mysql_connect.php"); // connect to the db
?>
// some code here along with SQL queries...like:
$queryCheckEmail = "SELECT userID, firstName, lastName, userName, email FROM users WHERE resetPasswordToken = '$token'";
$resultCheckEmail = @mysql_query ( $queryCheckEmail ); // run the query
// etc...
<?php
mysql_close ();
?>

 

Then, our mysql_connect.php file has the following contents:

<?php
global $fileID;
$fileID = 0;
global $isDebug;
$isDebug = false;
function setIsDebug($value) {
global $isDebug;
$isDebug = $value;
}
function isDebug() {
global $isDebug;
return $isDebug;
}

function mysql_query_with_print($query) {
echo "
    <br />$query<br />;<br />
    ";
$result = @mysql_query ( $query );
$err = mysql_error ();
if (! empty ( $err ))
	echo $err;
return $result;
}

function mysql_query_forDebug($query) {
if (isDebug ()) {
	global $fileID;
	$fileID ++;
	//echo "<br><br>Query $fileID:<br>$query";
	if (! (is_dir ( 'MainLeadsPageQueries_last' ))) {
		mkdir ( "MainLeadsPageQueries_last", 0777 );
	}
	$fh = fopen ( $myFile, 'a' );
	//echo "<br  />$query;<br />";
	$myFile = "MainLeadsPageQueries_last/Query_" . $fileID . ".txt";
	$fh = fopen ( $myFile, 'w' );
	fwrite ( $fh, "\n\n" . $query . "\n\n" );
	fclose ( $fh );
}
//echo "<br />$query";
return @mysql_query ( $query );
}
require_once ('Util/security.inc');
#Script 1.0 - Connection to MySQL Database
#Author - Ryan
#Date - 02.15.04
// This file contains the database access information.  This file also establishes a connection
// to MySQL and selects the database.
// require("config.inc");
// Set the database access information as constants
define ( "DB_USER", "blah" );
define ( "DB_PASSWORD", "xyz" );


define ( "DB_HOST", "555.12.12.12" );
define ( "DB_NAME", "blah" );

leadliaison_database_connect::connect ();
/*
// Make the connection and then select the database
// @ symbol surpresses the error generated.  Use of "mysql_error()" to print out the error msg.
if ($dbc = @mysql_connect ( DB_HOST, DB_USER, DB_PASSWORD, false, 65536 )) { //make the connection
if (! mysql_select_db ( DB_NAME )) { //if it can't select the db
	//handle error
	//my_error_handler (mysql_errno(),'Could not select the database: ' . mysql_error());
	//print msg to user and kill script
	echo '<p><font class="rederrormsg">The site is currently experiencing technical difficulties.  We apologize for any inconvenience.</font></p>';
	exit ();
} // end of mysql_select_db IF statement


} else { // If it couldn't connect to MySQL.
// print a msg to user and kill script
//my_error_handler (mysql_errno(),'Could not connect to the database: ' . mysql_error());
//print msg to user and kill script
echo '<p><font class="rederrormsg">The site is currently experiencing technical difficulties.  We apologize for any inconvenience.</font></p>';
exit ();

} //end of $dbc IF statement
*/

// function for escaping and trimming form data
function escape_data($data) {
global $dbc;
if (ini_get ( 'magic_quotes_gpc' )) {
	$data = stripslashes ( $data );
}
return mysql_real_escape_string ( trim ( $data ), $dbc );
} // end of escape_data() function.
//This method is at security.inc file and it validate all the inputs coming from the client side [Get, Post, Request and Cokkie]


session_start ();
if (isset ( $_SESSION ['COOKIES_TO_SESSION'] ) && is_array ( $_SESSION ['COOKIES_TO_SESSION'] ) && ! empty ( $_SESSION ['COOKIES_TO_SESSION'] )) {
foreach ( $_SESSION ['COOKIES_TO_SESSION'] as $cookie_key => $cookie_value ) {
	$_COOKIE [$cookie_key] = $cookie_value;
}
}

if (empty ( $ignoreValidateAllInputs )) {
if (empty ( $byPassInputValidation )) {
	ValidateAllInputs ();
} else {
	ValidateAll_COOKIES ();
}
}
$ignoreValidateAllInputs = true;

if (empty ( $byPassSession )) {
if (strstr ( $_SERVER ['REQUEST_URI'], '/Chat/' ) || strstr ( $_SERVER ['REQUEST_URI'], '/admin/' ))
	$byPassSession = true;
}
if (empty ( $byPassSession )) {
$seesionStartedHere = false;
if (! isset ( $_SESSION )) {
	$seesionStartedHere = true;
	session_start ();
}

/*
 * Validate CSRF
 */
if (empty ( $byPassActivity )) {
	$csrf_protect = new CsrfProtect ( );
	$csrf_protect->enable ();
}

if (! isset ( $redirectIfNotValid ))
	$redirectIfNotValid = true;

$session_ended = checkSecureSession ( $redirectIfNotValid );
if ($seesionStartedHere) {
	session_write_close ();
}
if ((empty ( $byPassActivity )) && (! empty ( $_COOKIE ["userID_ck"] ))) {
	$tUnixDate = time (); // grab current unix timestamp from server
	$sGMTMySqlDateTime = gmdate ( "Y-m-d H:i:s", $tUnixDate ); // current date time from current Unix TimeStamp
	$query_updateActivity = "UPDATE users SET webapp_lastactive='" . $sGMTMySqlDateTime . "' WHERE userID='" . $_COOKIE ["userID_ck"] . "' AND webapp_online='1' AND webapp_onlineuser_token = '" . $_SESSION ['securitysession_webapp_onlineuser_token'] . "';";
	@mysql_query ( $query_updateActivity );
}
}

function getDateTimeForQuery($tzOffset, $date, $dateTo = false) {
if (! $dateTo)
	return date ( "Y-m-d H:i:s", strtotime ( - 1 * $tzOffset . ' HOUR', strtotime ( $date ) ) );
else
	return date ( "Y-m-d H:i:s", strtotime ( - 1 * $tzOffset . ' HOUR', strtotime ( $date . ' 23:59:59' ) ) );
}

class leadliaison_database_connect {
public static function connect() {
	global $dbc;
	if ($dbc = @mysql_connect ( DB_HOST, DB_USER, DB_PASSWORD, false, 65536 )) {
		if (! mysql_select_db ( DB_NAME )) {
			echo '<p><font class="rederrormsg">The site is currently experiencing technical difficulties.  We apologize for any inconvenience.</font></p>';
			exit ();
		}
	} else {
		echo '<p><font class="rederrormsg">The site is currently experiencing technical difficulties.  We apologize for any inconvenience.</font></p>';
		exit ();

	}
}
}
class database_transaction {
public static function start() {
	mysql_query ( "SET AUTOCOMMIT=0" );
	mysql_query ( "START TRANSACTION" );
}
public static function commit() {
	mysql_query ( "COMMIT" );
}
public static function fail() {
	mysql_query ( "ROLLBACK" );
}
}

class connector_database_connect {
public static $is_connected = false;
public static $is_failed_to_connect = false;

public static $database_host = '555.12.12.12';
public static $database_name = 'something';
public static $username = 'something';
public static $password = 'blah';
/*
public static $database_host = 'something';
public static $database_name = 'blah';
public static $username = 'blah';
public static $password = 'blah';
*/
public static function connect() {
	global $dbc_connector;
	if ($dbc_connector = @mysql_connect ( connector_database_connect::$database_host, connector_database_connect::$username, connector_database_connect::$password, ! connector_database_connect::$is_connected, 65536 )) {
		if (mysql_select_db ( connector_database_connect::$database_name, $dbc_connector )) {
			connector_database_connect::$is_connected = true;
			connector_database_connect::$is_failed_to_connect = false;
			return true;
		} else {
			connector_database_connect::$is_connected = false;
			connector_database_connect::$is_failed_to_connect = true;
			return false;
		}
	} else {
		connector_database_connect::$is_connected = false;
		connector_database_connect::$is_failed_to_connect = true;
		return false;
	}
}

public static function mysql_query($query) {
	global $dbc_connector;
	if (empty ( connector_database_connect::$is_connected ))
		connector_database_connect::connect ();
	$result = @mysql_query ( $query, $dbc_connector );
	leadliaison_database_connect::connect ();
	return $result;
}

public static function close_database_connector() {
	global $dbc_connector;
	mysql_close ( $dbc_connector );
	connector_database_connect::$is_connected = false;
	connector_database_connect::$is_failed_to_connect = false;
}
}
/*
* This is how to use the queries:
* 1- Normal mysql_query for LL database.
* 2- connector_database_connect::mysql_query for Connector database.
*/
/* 
$result1 = mysql_query ( "select * from customers where customerID = '$customerID'" );
while ( $row = mysql_fetch_array ( $result1 ) ) {
_d ( $row );
}

$result2 = connector_database_connect::mysql_query ( "SELECT * FROM email_jobs where jobid = 4" );
while ( $row = mysql_fetch_array ( $result2 ) ) {
_d ( $row );
}

$result3 = mysql_query ( "select * from users where userID = '$userID'" );
while ( $row = mysql_fetch_array ( $result3 ) ) {
_d ( $row );
}

$result4 = connector_database_connect::mysql_query ( "SELECT * FROM email_jobs where jobid = 5" );
while ( $row = mysql_fetch_array ( $result4 ) ) {
_d ( $row );
}
*/

?>

 

 

[webbysanchez]

Hello ignace - Do you have any thoughts/comments on my last post? Wondering if we should change our approach in preparation for read/write splitting.

[ignace]

Sorry too much code to weed through + I'm not going to spend any time optimizing this as you may disregard it afterwards anyway.