DB Read-Write-Splitting

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]

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

Best regards,

yoshi

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

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

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…

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.yiiframework.com/extension/dbreadwritesplitting/

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?

Hi Jam. Please, I want to know if is valid for write method ->save() of ActiveRecord saves on master server?