Working with models in case slave-master shared databases

[applejohnny]

1. It would be cool if I could work with model in slave-master manner. All selects goes to slave, all updates goes to master. After changes, model(maybe along with relations) works with master only, during its lifetime.
2. Suppose, I have a model User, related with table users, which shared by primary key id. For now, I can't redefine method getConnection in right way, cause I have no access to fields in this method.
3. It seems to me, model stores all fields to database, even if I change only one field. If it's right, why model doesn't store to database only modified fields?

[niden]

1/2. You could specifically set your code to work with a master/slave scenario.

For instance you can have two User classes:

class UserMaster extends \Phalcon\Mvc\Model 
{
    public function initialize() 
    {
        $this->setConnectionService('db_master');
    }

    public function getSource() 
    {
        return 'users';
    }
}

class UserSlave extends \Phalcon\Mvc\Model 
{
    public function initialize() 
    {
        $this->setConnectionService('db_slave');
    }

    public function getSource() 
    {
        return 'users';
    }
}

Then in your code you can easily use one model or another to perform your operations. For instance:

// Use slave for read
$allUsers = UsersSlave::find();

$newUser = new UserMaster();

$newUser->name = 'John';
$newUser->last_name = 'Doe';
$newUser->save();

There is no functionality at the moment to use a master slave scenario automatically in your models so you have to do the heavy lifting with your code.

I understand that this doubles your model files but this is the easiest way to implement this.

This actually is a nice feature that needs to be thoroughly researched and implemented so as to take into account multiple master databases and slave databases and tie them to respective functions of the CRUD model.

We will definitely revisit this as a feature after 1.0.

As for Number 3, this is something we could do but it is not as easy as it sounds. We need to store somewhere a snapshot of the current record and then compare it with the modified record. This doubles the memory consumption at that point and impacts performance. I agree that updating x fields instead of all fields could potentially be less taxing in the database than updating the whole record, so we need to check it out by running some metrics. If we end up having savings even by storing the current record we will implement it :)

[applejohnny]

1/2 shard-slave technology and database sharding is a common practice for highload. I think, Phalcon is oriented to highload projects, so it would be great if Phalcon will have this functionalty from the box.

About point 2

class UserSlave extends \Phalcon\Mvc\Model 
{
    public function initialize() 
    {
        $this->setConnectionService('db_slave', $this->id);
    }

    public function getSource() 
    {
        return 'users';
    }
}

As You can see, I need user ID for initializing corresponding slaved shard, but method "setConnectionService" doesn't use second parameter and $this->id is null :(

About point 3 I have found that queries are not optimized in many cases. For example, there's no need to specify table name for query with only one table, there's no need to specify all table fields( "*" can be used instead). As for highload, database is a weakest part of application. I think, there's no big problem to have some memory overhead in php, but it's a good practice to send optimized queries to database, reducing the traffic and releasing database from additional work.

[phalcon]

setConnectionService doesn't require a di:

$di->set('db_slave', function(){
   //return the slave connection here
});

class UserSlave extends \Phalcon\Mvc\Model 
{
    public function initialize() 
    {
        $this->setConnectionService('db_slave');
    }

    public function getSource() 
    {
        return 'users';
    }
}

[applejohnny]

Users table is shared by id field, so db_slave service is defined something like this:

$di->set( 'db_slave', function ( $userId ) {
  // there are a lot of slaves, that shared by user id
  // this service gets $userId parameter and returns corresponding to $userId slave connection
});

How I can use this service in my UserSlave model?

[niden]

And in the lambda function you can specify your slave selection scheme like round robin etc.

In either case both recommendations are future NFRs. I will add them in.

[phalcon]

Where $userId comes from? session? another table? Is it a value in the same table which needs the connection?

[applejohnny]

I see it like this:

$user = User::find(1); // I need get info about user with id = 1
echo $user->name;

Logic to determine the shard hidden in the User class.

[phalcon]

Perhaps the logic of the problem does not start here, the id that is passed to "findFirst" is never assigned to any object so I'm not sure if this is the way to address the problem.

This is not the optimal solution, this class intercepts the execute/query calls changing the database connection before send them query to the server

<?php

class MysqlShardManager extends \Phalcon\Db\Adapter\Pdo\Mysql
{

    protected $_masterConnection;

    protected $_shardConnections = array();

    /**
     * This method executes all write statements INSERT/UPDATE/DELETE
     */
    public function execute($sqlStatement, $bindParams=null, $bindTypes=null)
    {
        //Assign first the master connection
        $this->_pdo = $this->getMasterConnection();

        //Always to master
        return parent::execute($sqlStatement, $bindParams, $bindTypes);
    }

    /**
     * This methods executes all the read statements (SELECT)
     */
    public function query($sqlStatement, $bindParams=null, $bindTypes=null)
    {

        //Detect the shard based on the sql statement?
        $shardId = //...

        //Change the pdo connection
        $this->_pdo = $this->getShardConnnection($shardId);

        //Execute the query in the slave
        return parent::query($sqlStatement, $bindParams, $bindTypes);
    }

}

Instead of registering a database connection, the shard manager is used:

$di->set('db', function(){
  return new MysqlShardManager();
}, true);

[phalcon]

This is implemented in 1.0.0, thanks