Multi-region databases

Does anyone know of an extension to the database class that supports multi-region read/write clusters? I searched around a bit and wasn't able to find one. Figured I'd ask before I write one myself.

If it had IP geolocation built in that would be ideal but I can build that on my own easily enough if it doesn't. Ideally I'd like an array of database connections set by country and a master write connection. Then throughout the code an easy way to specify if the query is a read or write, writes always go to master and reads use the closest region to the user.

Unless I've misunderstood, if you're going multi-regional to reduce latency, wouldn't that logic be further up the stack towards DNS or the regional load balancers instead of in the application? Otherwise, the web server handling the request could potentially be in a different continent from the database it needs to read/write to.

Unless I've misunderstood, if you're going multi-regional to reduce latency, wouldn't that logic be further up the stack towards DNS or the regional load balancers instead of in the application? Otherwise, the web server handling the request could potentially be in a different continent from the database it needs to read/write to.

Since there's a single master write and multiple read replicas each query in your application needs to know which database to hit depending on what it's doing. Any query that does a change (insert, update, delete) goes to the single write master and any select's go to the geographically closest read replica. Extending the database driver turned out to be easier than I thought there's a page in the Github wiki on how to do it. Unfortunately for accuracy you need to buy an IP to lat/lng database the free ones aren't very good. Once you know the users location you can haversine the distance to each of your read replicas to select the closest one.

CI is so awesome. Managed to do it without having to change any application logic!

Here's the code in case anyone else wants it.

application/helpers/geo_helper.php

PHP Code:

<?php if ( !defined( 'BASEPATH' ) ) exit( 'No direct script access allowed' );

function get_remote_addr( ) {
    if ( isset( $_SERVER[ 'HTTP_X_FORWARDED_FOR' ] ) && $_SERVER[ 'HTTP_X_FORWARDED_FOR' ] ) {
        $ips = array_values( array_filter( explode( ',', $_SERVER[ 'HTTP_X_FORWARDED_FOR' ] ) ) );

        $ip = end( $ips );
    }
    else {
        $ip = $_SERVER[ 'REMOTE_ADDR' ];
    }

    return $ip;
}

function haversine( $lat1, $lon1, $lat2, $lon2 ) {
    $dlat = deg2rad( $lat2 - $lat1 );
    $dlon = deg2rad( $lon2 - $lon1 );
    $r = 6372.797;

    $a = sin( $dlat / 2 ) * sin( $dlat / 2 ) + cos( deg2rad( $lat1 ) ) * cos( deg2rad( $lat2 ) ) * sin( $dlon / 2 ) * sin( $dlon / 2 );
    $c = 2 * asin( sqrt( $a ) );

    $km = $r * $c;

    return $km;
}

?>

application/core/MY_Loader.php

PHP Code:

<?php if ( !defined( 'BASEPATH' ) ) exit( 'No direct script access allowed' );

class MY_Loader extends CI_Loader {
    /* overloaded methods */

    public function database( $params = '', $return = false, $query_builder = null ) {
        $ci =& get_instance( );

        if ( $return === false && $query_builder === null && isset( $ci->db ) && is_object( $ci->db ) && !empty( $ci->db->conn_id) ) {
            return false;
        }

        require_once( BASEPATH . 'database/DB.php' );

        $db =& DB( $params, $query_builder );

        $driver = config_item( 'subclass_prefix' ) . 'DB_' . $db->dbdriver . '_driver';
        $file = APPPATH . 'libraries/' . $driver . '.php';

        if ( file_exists( $file ) === true && is_file( $file ) === true ) {
            require_once( $file );

            $dbo = new $driver( get_object_vars( $db ) );
            $db = & $dbo;
        }

        if ( $return === true ) {
            return $db;
        }

        $ci->db = '';
        $ci->db = $db;

        return $this;
    }
}

?>

libraries/MY_DB_mysqli_driver.php

PHP Code:

<?php if ( !defined( 'BASEPATH' ) ) exit( 'No direct script access allowed' );

class MY_DB_mysqli_driver extends CI_DB_mysqli_driver {
    private $_read = 'write';
    private $_rdb = null;

    /* internal methods */

    private function _q( $sql, $binds = false, $return_object = null ) {
        return parent::query( $sql, $binds, $return_object );
    }

    private function _set_reader( ) {
        $ci =& get_instance( );
        $s = $ci->load->is_loaded( 'session' );

        if ( $s === 'session' && $ci->session->userdata( '_read_replica' ) !== null ) {
            $this->_read = $ci->session->userdata( '_read_replica' );
            $this->_rdb = $ci->load->database( $this->_read, true );
        }
        else {
            $ip = get_remote_addr( );
            $parts = explode( '.', $ip );
            array_pop( $parts );
            $ip = implode( '', $parts );

            if ( $ip != '12700' ) {
                    $sql = "
                    SELECT 
                        ip.latitude, ip.longitude 
                    FROM 
                        ip_location ip 
                    WHERE 
                        ip_from LIKE ?
                    LIMIT 1
                ";

                $result = $this->_q( $sql, $ip . '%' )->row_array( );

                if ( empty( $result ) == false ) {
                    $sql = "
                    SELECT 
                        db.name, db.latitude, db.longitude 
                    FROM 
                        db_location db 
                    WHERE
                        db.enabled = 1
                ";

                    $servers = $this->_q( $sql )->result_array( );

                    $read = array(
                        'name' => 'write',
                        'reader' => 0,
                        'distance' => 0,
                    );

                    foreach ( $servers as $server ) {
                        $h = haversine( $result[ 'latitude' ], $result[ 'longitude' ], (float)$server[ 'latitude' ], (float)$server[ 'longitude' ] );

                        if ( $h < $read[ 'distance' ] || $read[ 'distance' ] == 0 ) {
                            $read[ 'name' ] = $server[ 'name' ];
                            $read[ 'reader' ] = (int)$server[ 'reader' ];
                            $read[ 'distance' ] = $h;
                        }

                        //echo ( $server[ 'name' ] . ' is ' . $h . ' away from user.<br>' );
                    }

                    if ( $read[ 'reader' ] === 1 ) {
                        $this->_read = $read[ 'name' ];
                        $this->_rdb = $ci->load->database( $this->_read, true );

                        if ( $s === 'session' ) {
                            $ci->session->set_userdata( '_read_replica', $this->_read );
                        }

                        //echo( '<br>' . $this->_read . ' chosen as read replica' );
                    }
                    else {
                        $this->_rdb = $this;

                        //echo( '<br>write chosen as read replica' );
                    }
                }
                else {
                    $this->_rdb = $this;

                    //echo( '<br>write chosen as read replica' );
                }
            }
            else {
                $this->_rdb = $this;

                //echo( '<br>write chosen as read replica' );
            }
        }
    }

    /* public methods */

    public function reader( ) {
        if ( $this->_rdb === null ) {
            $this->_set_reader( );
        }

        return $this->_rdb;
    }

    public function writer( ) {
        return $this;
    }

    public function query( $sql, $binds = false, $return_object = null ) {
        $t = strtolower( substr( trim( $sql ), 0, 6 ) );

        if ( $t === 'select' ) {
            return $this->reader( )->_q( $sql, $binds, $return_object );
        }
        else {
            return $this->_q( $sql, $binds, $return_object );
        }
    }
}

?>

migration

PHP Code:

<?php if ( !defined( 'BASEPATH' ) ) exit( 'No direct script access allowed' );

class Migration_Geo extends CI_Migration {
    public function up( ) {
        $sql = "
            CREATE TABLE `ip_location` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `ip_from` int(10) unsigned NOT NULL,
              `ip_to` int(10) unsigned NOT NULL,
              `latitude` double NOT NULL,
              `longitude` double NOT NULL,
              PRIMARY KEY (`id`),
              KEY `ip_location_ip_from_idx` (`ip_from`),
              KEY `ip_location_ip_to_idx` (`ip_to`),
              KEY `ip_location_ip_from_to_idx` (`ip_from`,`ip_to`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8
        ";

        $this->db->query( $sql );

        $sql = "
            CREATE TABLE `db_location` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(45) NOT NULL,
              `latitude` decimal(7,4) DEFAULT NULL,
              `longitude` decimal(7,4) DEFAULT NULL,
              `replica` tinyint(1) unsigned NOT NULL DEFAULT '1',
              `enabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
              PRIMARY KEY (`id`),
              KEY `db_location_enabled_idx` (`enabled`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8
        ";

        $this->db->query( $sql );

        $data = array(
            array( 'name' => 'oregon', 'latitude' => '43.8041', 'longitude' => '-120.5542', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'california', 'latitude' => '36.7782', 'longitude' => '-119.4179', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'virginia', 'latitude' => '37.4315', 'longitude' => '-78.6568', 'replica' => '0', 'enabled' => '1' ),
            array( 'name' => 'ohio', 'latitude' => '40.4172', 'longitude' => '-82.9071', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'saopaulo', 'latitude' => '-23.5505', 'longitude' => '-46.6333', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'ireland', 'latitude' => '53.1423', 'longitude' => '-7.692', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'frankfurt', 'latitude' => '50.1109', 'longitude' => '8.6821', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'london', 'latitude' => '51.5073', 'longitude' => '-0.1277', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'singapore', 'latitude' => '1.352', 'longitude' => '103.8198', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'sydney', 'latitude' => '-33.8688', 'longitude' => '151.2092', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'tokyo', 'latitude' => '35.6894', 'longitude' => '139.6917', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'seoul', 'latitude' => '37.5665', 'longitude' => '126.9779', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'mumbai', 'latitude' => '19.0759', 'longitude' => '72.8776', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'beijing', 'latitude' => '39.9042', 'longitude' => '116.4073', 'replica' => '1', 'enabled' => '0' ),
            array( 'name' => 'toronto', 'latitude' => '43.6532', 'longitude' => '-79.3831', 'replica' => '1', 'enabled' => '0' ),
        );

        $this->db->insert_batch( 'db_location', $data );
    }

    public function down( ) {
        $this->db->query( "DROP TABLE ip_location" );
        $this->db->query( "DROP TABLE db_location" );
    }
}

?>

In your config/database.php file, set your default group to 'write' and create settings that match each enabled 'name' from db_location.

You will need to populate ip_location with a copy of IP2Location. I'd recommend using the paid version the free database is not very accurate and will lead to strange results. You'll also need to update db_location with the lat/lng's of your colo's and flag replica's as 1 and your write master as 0. The set included is for AWS.

Just a note you do not need the PHP ending closing tag ?> in your classes.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Just a note you do not need the PHP ending closing tag ?> in your classes.

I know i think it looks weird without them