Welcome Guest, Not a member yet? Register   Sign In
Multi-region databases
#1

(This post was last modified: 03-01-2017, 07:25 AM by spjonez.)

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.
Reply
#2

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.
Reply
#3

(This post was last modified: 03-04-2017, 09:08 AM by spjonez.)

(03-04-2017, 03:10 AM)Nichiren Wrote: 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.
Reply
#4

(This post was last modified: 03-16-2017, 06:00 AM by spjonez.)

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_valuesarray_filterexplode','$_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 ) * sin$dlat ) + cosdeg2rad$lat1 ) ) * cosdeg2rad$lat2 ) ) * sin$dlon ) * sin$dlon );
    
$c asinsqrt$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 $driverget_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->_readtrue );
        }
        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' ] == ) {
                            
$read'name' ] = $server'name' ];
                            
$read'reader' ] = (int)$server'reader' ];
                            
$read'distance' ] = $h;
                        }

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

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

                        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 strtolowersubstrtrim$sql ), 0) );

        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.
Reply
#5

(This post was last modified: 03-15-2017, 03:12 PM by InsiteFX.)

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 )
Reply
#6

(03-15-2017, 03:11 PM)InsiteFX Wrote: Just a note you do not need the PHP ending closing tag ?> in your classes.

I know i think it looks weird without them Tongue
Reply




Theme © iAndrew 2016 - Forum software by © MyBB