Yii 1.1: dbreadwritesplitting

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

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.

        '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

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 15 comments

#19458 report it
Criesto at 2015/07/14 03:25am
How to know if the data I'm fetching is from slave or master.

I downloaded the "DbConnectionMan" file and placed it under my components folder, and did the necessary changes to my db config, how do I know if the data I'm fetching is from master db or slave db ?

#18592 report it
Jam at 2014/11/25 01:30am
A read operation immediately after a write operation may get the old data

@Lemar I know it. Actually,in my own project,this request is implemented as a simple global flag.And in its execution lifetime,it is either AlwaysReadFromMasterDB or not. You can simply set Yii::app()->db->enableSlave to false after a write operation if your code will issue a read operation later in the same http request lifetime.But it is not always need read from master after a write operation in the same lifetime,because in many cases,if the write operation is done by your program,it means your program already have the new data,so you did not need to read it from database again.And also I have no idea to detect which table was updated.

The slave db cannot be updated manually,but the cache can.So in my project,I do a manual cache update after a write operation.Thus if a user post a new article,everyone can see it in article list immediately,although the slave db still retains the old data,but the cache is fresh.

So the only way is:do it manually.Or you can consider use MysqlCluster or postgreSQL cluster solution.

#18570 report it
Lemar at 2014/11/19 06:13am
Slave number of second behind

How to deal best with slaves which are some seconds behind the master? Scenario would be a write operation sent to the master and right after a read operation sent to the slave which haven't yet the actual data. What would be the best solution for that?

My suggestion would be, if a write operation has been done, all read operation after that in the same request have to go to the master.

#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
 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 very much about this ext, this really helps me a lot~

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

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() {
#2379 report it
Jam at 2010/12/19 08:56pm

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

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