Monday, October 12, 2009

An idea for DB server load balancing

If the application has a heavy load of reading queries and there are no problems with the application requirements,
a simple possible idea to balance the db server loading is to run the INSERT/UPDATE/ALTER queries in all the servers and run the SELECT queries in just one server (chosen randomly).
Have a look at the 'loadBalancedQuery' function: if the query modifies the db it will be executed in both server, otherwise in just one.

class BalancedDB extends Mysqli {

private function queryServer($query, $server = "mainServer" ) {
$db = DB::GetInstance($server);
return
$db->query();
}

public function
loadBalancedQuery($query) {
if (
preg_match("/[]*(insert|INSERT|update|UPDATE|alter|ALTER).*/",
$query)){
$this->queryServer( $query, "mainServer" );
$this->queryServer( $query, "secondaryServer" );
} else {
$this->queryServer( $query, rand(0,1) ? "mainServer": "secondaryServer" );
}
}

}

1 comment:

  1. If there's additional interest in load balancing take a look at the affordables offered by companies like KEMP Technologies or the large enterprise ones offered by companies like F5 networks.

    http://www.kemptechnologies.com/?utm_source=blog&utm_medium=pv&utm_content=zs&utm_campaign=home

    ReplyDelete

 

PHP and tips|PHP