Balance

Until recently, our resource database was served on a couple of two servers: Bonnie and Clyde . Clyde is the main server of the project, which responds to all requests, Bonnie is a server that supports databases of other projects and a slave client of the super-Sabra database.

Clyde copes well with his business, a lot of work has been done to optimize the database, so he completely grinded all calls when generating a million or more documents per day. However, in moments of unpredictable peak surges, the load from time to time passed beyond the permissible limits.

Given the ever-increasing amount of data and load, it is time to take a step towards scaling the database hardware resources.
')

A set of serious tools that can help in this matter are well known, for example, these are proxying and sharding tools and a super cluster: MySQL Proxy , Spock Proxy , MySQL Cluster . The latter, of course, is seriously different in nature from the first two.

However, at this stage, it was decided to limit ourselves to a simple and trivial solution, adding some functionality to our Propeller framework, so I decided to make a very simple balancing between Bonnie and Clyde with an assessment of their current load, that is, choosing a minimally loaded server to work out individual resource-intensive parts of the project .

It was possible to avoid the need to use an external server state assessment tool: for the experiment, the ' Threads Running ' option was selected (the number of active threads) from the standard MySQL status report. As the tests showed, it quite objectively reflects the level of server load when it comes to two machines with identical settings.

As a result, this solution came out:

• Each time the database tools are initialized, the load on the servers from the specified list is compared, the replication status of the slave servers listed in the list (' Slave_IO_Running ', ' Slave_SQL_Running ' from ' show slave status ') is checked . On the basis of this analysis, the most suitable server is selected (for whom the “ Threads Runnig ” is smaller, the one is cooler).
• The test results are placed in Memcached for a short period of time (super methods of our product’s smartGet / smartSet minimizing the likelihood of a database of queries to the database when the object is removed from the cache), since the request for obtaining full status and selecting the Threads Runnig field from it 'pretty resource-intensive, which affects with frequent execution.
• If there are servers that are equally well suited for processing requests, only one of them is randomly selected.

The solution has been applied on the super gab on the main page and on the pages of all blogs for almost a day. In-time development and testing - one day. The result of the application is positive. Servers seem to breathe and breathe evenly most of the time :-)

  PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
 32130 mysql 22 20 0 3401M 3007M kserel 6 30.3H 1.51% mysqld 

  PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
 36399 mysql 26 20 0 4697M 3565M kserel 5 22.6H 1.61% mysqld 

In order not to be unfounded, I will give the corresponding method from our class Db (a low-level class for working with a database).

Attention, this code is really used on the super gab!

<?
/**
*
* @param array $connections
* @param int $permission
*/
function connectFree ($connections, $permission = DB_R) {
if (! is_array($connections)) throw new Exception( 'connectFree: ' );

$statuses = array(); //
$pool = array(); //
$newConnections = array(); //
$minLoad = 0; //
$avName = '' ; //
$report = '' ; //

$cachePref = 'db_cStat_' ; // memcached
$cacheTime = 35; //
$cache = Box:: getInstanceOf( 'CacheMC' ); //

//
foreach ($connections as $conName) {
/**
*
*/
$cacheKey = $cachePref . $conName;
$status = $cache->smartGet($cacheKey);

//
if (!$status) {
//
if (empty($ this ->connections[$conName])) {
try {
$connection = $ this ->connect($conName);
$newConnections[] = $conName;
} catch (Exception $e) {
err( ' ' . $conName);
$report .= $conName . ': ' . '; ' ;

continue ;
}
} else {
$connection = $ this ->connections[$conName];
}

// ,
if (! empty($ this ->conParams[$conName][ 'slave' ]) && !$ this ->checkSlave($connection)) {
$report .= $conName . ': ' . '; '; ;
$status = -2;
$statuses[$conName] = $status;
}

//
// ( , )
if (!$status) {
$status = $ this ->query( 'show status like \'Threads_running\'' , $connection);

//
if ($status && $status->rowCount()) $status = $status->fetchAll();
if (! empty($status[0][ 'Value' ])) $status = $status[0][ 'Value' ]; else $status = -1;
}

//
$cache->smartSet($cacheKey, $status, $cacheTime);
}

//
if ($status) {
//
if ((!$minLoad && $status >= 0) || ($status > 0 && $status < $minLoad)) {
$minLoad = $status;
$avName = $conName;
}

$statuses[$conName] = $status;
$report .= $conName . ': ' . $status . '; ' ;
}
}

//
if (! count($statuses)) throw new Exception( 'connectFree: ' );

//
foreach ($connections as $conName) {
if ($statuses[$conName] == $minLoad) $pool[] = $conName;
}

// ,
if (count($pool) > 1) {
shuffle($pool);
$avName = $pool[0];
}

// , ,
foreach ($newConnections as $cName) {
if ($cName != $avName) $ this ->disconnect($cName);
}

//
if (empty($ this ->connections[$avName])) {
$ this ->connect($avName);
}

//
if ($permission == DB_R) {
if ($ this ->conNameRead != $avName) $ this ->init($avName);
} else {
if ($ this ->conNameWrite != $avName) $ this ->init( null , $avName);
}

//
$report .= ': ' . $avName;
debug($report, 'Db:: connectFree' );
}
?>