Hi,
this is a little proof of concept i did to enable some rudimentary application-level read/write-splitting in yii.
I first started with MySQLProxy, but also the contained rw-splitting script has a load balancer which doesn’t ensure that read querys go against a slave.
What’s all this good for?
I had a pretty simple issue: because of a really poor bandwidth i needed one instance of my application to run on a local network while the other runs on an internet server. The master db is located on this internet server and replicated to a local network slave using mysqls master-slave-replication.
There is a lot of data that is read from db while updates and insert are not so weightily, so every select-statement (for list views) that goes against the master could cost up to 30 seconds - that’s bad.
First of all it isn’t good practise to modify core files - i know - but i didn’t find another way because this needs to be located at the root of yiis db layer.
Comment: all this was done using yii 1.0.
1. CDbConnection
I’ve derived a class called CDbConnectionRwMaster from CDbConnection to add two attributes to the master-db-connection. _enableRwSplitting to enable/disable splitting and _slaves which holds the list of slave-connections for this master-connection.
<?php
/**
* CDbConnectionRwMaster class file
* Master db connection.
*/
class CDbConnectionRwMaster extends CDbConnection
{
/**
* bool Flag to enable read-write-splitting for this connection. Defaults to FALSE.
*/
private $_enableRwSplitting = false;
/**
* Array Names of read-only slave connections for this master.
* Example: $slaves = array('db_slave1', 'db_slave2', 'anotherSlaveConnection');
*/
private $_slaves = array();
public function getEnableRwSplitting()
{
return $this->_enableRwSplitting;
}
public function setEnableRwSplitting($value)
{
$this->_enableRwSplitting = (bool)$value;
}
public function getSlaves()
{
return $this->_slaves;
}
public function setSlaves($value)
{
if(is_array($value))
$this->_slaves = $value;
else {
$this->_slaves = array();
$this->_slaves[] = $value;
}
}
}
?>
[b]
- CDbCommand[/b]
This is where we grub into yiis guts.
Here is a replacement of yiis private function queryInternal() located in CDbCommand.php:
private function queryInternal($method,$mode,$disableSplitting=false)
{
$lastConnection = $usedConnection = $this->getConnection();
$is_select = $hasBadPattern = false;
// $retry is just used to collect needed and evaluated statements while code is executed, otherwise
// the IF-statement to check if we should fallback to the master is so damn long.
$retry = true;
// The bad patterns are searched for inside the sql query because the db connection
// must not be changed to a slave if any of these sql commands is found
$badPatterns = array('SQL_CALC_FOUND_ROWS','FOUND_ROWS','LAST_INSERT_ID','ROW_COUNT');
// Application specific bad patterns, like non-splitable tables and so on
$customBadPatterns = array();
// Lets start when read-write-splitting is enabled local and global
if(($retry &= ($disableSplitting===false)) && ($retry &= ($usedConnection->enableRwSplitting===true)))
{
if($retry &= ($usedConnection->getCurrentTransaction() == null)) // We are not in a transaction
{
$string = strtoupper(ltrim($this->getText()));
if(strpos($string,'SELECT')===0 || strpos($string,'SHOW')===0) // Its a SELECT or SHOW query
{
$retry &= $is_select = true;
$searchPatterns = array_merge($badPatterns,$customBadPatterns); // Merge standard and custom patterns
// Search query string for bad words
foreach($searchPatterns as $pattern)
{
if(strpos($string,$badPattern)!==false) {
// A bad word was found inside the sql query
$hasBadPattern = true;
break;
}
}
if($hasBadPattern===false)
{
$maxSlaves = count($usedConnection->slaves); // Count available slave connections
if($maxSlaves >= 1) {
$slaveConnection = $usedConnection->slaves[rand(0,$maxSlaves-1)]; // Pick a slave - very poor till now
if(Yii::app()->{$slaveConnection}->active != true)
Yii::app()->{$slaveConnection}->active = true; // Activate connection if necessary
$usedConnection = Yii::app()->{$slaveConnection};
Yii::trace('RW-splitting: Switching connection to SLAVE: '.Yii::app()->{$slaveConnection}->connectionString,'system.db.CDbCommand');
}
}
else
Yii::trace('RW-splitting: Keeping connection to '.$usedConnection->connectionString.' because of pattern detected','system.db.CDbCommand');
}
}
else
Yii::trace('RW-splitting: Keeping connection to '.$usedConnection->connectionString.' because of TRANSACTION','system.db.CDbCommand');
}
else
Yii::trace('RW-splitting: Keeping connection - splitting is (temporarily) disabled for '.$usedConnection->connectionString,'system.db.CDbCommand');
$this->_connection = $usedConnection;
$params=$usedConnection->enableParamLogging && !empty($this->_params) ? '. Bind with parameter ' . implode(', ',$this->_params) : '';
Yii::trace('Querying SQL: '.$this->getText().'; ['.$params.$usedConnection->connectionString.']','system.db.CDbCommand');
try
{
if($usedConnection->enableProfiling)
Yii::beginProfile('system.db.CDbCommand.query('.$this->getText().') ['.$usedConnection->connectionString.']','system.db.CDbCommand.query');
if($this->_statement instanceof PDOStatement)
$this->_statement->execute();
else
$this->_statement=$usedConnection->getPdoInstance()->query($this->getText());
if($method==='')
{
$result=new CDbDataReader($this);
$n = $result->getColumnCount();
}
else
{
$result=$this->_statement->{$method}($mode);
$n = $this->_statement->rowCount();
$this->_statement->closeCursor();
}
/*
* A simple master-slave replication doesnt ensure that both are consistent.
* So after an INSERT maybe the slave doesnt yet have this record so we will take a look at the master-db
*/
//if($n<=0 && $is_select && !$disableSplitting && ($usedConnection != $lastConnection ) && $this->getConnection()->enableRwSplitting && ($this->getConnection()->getCurrentTransaction() == null))
// Shorter:
if( $n<=0 && $retry && ($usedConnection != $lastConnection ) )
{
Yii::trace('RW-splitting: Empty result - falling back to MASTER: '.$this->getConnection()->connectionString,'system.db.CDbCommand');
$this->_connection = $lastConnection;
// PDOStatement is bound to the connection so we need to rebuild everything,
// just changing $this->_connection wouldnt do the job
if($this->_statement instanceof PDOStatement)
{
$this->_statement = null;
foreach($this->_saveParams as $param)
$this->bindParam($params[0],$params[1],$params[2],$params[3]);
foreach($this->_saveValues as $value)
$this->bindValue($value[0],$value[1],$value[2]);
}
else
$this->_statement = null;
// Retry by calling this function recursive but of course WITHOUT rw-splitting
// because we want to fall back to the master
return $this->queryInternal($method,$mode,true);
}
if($usedConnection->enableProfiling)
Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');
return $result;
}
catch(Exception $e)
{
if($usedConnection->enableProfiling)
Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');
Yii::log('Error in querying SQL: '.$this->getText().$params,CLogger::LEVEL_ERROR,'system.db.CDbCommand');
throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
array('{error}'=>$e->getMessage())));
}
}
As you can see there are a lot of tracing messages in there to take a look at the behavior on runtime.
Beside the standard rw-splitting there is also a ‘master-fallback’ which requests a record from the master if the slave returned an empty result. This isn’t optimal yet but if the user INSERTs a record and the replication isn’t done yet the user would see a “there is not X with this id…” kind of message.
After adding the CDbConnectionRwMaster.php to /framework/db/ and changing the queryInternal() function of CDbCommand.php you can setup db connections in your main.php as following:
'db'=>array(
'class'=>'system.db.CDbConnectionRwMaster',
'connectionString'=>'mysql:host=host1;dbname=myDB',
'charset'=>'utf8',
'username'=>'user',
'password'=>'password',
'slaves'=>array('db_readonly1','db_readonly2'), // List of read-only slaves
'enableRwSplitting'=>true, // Enable read-write-splitting for this connection
),
'db_readonly1'=>array(
'class'=>'CDbConnection',
'connectionString'=>'mysql:host=host2;dbname=myDB',
'charset'=>'utf8',
'username'=>'user',
'password'=>'password',
),
'db_readonly2'=>array(
'class'=>'CDbConnection',
'connectionString'=>'mysql:host=hostX;dbname=myDB',
'charset'=>'utf8',
'username'=>'user',
'password'=>'password',
),
As mentioned above this is only a proof of concept and not ready to use in productive environments.
Don’t know but maybe this could be useful for someone. 
Best regards,
yoshi