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
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()
@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^.
@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(); }thanks
thanks very much about this ext, this really helps me a lot~
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); } }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.
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.
Not a bug
It's not the end of the world, grigori. Just change
isReadOperation()topublic 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 thepreg_match().I.e. New DB connection + query itself (much heavier than)
preg_match():)
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. ;)
isReadOperation code improved
@grigori
I have improved isReadOperation method now.
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.
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.
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.
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
AlwaysReadFromMasterDBor not.You can simply set
Yii::app()->db->enableSlaveto 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.
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 ?
Is this extension valid for CActiveRecord ->save() method? I mean... will it save on master when ->save() ?
If you have any questions, please ask in the forum instead.
Signup or Login in order to comment.