Распределение нагрузки на MySQL

[fisher]

Не далее, чем в прошлом году я поднимал тему "Полная переустановка сервера".
Всё сработало, мануал написан. Но вот со временем, ввиду расширения сети, увеличением пользователей, стало ясно, что некоторые сервисы в определённые моменты нереально отъедают ресурсы сервера. Вы уже знаете эту проблему - мы обращались.
В основном это касается собственно связки "Технологический модуль"-MySQL (хотя возможно и клиенты PEGAS при работе с БД, но не так открыто). Когда пользователь начинает строить графики - серверу MySQL посылается запрос, который подразумевает большое кол-во данных и сервис mysqld-nt.exe просто забирает большую часть ресурсов сервера (отчётливо видно по диспетчеру задач). А пока процессор с памятью выгребают sql-запрос - начинают проявляться проблемы в другом серверном ПО. В частности PEGAS и мониторинг устройств ОТС, ОбТС начинают терять сетевые элементы. Не на долго - 5-10 сек. Но! Во-первых, это в итоге попадает в ЕСМА, а во-вторых - это всё равно не хорошо. А если несколько пользователей будут графики просматривать? Всё? От телеконтроля можно отказываться?
Попытки оптимизировать работу сервера в целом, играясь приоритетами сервисов в системе, положительного эффекта не принесли. Либо начинал «Технологический модуль» (с вашего позволения в дальнейшем "ТМ") запираться, либо PEGAS, либо ...

Вот поэтому я задался вопросом освободить MySQL от мощных ударных запросов данных со стороны "ТМ". Можно, конечно, его вообще вынести на другое железо, но сам он не мешает работе сервера. К тому же, как я понял, он заточен под IIS, а у нас свободной лицензии на Windows 200x Server нет.
Потому появилась мысль вынести за границы основного сервера не его, а MySQL к которому он будет посылать свои запросы. А последний, в свою очередь, водрузить на "соседний" Linux-сервер. В связи с этим появились некоторые вопросы:
«ТМ» работает с собственной БД под названием technology, в которой содержит свои служебные данные. А вот данные измерений он берёт из базы PEGAS-а smk30? Если так, то где это указывается? Судя по всему в какой-либо dll (что-то мне подсказывает ,что это technology.dll)
В принципе, решение вопроса уже созрело:
Создаём ещё один сервер MySQL (как я уже говорил на другом физическом сервере) и настраиваем его репликой к основному. Причём, если измерения выбираются именно из базы smk30, то реплицируем только её. Т.о. база technology будет как основная, а вот данные измерений будут выбираться из реплицированной.
Подскажите, пожалуйста, где я ошибаюсь.

P.S. Возможно с подобной темой к вам обратился «СервисТелеком». Это с моей подачи.

[Пинчук Андрей]

.....

Может сейчас еще кто нибудь ответит более квалифицированно, но проблем не вижу совсем. Репликация, ПМСМ, может иметь смысл, чтобы не создавать задержки для серверов PEGAS и PTServer для записи в базу на удаленном сервере. Но я бы предложил просто разместить MySQL на другой машине и перенастроить сервера.
В случае репликации необходимо реплицировать обе базы.

[fisher]

Продумывал и по полному выносу мускула, но...
- за счёт реплики увеличивается отказоустойчивость системы (бекап почти в чистом виде)
- за счёт реплики увеличивается производительность системы: PEGAS (на PTServer у нас МДК-Мх не мониторятся, потому я его для себя опускаю) пишет в основную базу, ТМ читает с реплики. И не толпятся возле одного окошка
- задержка данных даже на полчаса - большой роли для ТМ не играет (по крайней мере у нас он не используется в качестве оперативного оповещения аварий), а репликация думаю будет проходить много быстрее.

То поводу репликации обоих баз тоже думал, но, как известно, при простой системе master-slave изменения на реплике не вляют на master-базу. А ТМ работает со своей базой technology не только на чтение, но и на запись (пользователи, привязка модулей к карте и т.д.). Потому либо её вообще не реплицровать, либо закрутить эту базу (или обе) в кольцо. Но как это сделать, я могу только предполагать.

Жду ещё мнений и советов

P.S. ПМСМ - это русский аналог импортного IMHO?

[Шитов Алексей]

Когда пользователь начинает строить графики - серверу MySQL посылается запрос, который подразумевает большое кол-во данных и сервис mysqld-nt.exe просто забирает большую часть ресурсов сервера (отчётливо видно по диспетчеру задач).

Дело не только в большом объёме данных, но и в не самой оптимальной организации таблиц, индексов... Любая оптимизация данных будет возможна только после полного перехода на сервер PEGAS. Сейчас же приходится поддерживать совместимость баз для PEGAS'а и PTServer'а. На данный момент переход невозможен из-за отсутствия поддержки в РЛТ и МЦФЛ, в стадии завершения оптика. PEGAS к переходу готов .

Попытки оптимизировать работу сервера в целом, играясь приоритетами сервисов в системе, положительного эффекта не принесли. Либо начинал «Технологический модуль» (с вашего позволения в дальнейшем "ТМ") запираться, либо PEGAS, либо ... Можно, конечно, его вообще вынести на другое железо, но сам он не мешает работе сервера.

ТМ процессор практически не использует (если, конечно, не организовать на него DDoS атаку ), во время запроса "грузится" только служба MySQL, поэтому смысла выносить ТМ большого не вижу.

Потому появилась мысль вынести за границы основного сервера не его, а MySQL к которому он будет посылать свои запросы. А последний, в свою очередь, водрузить на "соседний" Linux-сервер. В связи с этим появились некоторые вопросы...

Мысль очень правильная. Если есть возможность выделить отдельный сервер, то только поддерживаю. Могу предложить следующий вариант:
- реплицировать только smk30;
- настроить ТМ на вынесенную базу, БД technology будет только на вынесенной базе;
- в АРМ'ах PEGAS также настроить подключение к slave;
- ЕСМА и сервера будут обращаться только к smk30 на основной машине;
- в technology изменения вносятся не часто, поэтому достаточно раз в неделю-месяц делать backup.

ЗЫ для админов: Есть смысл настраивать репликацию только на физически вынесенную базу, также как и полностью выносить MySQL лучше на отдельный сервер. Виртуалка не катит!
ЗЫ к теме: В PEGAS сервере периодически выполняется запрос "check table ... quick" который загружает mysql при большом размере таблиц (в зависимости от производительности сервера, от 1 до неск. минут при размере таблицы порядка 1ГБ). В версии 2.2.1 заменён на "check table ... fast quick" - проверка только некорректно закрытых таблиц.

[fisher]

Возникли проблемы. Реплика создана, но появились проблемы с синхронизацией. На реплике возникла вот такая ошибка

Код: Выделить всё

110704 11:17:55 [ERROR] Slave SQL: Error 'Failed to load routine smk30.UPDCNTR. The table mysql.proc is missing, corrupt, or contains bad data (internal code
-5)' on query. Default database: 'smk30'. Query: 'INSERT INTO `users` (`domain_id`,`user_id`,`login`,`surname`,`name`,`midname`,`directorate`,`rcs`,`post`,`tabnumber`,`deleted`,`create_time`,`delete_time`,`upd_counter`) VALUES (10,4,'smena-ctu-admin','','','','','','','',FALSE,NOW(),NULL,UPDCNTR()) ON DUPLICATE KEY UPDATE `login`='smena-ctu-admin',`surname`='',`name`='',`midname`='',`directorate`='',`rcs`='',`post`='',`tabnumber`='',`deleted`=FALSE,`delete_time`=NULL',
Error_code: 1457

В итоге ТМ не видит базу smk30
Дампились базы mysql,smk30,technology, но реплицицируется только smk30
Дамп снимался при остановленных ПО с заблокрованными в режим Read_only базами. Значение позиции бинлога не менялась.
Если нужен доступ - предоставлю в личку (кстати к БД у вас должен быть доступ)

[Шитов Алексей]

Возникли проблемы...

Скорее всего требуется вручную создать хранимую процедуру updcntr() (данные о хранимых процедурах не хранятся в директории с БД).
Необходимо выполнить на slave'е следующий запрос:

Код: Выделить всё

CREATE FUNCTION  `smk30`.`UPDCNTR`() RETURNS bigint(20)
BEGIN
   DECLARE curr_max BIGINT;
   SELECT IFNULL(MAX(upd_counter), 0) + 1 INTO curr_max FROM `smk30`.`locals`;
   UPDATE `smk30`.`locals` SET upd_counter=curr_max;
   IF ROW_COUNT()=0 THEN
     INSERT INTO `smk30`.`locals` VALUES(curr_max);
   END IF;
   RETURN curr_max;
END;

[fisher]

Спасибо, но ...

Код: Выделить всё

mysql> CREATE FUNCTION  `smk30`.`UPDCNTR`() RETURNS bigint(20)
    -> BEGIN
    -> DECLARE curr_max BIGINT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Где-то кавычки надо ставить...

[Шитов Алексей]

Вариант №2

Код: Выделить всё

DELIMITER $$

DROP FUNCTION IF EXISTS `smk30`.`UPDCNTR`$$
CREATE FUNCTION  `smk30`.`UPDCNTR`() RETURNS bigint(20)
BEGIN
   DECLARE curr_max BIGINT;
   SELECT IFNULL(MAX(upd_counter), 0) + 1 INTO curr_max FROM `smk30`.`locals`;
   UPDATE `smk30`.`locals` SET upd_counter=curr_max;
   IF ROW_COUNT()=0 THEN
     INSERT INTO `smk30`.`locals` VALUES(curr_max);
   END IF;
   RETURN curr_max;
END;

 $$

DELIMITER ;

[fisher]

На счёт кавычек видимо форум подламывает и неправильные выдаёт. Поправил, но всё равно ошибку выдаёт

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''smk30'.'UPDCNTR'' at line 1

[fisher]

Можно попросить это sql-скриптом к сообщению првязать?
Удаление функции, кстати, сработало, потому как теперь новая ошибка

Last_Error: Error 'FUNCTION smk30.UPDCNTR does not exist' on query...

[fisher]

Ну вроде бы кое-что добился

Код: Выделить всё

mysql> DELIMITER //
mysql>
mysql> DROP FUNCTION IF EXISTS `smk30`.`UPDCNTR`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION  `smk30`.`UPDCNTR`() RETURNS bigint(20)
    -> BEGIN
    ->    DECLARE curr_max BIGINT;
    ->    SELECT IFNULL(MAX(upd_counter), 0) + 1 INTO curr_max FROM `smk30`.`locals`;
    ->    UPDATE `smk30`.`locals` SET upd_counter=curr_max;
    ->    IF ROW_COUNT()=0 THEN
    ->      INSERT INTO `smk30`.`locals` VALUES(curr_max);
    ->    END IF;
    ->    RETURN curr_max;
    -> END;
    ->
    -> //
ERROR 1307 (HY000): Failed to CREATE FUNCTION UPDCNTR
mysql>
mysql> DELIMITER ;

[Шитов Алексей]

Думаю надо перед этим отключить репликацию. Если не под рутом - проверьте права доступа к smk30. Если не получится - кидайте в личку параметры подключения, будем выяснять.

[fisher]

Делал всё под рутом, потому с правами должно быть всё ОК.
Попробовал заново залить дамп, удалив базы. Ситуация повторилась - сначала 1457 ошибка, после попытки реанимировать функцию - 1305
Уже продумываю в качестве варианта повторить процедуру снятия дампа.

Данные по доступу к мускулам выслал в личку.

[fisher]

Всё-таки снял дамп снова и залил на реплику. Правда теперь всё делал с слейв-сервера. Уж не знаю, что помогло, но теперь всё синхронизировалось
Перенастроил работу ТМ на реплику. Буду наблюдать как проходит синхронизация и как изменится нагрузка на сервер. Правда ещё остаются пользователи PEGAS, но .. это будет следующий этап - после окончательного вердикта, потому как данная схема - тестовая.

P.S. Кстати, реплика работает на виртуальной машине, но физически сервер другой.

[fisher]

Утром обнаружил ошибку

Last_SQL_Errno: 1146
Last_SQL_Error: Error 'Table 'smk30.Boards' doesn't exist' on query. Default database: 'smk30'. Query: 'INSERT Boards SET domain_id=10, id="D:10 A:5.1 B:9 ��A2-4", root_id="D:10 A:5.1", type=18, name="��A2-4", state=5, pos=9, location="�������-2 ����������� (5.1) ��A2-4 N9" ON DUPLICATE KEY UPDATE state=5,location="�������-2 ����������� (5.1) ��A2-4 N9",utime=NULL'
1 row in set (0.00 sec)

Не посмотрите?

UPD: А собственно проблему сам нашёл, но вот как решить - не знаю. Таблица Boards есть в обоих базах, но linux-овый MySQL различает регистры
Linux MySQL:

Код: Выделить всё

mysql> SHOW TABLES LIKE 'boards';
+--------------------------+
| Tables_in_smk30 (boards) |
+--------------------------+
| boards                   |
+--------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES LIKE 'Boards';
Empty set (0.00 sec)

Windows MySQL

Код: Выделить всё

mysql> SHOW TABLES LIKE 'boards';
+--------------------------+
| Tables_in_smk30 (boards) |
+--------------------------+
| boards                   |
+--------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES LIKE 'Boards';
+--------------------------+
| Tables_in_smk30 (Boards) |
+--------------------------+
| boards                   |
+--------------------------+
1 row in set (0.00 sec)

UPD2: Решается добавлением в конфиг MySQL опции

Код: Выделить всё

lower_case_table_names = 1

Видимо в формируемых PEGAS-ом запросах не учитывается регистр в названиях таблиц. Будете исправлять в PEGAS или нет - вам решать.

[Пинчук Андрей]

Утром обнаружил ошибку

Last_SQL_Errno: 1146
Last_SQL_Error: Error 'Table 'smk30.Boards' doesn't exist' on query. Default database: 'smk30'. Query: 'INSERT Boards SET domain_id=10, id="D:10 A:5.1 B:9 ��A2-4", root_id="D:10 A:5.1", type=18, name="��A2-4", state=5, pos=9, location="�������-2 ����������� (5.1) ��A2-4 N9" ON DUPLICATE KEY UPDATE state=5,location="�������-2 ����������� (5.1) ��A2-4 N9",utime=NULL'
1 row in set (0.00 sec)

Не посмотрите?

Видимо в MySQL под linux имеет значение регистр символов в названии таблиц. Сейчас выложу PTServer 1.2.42, в котором это учтено.

[fisher]

Однозначно, что на дефолтных настройках linux-овый MySQL различает регистры. Как его отучить от этого я уже указал. Но думаю, для совместимости это было бы неплохо эту проблему устаранить в ПО и не только в PTServer

[fisher]

И снова прошу помощи.
Не знаю что произошло, но в основную базу перестали писаться измерения. Журнал событий пишется, а измерения застряли вчера между 12 и 13.
ТМ тоже ничего не видит.
Блин... Это таже ошибка, что и на реплике была

SQL: error 1305('FUNCTION smk30.UPDCNTR does not exist')

[fisher]

Основной сервер отремонтирован установкой параметра

Код: Выделить всё

SET GLOBAL log_bin_trust_function_creators = 1;

и добавлением самой функции.

Теперь на этой команде застрял слейв

Last_SQL_Errno: 1457
Last_SQL_Error: Error 'Failed to load routine smk30.UPDCNTR. The table mysql.proc is missing, corrupt, or contains bad data (internal code -5)' on query. ...

А что если убрать из логгирования таблицу smk30.locals?

[Шитов Алексей]

Основной сервер отремонтирован установкой параметра

Код: Выделить всё

SET GLOBAL log_bin_trust_function_creators = 1;

и добавлением самой функции.

Теперь на этой команде застрял слейв

Last_SQL_Errno: 1457
Last_SQL_Error: Error 'Failed to load routine smk30.UPDCNTR. The table mysql.proc is missing, corrupt, or contains bad data (internal code -5)' on query. ...

А что если убрать из логгирования таблицу smk30.locals?

Видимо копировали все БД целиком. БД mysql - служебная и отличается в 5.1 и 5.0.