1

I have an e-commerce website (built upon Opencart), utilizing Master-Slave replication (1 Master and 2 Slave DB servers). For effective load balancing, people recommended us to use Maxscale. However, to avoid unnecessary increase in costs due to licensing and additional servers (for Maxscale), we implemented a weighted-random load balancing on our own. At the end, I am posting some relevant and interesting portions from my code, which many people may find helpful :-)

My code can achieve the following:

  • All Write (DML) queries are always routed to Write (Master) DB Server.
  • Queries inside a Transaction/Lock/Unlock operation, are always routed to Write Server.
  • Immediately after a Write operation, any number of Read operations can also be routed to Write server. For eg: I allow 2 Read operation, because sometimes due to Replication lag, inserted/updated data in the Master server, does not immediately reflect in the Slave server(s).
  • Read load is randomly distributed, but in weighted manner across various Slave (Read) servers.

Now, I am inclined to switch to an ORM, and after a lot of Google searches, I am unable to find a straightforward ORM solution for my requirement(s). My question is: Are any ORM(s) which allow handling of Master-Slave load balancing with flexibility for weighted load distribution ?

PS: This question was considered too broad by StackOverflow community. So, I am hoping that this community may help in this!

Please find below the code snippet. I am also open to further improvement suggestions by the community:

<?php
/*
* DB class to manage database connections for READ or WRITE servers
* Note: To use this class need to pass list of READ and WRITE server list in 
*       DB class constructor
* If we want this DB object to use only WRITE servers, see useWriteDbOnly() function.            
*/

declare(strict_types=1);   //declare strict typing

namespace Database;

class DB {

/******************/
/****  PUBLIC  ****/
/******************/

/** 
 * Class constructor with database servers (READ/WRITE) list as parameters
 * eg: $db_servers = array(
                        // Slave DB servers     
                        array(
                            'host'          =>  'xxx.xxx.xxx.xxx',
                            'username'      =>  'slave1',
                            'password'      =>  'password',
                            'is_slave'      =>  true,
                            'weightage'     =>  75      // 75% weightage to Slave 1 for reading
                            ),
                        array(
                            'host'          =>  'xxx.xxx.xxx.xxx',
                            'username'      =>  'slave2',
                            'password'      =>  'password',
                            'is_slave'      =>  true,
                            'weightage'     =>  25      // 25% weightage to Slave 2 for reading
                            ),
                        // Master DB Servers
                        array(
                            'host'          =>  'xxx.xxx.xxx.xxx',
                            'username'      =>  'master',
                            'password'      =>  'password',
                            'is_slave'      =>  false,
                            'weightage'     =>  0       // 0% ensures only Write loads - in emergency it can be used for reading.
                            )
                        );
*/
public function __construct( array $db_servers )
{

    // Set database write commands
    $this->setWriteDbCommands();

    // Bifurcate the servers into Read and Write
    $this->bifurcateDbServers();

    // Shuffle DB servers array based on weightage
    $this->shuffleDbServersOnWeightage($this->read_db_servers);
    $this->shuffleDbServersOnWeightage($this->write_db_servers);

    // Make Database connection object for Read and Write operations
    // You can use MySQLi or PDO here.
    $this->read_db_conn = $this->makeConnection($this->read_db_servers);
    $this->write_db_conn = $this->makeConnection($this->write_db_servers);

    // By default, first active connection will be READ
    $this->setActiveConnectionToRead();
}

/*
* Public method to set active DB Class object to use only for Write operations
*  for either READ or WRITE operations.
* @return void
*/
public function useWriteDbOnly(): void {
    $this->use_write_db_only = true;
    $this->setActiveConnectionToWrite(true);
}

/**
* Bunch of other Public methods
* for Querying, Fetching, Preparing, 
* Escaping, etc.
* We use $active_db_conn to for these public methods.
* Also, makeConnection method is implemented, which is 
* similar to making a MySQLi/PDO connection.
*/


/******************/
/****  PRIVATE ****/
/******************/

// Database Connections
private $active_db_conn; // DB connection currently used for a query etc
private $write_db_conn;  // DB connection to Write server
private $read_db_conn;   // DB connection to Read server
private $use_write_db_only    = false; // for always using Write DB connection
private $is_active_conn_write = false; // if active connection is Write

// Database Servers
private $db_servers       = array(); // All DB servers
private $read_db_servers  = array(); // All READ DB Servers
private $write_db_servers = array(); // All WRITE DB Servers 

// At times, we need to immediate call read operation after a write.
// This read operation is on the data, which has just been written by us.
// Due to slave lag at times, read gives inconsistent data. 
// So, we allow for 2 SELECT queries post a write query on Write DB connection.
private $select_on_write_conn_counter = 0;
private $max_continuous_select_on_write_db;

// If we are in Transactional queries, we do it on Write DB connection only.
// It is continued until we hit a COMMIT or ROLLBACK
private $wait_for_commit_rollback = false;

// Query keywords used to identify if it is a WRITE operation.
private $write_commands = array();


/*
* private method to set Write DB commands
*/
private function setWriteDbCommands()
{
    $this->write_commands = array(
    'create', 
    'alter', 
    'drop', 
    'truncate',
    'comment', 
    'rename', 
    'insert', 
    'update',
    'delete', 
    'merge', 
    'call', 
    'lock', 
    'unlock',
    'start', 
    'commit', 
    'rollback', 
    'savepoint',
    'set'
    );
}


/*
* private method to bifurcate DB Servers into Read and Write.
* Slave servers will be Read only.
* Master servers can be both Read and Write.
* @return: void
*/
private function bifurcateDbServers(): void
{
    foreach ($this->db_servers as $value) {

        // Defaulting to true to avoid risk of writing on a slave server
        $is_slave = (bool)($value['is_slave'] ?? true);

        // Every server can be Read server
        $this->read_db_servers[] = $value;

        if (!$is_slave) {
            // Only non-Slave can be Write
            $this->write_db_servers[] = $value;
        }
    }
}

/*
* Private method to shuffle DB servers array.
* As per weightage, a randomly selected Server 
* will be pushed to beginning.
* @param &$servers array (passed by reference)
* @return void
*/
private function shuffleDbServersOnWeightage(array &$servers) : void 
{
    // get total weightage sum
    $weightage_sum = (int)array_sum(array_column($servers, 'weightage'));

    // if invalid sum of weightage, do nothing to array
    if ($weightage_sum > 0) {

        // choose a random between 1 and the sum of the weights.
        $random = random_int( 1, $weightage_sum );

        //loop over servers list
        foreach( $servers as $key => $details ) {

            // ***The next two lines are the heart of this algorithm***
            // decrement the random by the current weighting.
            $random -= $details['weightage'];        

            // The larger the weighting, the more likely random is less than zero.
            if( $random <= 0 ) {

                // remove the $key from array
                unset($servers[$key]);
                // move it to beginning
                array_unshift($servers, $details);

                // Break out of loop - our job is done
                break;
            }
        }
    }
}

/*
* private method to update active db connection based on active query
* @return: void
*/
private function updateActiveConnection(string $sql): void {

    $dml_query = false;

    $words = str_word_count(strtolower(trim($sql)), 1);
    $first_word = isset($words[0]) ? $words[0] : '';
    $second_word = isset($words[1]) ? $words[1] : '';

    if (in_array($first_word, $this->write_commands)) {
        // if it is not "set" then we set to master link
        if ($first_word !== 'set'
        || ($first_word === 'set' && $second_word === 'autocommit')
        || ($first_word === 'set' && $second_word === 'transaction')
        ) {
            $dml_query = true;

            // If we Lock tables or Begin a Transaction, we should run on Write servers only
            // till we Commit/Rollback or Unlock Tables
            if(($first_word === 'start' && $second_word === 'transaction') 
            || $first_word === 'lock'){
                $this->wait_for_commit_rollback = true;
            }

            // We are doing Commit/Rollback or Unlock Tables
            if ($first_word === 'commit' 
            || $first_word === 'rollback' 
            || $first_word === 'unlock') {
                $this->wait_for_commit_rollback = false;
            }
        }
    }

    // It's a insert/update/delete/etc query - to be run on Write Db only
    if ($dml_query || $this->wait_for_commit_rollback) { 
        $this->setActiveConnectionToWrite(true);            

    } elseif (!$dml_query && $this->is_active_conn_write) { 
        // Not a DML query, but we are running on Master
        $this->select_on_write_conn_counter++;

        if ($this->select_on_write_conn_counter > $this->max_continuous_select_on_write_db) {
        $this->setActiveConnectionToRead();
        }

    } else {
        $this->setActiveConnectionToRead();
    }

}

private function setActiveConnectionToWrite(bool $reset_select_counter) : void {

    $this->is_active_conn_write = true;
    $this->active_db_conn = $this->write_db_conn;
    if ($reset_select_counter) {
        $this->select_on_write_conn_counter = 0;
    }
}

private function setActiveConnectionToRead() : void {

    $this->is_active_conn_write = false;
    $this->active_db_conn = $this->read_db_conn;
    $this->select_on_write_conn_counter = 0; // also reset the SELECT queries counter

}
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.