Building an Web App on a Read and Write based server set

Question

I've got a general curiosity question.

Say I have 1 master server (for writes) and 5 slave servers (for read).

Within my web application, would I initiate a connection to both the master and slave? Using the master connection only for sending writes and using the slave connection only for reading data?

score 1 · Accepted Answer · answered Jan 26 '11 at 21:44

In any case you should handle your Master and Slave connection on a per need basis, usually via a getConnection() function. On my setup at work, 2 clusters, 2 masters, 4 slaves on one, 8 on the other ones, the function basically look like this:

<?php
class Custom_Db_Handler 
{
    const READ = "read";
    const WRITE = "write";

    private static $_instance;

    private $_connections = array();
    private $_config;

    private function __construct() {
        $this->_config = Storage::get("config mysql");
    }

    public function get() {
        if(is_null(self::$_instance)) {
            self::$_instance = new self;
        }
        return self::$_instance;
    }

    public function getConnection($db, $type = "read") {
        if(array_key_exists($type, $this->_connections)) {
            return $this->_connections[$type][$db];
        }

        if($type != self::READ || $type != self::WRITE) {
            return false;
        }

        $this->_connections[$type][$db] = mysql_connect($this->_config[$type]['host'], $this->_config[$type]['user'], $this->_config[$type]['pass']);
        mysql_select_db($db, $this->_connections[$type][$db]);
        return $this->_connections;
    }

}

Usage would go along the line of :

$query = "SELECT * FROM table";
$res = mysql_query($query, Custom_Db_Handler::get()->getConnection("my_db", Custom_Db_Handler::READ));

This is a very basic example, but i guess you get the idea on how to manage master/slave connections.

score 1 · Answer 2 · answered Feb 07 '12 at 17:02

If you use PHP 5.3 and mysqlnd driver, you can add mysqlnd_ms plugin which will handle read/write split at the driver level, so no modification in your code (works with mysql, mysqli, PDO connection libraries).

For more info read: http://pecl.php.net/package/mysqlnd_ms http://www.slideshare.net/nixnutz/mysqlnd-quick-overview2011

Building an Web App on a Read and Write based server set

2 Answers2