До недавних пор база данных нашего ресурса обслуживалась на пару двумя серверами: Bonnie и Clyde. Clyde — основной сервер проекта, отвечающий на все запросы, Bonnie — сервер, поддерживающий базы других проектов и слейв-клиент базы суперхабра.

Clyde хорошо справляется со своим делом, была проведена большая работа по оптимизации базы, так что он вполне перемалывал все обращения при генерации миллиона с лишним документов в сутки. Однако, в моменты непредсказуемых пиковых скачков нагрузка время от времени переваливала за допустимые пределы.

Учитывая постоянно растущий объём данных и нагрузку, настала пора предпринять шаг в сторону масштабирования аппаратных ресурсов базы данных.


Набор серьёзных средств, которые могут помочь в этом деле вполне известны, например, это средства проксирования и шардинга и супер-кластер: MySQL Proxy, Spock Proxy, MySQL Cluster. Последний, конечно, серьёзно отличается по своей сути от двух первых.

Однако, на данном этапе было решено ограничиться простым и тривиальным решением, внеся некую функциональность в наш фреймворк Propeller, поэтому я решил сделать очень простую балансировку между Bonnie и Clyde с оценкой их текущей нагрузки, то есть выбором минимально нагруженного сервера для отработки отдельных ресурсоёмких частей проекта.

Удалось избежать потребности использования внешнего средства оценки состояния сервера: для эксперимента был выбран параметр 'Threads Running' (количество активных тредов) из стандарного статусного отчёта MySQL. Как показали испытания, он вполне объективно отражает уровень нагруженности сервера если речь идёт о двух машинах с идентичными настройками.

В результате вышло такое решение:
  • При каждой инициализации средств работы с базой данных, производится сравнение загруженности серверов из заданного списка, проверяется состояние репликации слейв-серверов, представленных в списке ('Slave_IO_Running', 'Slave_SQL_Running' из 'show slave status'). На основе этого анализа выбирается наиболее подходящий сервер (у кого 'Threads Runnig' меньше, тот и круче).
  • Результаты тестов помещаются в Memcached на короткий период времени (супер-методы smartGet/smartSet нашего же изделия, минимизирующие вероятность образования шквала запросов к базе данных в момент удаления объекта из кеша), так как запрос получения полного статуса и выбор из него поля 'Threads Runnig' довольно ресурсоёмок, что сказывается при частом его выполнении.
  • Если есть сервера, одинаково хорошо подходящие для отработки запросов, случайным образом выбирается только один из них.


Решение применяется на суперхабре на главной странице и страницах всех блогов уже почти сутки. Врямя разработки и тестирования — один день. Результат применения — положительный. Сервера как будто дышат и большую часть времени дышат ровно :-)

  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


Чтобы не быть голословным, приведу соответствующий метод из нашего класса Db (класс низкого уровня для работы с базой данных).

Внимание, этот код действительно используется на суперхабре!

<?
/**
* Устанавливает соединение с наименее нагруженным сервером из списка
* @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');
}
?>