Multi-region databases - spjonez - 03-01-2017
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.
RE: Multi-region databases - Nichiren - 03-04-2017
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.
RE: Multi-region databases - spjonez - 03-04-2017
(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.
RE: Multi-region databases - spjonez - 03-15-2017
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.
RE: Multi-region databases - InsiteFX - 03-15-2017
Just a note you do not need the PHP ending closing tag ?> in your classes.
RE: Multi-region databases - spjonez - 03-15-2017
(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
|