Yii Framework Forum: DB Read-Write-Splitting - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

DB Read-Write-Splitting Using master-slave connections - proof of concept Rate Topic: ***-- 1 Votes

#1 User is offline   yoshi 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 170
  • Joined: 28-February 09
  • Location:Germany

Posted 01 April 2010 - 07:13 AM

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;
        }
    }   
}
?>


2. CDbCommand

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. :unsure:

Best regards,
yoshi
0

#2 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 01 April 2010 - 11:32 AM

Didn't look very thoroughly, but your posting reminded me of a discussion from last year. Maybe it gives some inspiration:

http://www.yiiframew...ail-over-setup/
0

#3 User is offline   yoshi 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 170
  • Joined: 28-February 09
  • Location:Germany

Posted 01 April 2010 - 05:42 PM

View PostMike, on 01 April 2010 - 11:32 AM, said:

Didn't look very thoroughly, but your posting reminded me of a discussion from last year. Maybe it gives some inspiration:

http://www.yiiframew...ail-over-setup/


Hi mike,

you're right, it's about the same topic. But in the thread you've posted it's model based what doesn't give you a fine-grained control over read and write querys. This approach here is low-level so it can also control querys performed without AR or using stuff like queryBySql() and e.g. SHOW-statements performed by yii.

Thanks for your reply - i appreciate your comments!

Best regards
0

#4 User is offline   jamesmoey 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 17-July 09

Posted 24 May 2010 - 12:59 AM

You should post it as an extension. I think it is a useful extension for everyone with master/slave db configuration.
0

#5 User is offline   hobs 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 09-June 11
  • Location:Borneo

Posted 25 July 2011 - 10:33 PM

I had a similar problem--development collaboration remotely through a slow connection to db. I would definitely use this if it were modularized as an extension or patch or branch and kept up to date with the latest yii version. Doesn't look like others have taken up the gauntlet though...
0

#6 User is offline   NaX 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 51
  • Joined: 13-January 12
  • Location:Johannesburg

Posted 29 February 2012 - 09:37 AM

I know this is an old post, but I was doing some searching and found this post and if anybody else is interested the dbreadwritesplitting extension here: http://www.yiiframew...writesplitting/
0

#7 User is offline   roninio 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 28-March 12

Posted 01 April 2012 - 04:15 AM

View PostNaX, on 29 February 2012 - 09:37 AM, said:

I know this is an old post, but I was doing some searching and found this post and if anybody else is interested the dbreadwritesplitting extension here: http://www.yiiframew...writesplitting/



hi i have tried the extension dbreadwritesplitting on my application but it doesnt seem to go to the slave and stays on the master. Using yii 1.1.10
anyone has implmented this?
0

#8 User is offline   Jam 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 26-November 10

Posted 15 July 2013 - 04:06 AM

View Postroninio, on 01 April 2012 - 04:15 AM, said:

hi i have tried the extension dbreadwritesplitting on my application but it doesnt seem to go to the slave and stays on the master. Using yii 1.1.10
anyone has implmented this?


I'm the author of this extension.I use this extension in my company project for one more years,and it runs well.
Do you still can reproduce this bug today?
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users