Yii 1.1: dbreadwritesplitting

Database read write operation use splitting connection.Read from slave database,write to master database.
18 followers

A very simple component that allow you to use master/slave splitting database connection.That read operation will route to slave db,write operation will route to master db.

Notice:You need config your mysql database server to synchronize bettween master/slave,this component doesn't finish the data synchronize job!

Requirements

Base CDbConnection

Usage

Configure it as your system default db connection component.

'components'=>array(
    'db'=>array(
        'class'=>'DbConnectionMan',//Specify it,instead of CDbConnection,other options is same as CDbConnection
        'connectionString' => 'mysql:host=localhost;dbname=test',
        'emulatePrepare' => true,
        'username' => 'root',
        'password' => 'root',
        'charset' => 'utf8',
        'enableSlave'=>true,//Read write splitting function is swithable.You can specify this value to false to disable it.
        'slaves'=>array(//slave connection config is same as CDbConnection
            array(
                'connectionString'=>'mysql:host=slave1;dbname=xxx',
                'username'=>'root',
                'password'=>'root'
            ),
            array(
                'connectionString'=>'mysql:host=slave2;dbname=xxx',
                'username'=>'demo',
                'password'=>'xxx'
            ),
        ),
    ),
)

Update 2012-10-08:

Support slave db random choices.

Fixed:createCommand accept null parameter;

Improved:isReadOperation no longer use preg_match

Update 2013-07-15:

Fixed: Tolerate master db in a dead state.

Update 2013-09-13: Fixed: Mark master db dead bug.

Improvement: When one slave or master db down,it will mark this server dead for 10 minutes in memcache.So it will not try again connect to dead server untill 10 minutes elapsed.

My Blog:jex.im

Total 12 comments

#10154 report it
Jam at 2012/10/08 03:23am
simple transation detect

@grigori I have write a simple transation detect:!$this->getCurrentTransaction(). If current query is within a transaction,then always use master db.

#10151 report it
grigori at 2012/10/08 02:56am
thanks for a fix

Thank you for your attention, this looks better.

Actually, framework should support replication in the inner level. The cases like transactions should be taken into account: within the transaction we can't run SELECTs from the slave. Additionally the sync time should be taken into account and we can't use a slave to SELECT sensitive values like money balances from a slave right after calling a stored procedure.

So the problem is much deeper then just a query analysis.

#10149 report it
Jam at 2012/10/07 11:02pm
isReadOperation code improved

@grigori I have improved isReadOperation method now.

#10003 report it
grigori at 2012/09/28 04:21am
not the end of the world

There is a better solution for replication. The code is abandoned and buggy. That's all I wanted to say. ;)

#9994 report it
Ezekiel Victor at 2012/09/27 01:57pm
Not a bug

It's not the end of the world, grigori. Just change isReadOperation() to public static function.

Any perceived performance drawback from preg_match() is going to be outweighed heavily by the fact that using this extension you'll have two DB connections open per user, and the fact that it is only used when you're issuing a query, and the query itself will be far heavier than the preg_match().

I.e. New DB connection + query itself (much heavier than) preg_match()

:)

#9988 report it
grigori at 2012/09/27 08:47am
Bug report
self::isReadOperation($sql)
...
 public function isReadOperation($sql) {

A static function should be declared as static.

Pity, but preg_match has a noticable performance drawback and I can't use this.

#8311 report it
Ezekiel Victor at 2012/05/24 06:03pm
createCommand() backwards compatibility

Response to antonywu (comment #6553), better yet:

public function createCommand($query=NULL) {
        if (is_string($query) && $this->enableSlave && !$this->getCurrentTransaction() && self::isReadOperation($query)) {
            return $this->getSlave()->createCommand($query);
        } else {
            return parent::createCommand($query);
        }
    }

This ensures full backwards compatibility.

#6553 report it
antonywu at 2012/01/18 11:02am
Should modify the createCommand to accept null

There are quite a few places where createCommand() is called without a parameter given. For example, if you use CDbHttpSession class, instead of the default CHttpSession, you can run into PHP warnings inside the regenerateID() method.

Thus, the simplest fix is to accept $sql being null and redirect that to the parent::createCommand

public function createCommand($sql = null) {
        if ($sql && $this->enableSlave && !$this->getCurrentTransaction() && self::isReadOperation($sql)) {
            return $this->getSlave()->createCommand($sql);
        } else {
            return parent::createCommand($sql);
        }
    }
#3134 report it
Francis.TM at 2011/03/20 01:30am
thanks

thanks very much about this ext, this really helps me a lot~

#2381 report it
Jam at 2010/12/20 04:55am
@mdomba

If needed(I think,it is useful for routing the query to random slave db on each application life cycle,for load balance). declare a init method,add below code:

public function init() {
    shuffle($this->slaves);
    parent::init();
}
#2379 report it
Jam at 2010/12/19 08:56pm
@mdomba

I think slave connection should always be reused.Because creating connection will spend time,thus there no need for random connection. Could do you describe some cases that need create new slave db connection each read qurey? On the other hand,this component is very simple(only several tens of lines),in fact,in productive environment,I think they will use mysqlproxy to implements read/write splitting. I think this component wasn't really much of use,just an example for the topic that I saw in forum about db read/write splitting ^0^.

#2367 report it
Maurizio Domba Cerin at 2010/12/17 07:33am
nice

Interesting idea... by looking at the code seems that when once a slave is chosen it always returns the same slave... wouldn't be better to choose a different slave for every call?

Or at least have an option/function like randomizeSlave()

Leave a comment

Please to leave your comment.

Create extension