Web applications can often be quite heavy in DB operations, and if using MyISAM tables, an expensive SELECT query can lock up your tables, blocking any INSERT/UPDATE/DELETEs until the query is complete.

A typical response to this problem is to set up replication and split the database functions across multiple machines. To start, the slave database can be responsible for reads (SELECTs) while the master database can be responsible for writes (INS/UP/DEL).

In a read-heavy application, this clear separation can sometimes offload too much work to the slave, leaving the master relatively free and bored. Another way to split the functions is like so:

• all writes go to the master
• reads are split between the master and slave(s)

This way the master gets to help out with reads as well. The only tricky part is modifying your web application to be smart enough to pick the correct database server for each query.

We'll assume that all queries go through a single function, which is the only sane way to build a web app. We'll call this function run_query(). When a web app normally initializes, it establishes a single connection to the database. We need to change this to set up two connections, one for reads and one for writes.

Then we use the time-of-day in seconds as a way of doing some rudimentary round-robin rotation through all possible slave (read-only) servers.

Here's what your initialization routine might look like:

define('DB_HOST_MASTER', 'db-master');
define('DB_USER', 'user');
define('DB_PASS', 'pass');
$DB_SLAVES = array('db-slave1','db-slave2','db-slave3');

$db = new Database();

// First, the write DB -- always use the master
$db->conn_write = mysql_pconnect(DB_HOST_MASTER, DB_USER, DB_PASS);
mysql_select_db(DB_NAME, $db->conn_write);

// Now, we'll pick a slave based on the time-of-day in seconds
$idx = time() % count($DB_SLAVES);
$slave = $DB_SLAVES[$idx];
$db->conn_read = mysql_pconnect($slave, DB_USER, DB_PASS);
mysql_select_db(DB_NAME, $db->conn_read);

Now, when running a query, we'll just look at the beginning of it to determine if it's a read query or not. If it starts with "SELECT" we'll use the read-only DB. Otherwise, we'll default to the read/write master to be safe.

class Database {
   var conn_read;
   var conn_write;

   function run_query($query) {
     if(eregi('^SELECT', $query)) {
       // use the read-only DB
       $conn =& $this->conn_read;
     } else {
       // use the read/write DB
       $conn =& $this->conn_write;
     }
     return mysql_query($query, $conn);
   }
}

Pretty simple. It's also a good idea to disable any INSERT/UPDATE/DELETE privileges on the slave servers, so nobody accidentally issues a write query to them. If a slave performs writes, it will no longer be a mirror copy of the master.