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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.