[henqi]

We are developing a web application on Yii, which would like to implement Load Balance solution with MySQL.

E.g.: I have 2 MySQL servers, one act as Master (read/write), the other as Slave (read only). Both server also serving http requests using apache. Every data (new/update) is SAVE ONLY on Master, the Salve will replicate the data from Master, to make both server has the same updated data. Slave is purely serving READ only request.

My problem is, what is the best way to configure/setup/change inside my Yii application, to let all new/update data saved only on Master? Do I need to overide CActiveRecord::getDbConnection() in every Models.

Any advice are highly appreciated!

[killermonk]

I would just suggest having to active database connections open and using the specific connection you want when you need it. I have only ever run into problems with trying to change which database you are using magically behind the scenes. Try something like this in your config:

// protected/config/main.php
return array(
...
'db' => array(
	'class' => 'CDbConnection',
	'connectionString' => 'mysql:host=<master ip>;dbname=<dbname>;port=3306',
	'username' => '<username>',
	'password' => '<password>',
	'autoConnect' => false,
	'enableParamLogging' => YII_DEBUG,
	'schemaCachingDuration' => 54000 // 15 minutes
),
'reader' => array(
	'class' => 'CDbConnection',
	'connectionString' => 'mysql:host=<slave ip>;dbname=<dbname>;port=3306',
	'username' => '<username>',
	'password' => '<password>',
	'autoConnect' => false,
	'enableParamLogging' => YII_DEBUG,
	'schemaCachingDuration' => 54000 // 15 minutes
),
...
);

Then in your PHP script all the built-in database stuff will use the master, and when you need something to use the slave you can specify it's use directly:

$sql = "read query for slave";
$cmd = Yii::app()->reader->createCommand($sql);
$result = $cmd->query();

If you need to use ActiveRecords with this setup, I'm fairly certain that their is not anything build in, but it shouldn't be too hard to add a new child class and put some functionality in it to change which database is being used.

[Y!!]

Something like this maybe:

<?php

class MyActiveRecord extends CActiveRecord
{

  public function onBeforeSave($event)
  {
    $this->db = Yii::app()->masterDb;
  }

  public function onAfterSave($event)
  {
    $this->db = Yii::app()->db;
  }

  // ...

}

?>

Not quite sure if it works.

[jerry2801]

Y!!, on 23 November 2009 - 11:15 PM, said:

Something like this maybe:

<?php

class MyActiveRecord extends CActiveRecord
{

  public function onBeforeSave($event)
  {
    $this->db = Yii::app()->masterDb;
  }

  public function onAfterSave($event)
  {
    $this->db = Yii::app()->db;
  }

  // ...

}

?>

Not quite sure if it works.

looks nice~

[henqi]

Hi all, thanks for sharing.

I read at the Yii tutorial, there is the paragraph

If you want to use an application component other than db, or if you want to work with multiple databases using AR, you should override CActiveRecord::getDbConnection(). 

Can this help for the AR side? Just override the getDbConnection() in all my models, may be can add in some code somewhere inside each models, to check if this is a save() operation, then use the 'reader' connection instead 'db'

Can this works?

[henqi]

Y!!, on 23 November 2009 - 11:15 PM, said:

Something like this maybe:

<?php
class MyActiveRecord extends CActiveRecord
{
  public function onBeforeSave($event)
  {
    $this->db = Yii::app()->masterDb;
  }
  public function onAfterSave($event)
  {
    $this->db = Yii::app()->db;
  }
  // ...
}
?>

Not quite sure if it works.

I re-read again the Yii tutorial, found a sentences

Tip: There are two ways to work with multiple databases in AR. If the schemas of the databases are different, you may create different base AR classes with different implementation of getDbConnection(). Otherwise, dynamically changing the static variable CActiveRecord::db is a better idea.

Sounds like CActiveRecord::db can be change dynamically in any time/situation (new/read/write) even after the AR has been created (will this couse any problem?).

E.g.: By using default db (which setup as read only) connection to create the AR object, read/manipulate it, if save/update is necessary, it can directly call the save(), without need to know which connection to use, with the help of onBeforeSave() and onAfterSave().

If this is the case, then the solution proposed by our friend Y!! should be able to work!!

Can some body give more advice or idea on this?

Thanks!!!

[Y!!]

henqi, on 24 November 2009 - 01:03 AM, said:

Hi all, thanks for sharing.

I read at the Yii tutorial, there is the paragraph

If you want to use an application component other than db, or if you want to work with multiple databases using AR, you should override CActiveRecord::getDbConnection(). 

Can this help for the AR side? Just override the getDbConnection() in all my models, may be can add in some code somewhere inside each models, to check if this is a save() operation, then use the 'reader' connection instead 'db'

Can this works?

I'm not sure when getDbConnection() is triggered. If it's triggered every time a read/write happens, you could do this:

<?php

class MyActiveRecord extends CActiveRecord
{

  public $useMasterDb;

  public function onBeforeSave($event)
  {
    $this->useMasterDb = true;
  }

  public function onAfterSave($event)
  {
    $this->useMasterDb = false;
  }

  public function onBeforeDelete($event)
  {
    $this->useMasterDb = true;
  }

  public function onAfterDelete($event)
  {
    $this->useMasterDb = false;
  }

  public function getDbConnection()
  {

    if ($this->useMasterDb)
    {
      return Yii::app()->masterDb;
    }
    else
    {
      return Yii::app()->db;
    }

  }

}

?>

If it's triggered only once on init, you may try the first example I posted.

[henqi]

Will check it up later, I do believe your first sample should work properly

Thanks.

[henqi]

Hi Y!!

I copy and paste your code into one of my model, I found that onBeforeSave() or onAfterSave() is not triggered when saving the model, am I miss something?

I tryied to put them inside beforeSave()/afterSave(), it works, but it trigger an error said

CException
Description

Property "MyModel.db" is not defined.
Source File

/www/yii-1.0.10.r1472/framework/db/ar/CActiveRecord.php(450)

Can you please advice?

Thanks.

[henqi]

Hi,

I change my code to

	public function beforeSave() {

		if ($this->isNewRecord)
			$this->dateCreated = new CDbExpression('NOW()');
		else
			$this->dateModified = new CDbExpression('NOW()');

		parent::$db = Yii::app()->writer;
		return parent::beforeSave();
	}

	public function afterSave() {
		parent::$db = Yii::app()->reader;
		return parent::afterSave();
	}

by change from $this to parent.

Will it be OK? Should I use the onBeforeSave()/onAfterSave() instead of beforeSave()/afterSave(). What is their different?

[Y!!]

henqi, on 01 December 2009 - 09:52 PM, said:

Should I use the onBeforeSave()/onAfterSave() instead of beforeSave()/afterSave(). What is their different?

Actually you're using it correctly. onBeforeSave/onBeforeDelete is an event - beforeSave/beforeDelete is the actual function that triggers the event. You may read this for more info about events.

I don't know why $this->db doesn't work, but parent::$db should work without problems I'd guess.

If you figure out enhancements or something, please post them.

[killermonk]

Y!!, on 01 December 2009 - 11:14 PM, said:

Actually you're using it correctly. onBeforeSave/onBeforeDelete is an event - beforeSave/beforeDelete is the actual function that triggers the event. You may read this for more info about events.

I don't know why $this->db doesn't work, but parent::$db should work without problems I'd guess.

If you figure out enhancements or something, please post them.

$this->db does not work because $db is declared as 'public static'. You have to use parent::$db to access the public static member $db of the parent object. self::$db might work, but I can't remember how php 5.2 handle static inheritance. I'm fairly positive self::$db will work in php 5.3, but I might be wrong.

[Mike]

Nice discussion.

I guess MyActiveRecord should be the base class for all records, right? Then i think using before/afterSave() can be problematic if your AR overrides this methods. You always need to call parent::before/afterSave() there. Maybe another idea would be to add a init() method in the base class and attach the two methods (with different names) to OnBefore/OnAfterSave?

[henqi]

Hi all,

Thanks for discussion here.

To avoid unknown error/problem, I has choice to add

	public function beforeSave()
	{
		# Redirect the write request to db writer
		parent::$db = Yii::app()->writer;
		return parent::beforeSave();
	}
	public function afterSave() {
		# Restore to the readonly db
		parent::$db = Yii::app()->db;
		return parent::afterSave();
	}
	public function beforeDelete()
	{
		# Redirect the write request to db writer
		parent::$db = Yii::app()->writer;
		return parent::beforeDelete();
	}
	public function afterDelete()
	{
		# Restore to the readonly db
		parent::$db = Yii::app()->db;
		return parent::afterDelete();
	}

in all my 40 models. Create a MyActiveRecord will be a good idea, but there might be more changes to my existing code and some of unexpected problem..

Additional reason is there are model are using the beforeSave()/afterSave(), I just add the additional line in that method.

This changes will be post to production for initial test run soon. If I has any update, will reply here!

Thanks guy!

[Mike]

Ok, so to avoid that you have to add all those lines you could extend your records from this:

<?php
class BalancingActiveRecord extends CActiveRecord
{

    public function init()
    {
        $this->attachEventHandler('OnBeforeSave',   array($this,'switchToWriter'));
        $this->attachEventHandler('OnBeforeDelete', array($this,'switchToWriter'));

        $this->attachEventHandler('OnAfterDelete', array($this,'switchToReader'));
        $this->attachEventHandler('OnAfterSave',   array($this,'switchToReader'));
    }

    public function switchToWriter()
    {
        self::$db=Yii::app()->writer;
        return true;
    }

    public function switchToReader()
    {
        self::$db=Yii::app()->db;
        return true;
    }
}

Didn't test it, but would be interested if it works

[henqi]

Hi!

Nice trick! Will test it soon.. Thanks!

[killermonk]

I just want to point out (because I ran into this with some of my AR alterations) that the OnBefore* and OnAfter* events are not trigged when you use findBySql and findAllBySql, or the count commands.

If you on planning on using these functions and would like them to be consistent with everything else, you might want to find a way to make a reader your default connection and only switch to the writer when you have an update query. Once the update is down, switch the database back to the reader.

[Mike]

killermonk, on 03 December 2009 - 01:57 AM, said:

If you on planning on using these functions and would like them to be consistent with everything else, you might want to find a way to make a reader your default connection and only switch to the writer when you have an update query. Once the update is down, switch the database back to the reader.

Hmm. Isn't that exactly what the above class does?

I only see a problem with e.g. saveAttributes() as it doesn't call beforeSave(). So you could also override this method in the class above:

<?php
public function saveAttributes($attributes) {
    $this->switchToWriter();
    $ret=parent::saveAttributes($attributes);
    $this->switchToReader();
    return $ret;
}

There might be more methods, didn't check thoroughly.

Another thing: If you use before/afterSave/Delete() in an AR you must call parent::* there, otherwhise the events wont be raised.

[killermonk]

Mike, on 03 December 2009 - 04:31 AM, said:

Hmm. Isn't that exactly what the above class does?

I only see a problem with e.g. saveAttributes() as it doesn't call beforeSave(). So you could also override this method in the class above:

<?php
public function saveAttributes($attributes) {
    $this->switchToWriter();
    $ret=parent::saveAttributes($attributes);
    $this->switchToReader();
    return $ret;
}

There might be more methods, didn't check thoroughly.

Another thing: If you use before/afterSave/Delete() in an AR you must call parent::* there, otherwhise the events wont be raised.

The problem is that the switching happens automagically behind the scenes. A master-slave configuration is all find and dandy up until the point that the slave replication gets behind the master. I have worked with configurations that - because the slave is on a less powerful machine - can get very far behind during peak hours. In these situations an automatic configuration that switches between the master and slave for reads and writes is *NOT* what you want. You would save data to the master, redirect to the next page that needs to read and reference that information, and it wouldn't exist because of the latency between slave and master.

There needs to be a way to force the application to use a specific database, you cannot limit reads to only be able to happen on the slave. You do need to read off of the master sometimes.

As such, my programming philosophy is that all reads/writes happen on the master unless I, the developer, tell the application to do otherwise. This is something as simple as calling "$db->useSlave();" before executing my query. At this point, the database will remain reading from the slave until I call "$db->useMaster();", "$db->reset();", or page execution completes.

If you explicitly tell the application which database to use, then you won't run into magic ghost bugs with missing data that you can never trace down because the database with which you are interfacing transparently changes mid-transaction. Believe me, I have run into very time costly and hard to fix bugs when I had an application that automated the slave/master exchange.

Now, on that same note. If you want your application to read from the slave and write to the master by default, then that isn't necessarily a problem, and has it's benefits. If you have an application that will mostly be doing non time sensitive reads then it makes sense to use the slave by default. However, you *NEED* some way of forcing the application to be able to read from the master. If you have no way of manually forcing which database connection you use for a given query, you are setting yourself up for inevitable disaster that could be very costly to you in the future.

[hobs]

Yoshi came up with an interesting way to deal with the latency between the data on the read and write servers. It also does everything automatically in the background so it you get it working, it will always work. But it would be painful to keep the customized CDBConnection class up to date on the latest Yii version of CDBConnection.

http://www.yiiframew...rite-splitting/