I’ve been working on ways to balance database traffic amongst multiple servers for about a week now. Initially, I had created a DB driver class that extended from DB_mysql, but opted for a cleaner approach using the decorator pattern.

Essentially, DB_Virtual allows you to connect to N database servers with one of them acting as the master. 100% of all manipulation queries (ie. DELETE, INSERT, etc.) are sent to the master nodes while SELECT queries are balanced amongst the remaining nodes using a weighted round robin approach.

The pros of this are obvious, you can take your database traffic and with little effort balance traffic amongst your database nodes. The con is that you have X * N connections per request where N is the number of database nodes and X is the number of databases you connect to.

<?php

require_once 'DB/Virtual.php';

$db = new DB_Virtual();

// Attach a master (you MUST do this first)
$result = $db->attachMaster('mysql://root@192.168.10.25/enotes_com',50);
if (PEAR::isError($result)) {
    die($result->getMessage()."n");
}

// Attach a node (do this for however many nodes you have)
$result = $db->attachNode('mysql://root@192.168.10.10/enotes_com',50);
if (PEAR::isError($result)) {
    die($result->getMessage()."n");
}

// Depending on the query DB_Virtual will either propagate the call to all
// nodes or send it to the master.
$db->setFetchMode(DB_FETCHMODE_ASSOC);

// Use DB_Virtual just as you would PEAR DB
$result = $db->query("SELECT * FROM tbl");
if (!PEAR::isError($result)) {
    while ($row = $result->fetchRow()) {
        print_r($row);
    }
}

?>

The best part is DB_Virtual is 100% compatible with PEAR’s DB package so there’s no reason to go around changing all of your code as it should work automagically through the wonders of PHP5’s overloading mechanism.

Download DB_Virtual 0.0.4