Распределение нагрузки на MySQL
Распределение нагрузки на MySQL
Не далее, чем в прошлом году я поднимал тему "Полная переустановка сервера".
Всё сработало, мануал написан. Но вот со временем, ввиду расширения сети, увеличением пользователей, стало ясно, что некоторые сервисы в определённые моменты нереально отъедают ресурсы сервера. Вы уже знаете эту проблему - мы обращались.
В основном это касается собственно связки "Технологический модуль"-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. Возможно с подобной темой к вам обратился «СервисТелеком». Это с моей подачи.
Всё сработало, мануал написан. Но вот со временем, ввиду расширения сети, увеличением пользователей, стало ясно, что некоторые сервисы в определённые моменты нереально отъедают ресурсы сервера. Вы уже знаете эту проблему - мы обращались.
В основном это касается собственно связки "Технологический модуль"-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. Возможно с подобной темой к вам обратился «СервисТелеком». Это с моей подачи.
Re: Распределение нагрузки на MySQL
Может сейчас еще кто нибудь ответит более квалифицированно, но проблем не вижу совсем. Репликация, ПМСМ, может иметь смысл, чтобы не создавать задержки для серверов PEGAS и PTServer для записи в базу на удаленном сервере. Но я бы предложил просто разместить MySQL на другой машине и перенастроить сервера.fisher писал(а): .....
В случае репликации необходимо реплицировать обе базы.
Re: Распределение нагрузки на MySQL
Продумывал и по полному выносу мускула, но...
- за счёт реплики увеличивается отказоустойчивость системы (бекап почти в чистом виде)
- за счёт реплики увеличивается производительность системы: PEGAS (на PTServer у нас МДК-Мх не мониторятся, потому я его для себя опускаю) пишет в основную базу, ТМ читает с реплики. И не толпятся возле одного окошка
- задержка данных даже на полчаса - большой роли для ТМ не играет (по крайней мере у нас он не используется в качестве оперативного оповещения аварий), а репликация думаю будет проходить много быстрее.
То поводу репликации обоих баз тоже думал, но, как известно, при простой системе master-slave изменения на реплике не вляют на master-базу. А ТМ работает со своей базой technology не только на чтение, но и на запись (пользователи, привязка модулей к карте и т.д.). Потому либо её вообще не реплицровать, либо закрутить эту базу (или обе) в кольцо. Но как это сделать, я могу только предполагать.
Жду ещё мнений и советов
P.S. ПМСМ - это русский аналог импортного IMHO?
- за счёт реплики увеличивается отказоустойчивость системы (бекап почти в чистом виде)
- за счёт реплики увеличивается производительность системы: PEGAS (на PTServer у нас МДК-Мх не мониторятся, потому я его для себя опускаю) пишет в основную базу, ТМ читает с реплики. И не толпятся возле одного окошка
- задержка данных даже на полчаса - большой роли для ТМ не играет (по крайней мере у нас он не используется в качестве оперативного оповещения аварий), а репликация думаю будет проходить много быстрее.
То поводу репликации обоих баз тоже думал, но, как известно, при простой системе master-slave изменения на реплике не вляют на master-базу. А ТМ работает со своей базой technology не только на чтение, но и на запись (пользователи, привязка модулей к карте и т.д.). Потому либо её вообще не реплицровать, либо закрутить эту базу (или обе) в кольцо. Но как это сделать, я могу только предполагать.
Жду ещё мнений и советов
P.S. ПМСМ - это русский аналог импортного IMHO?
Re: Распределение нагрузки на MySQL
Дело не только в большом объёме данных, но и в не самой оптимальной организации таблиц, индексов... Любая оптимизация данных будет возможна только после полного перехода на сервер PEGAS. Сейчас же приходится поддерживать совместимость баз для PEGAS'а и PTServer'а. На данный момент переход невозможен из-за отсутствия поддержки в РЛТ и МЦФЛ, в стадии завершения оптика. PEGAS к переходу готов .fisher писал(а):Когда пользователь начинает строить графики - серверу MySQL посылается запрос, который подразумевает большое кол-во данных и сервис mysqld-nt.exe просто забирает большую часть ресурсов сервера (отчётливо видно по диспетчеру задач).
ТМ процессор практически не использует (если, конечно, не организовать на него DDoS атаку ), во время запроса "грузится" только служба MySQL, поэтому смысла выносить ТМ большого не вижу.fisher писал(а):Попытки оптимизировать работу сервера в целом, играясь приоритетами сервисов в системе, положительного эффекта не принесли. Либо начинал «Технологический модуль» (с вашего позволения в дальнейшем "ТМ") запираться, либо PEGAS, либо ... Можно, конечно, его вообще вынести на другое железо, но сам он не мешает работе сервера.
Мысль очень правильная. Если есть возможность выделить отдельный сервер, то только поддерживаю. Могу предложить следующий вариант:fisher писал(а):Потому появилась мысль вынести за границы основного сервера не его, а MySQL к которому он будет посылать свои запросы. А последний, в свою очередь, водрузить на "соседний" Linux-сервер. В связи с этим появились некоторые вопросы...
- реплицировать только smk30;
- настроить ТМ на вынесенную базу, БД technology будет только на вынесенной базе;
- в АРМ'ах PEGAS также настроить подключение к slave;
- ЕСМА и сервера будут обращаться только к smk30 на основной машине;
- в technology изменения вносятся не часто, поэтому достаточно раз в неделю-месяц делать backup.
ЗЫ для админов: Есть смысл настраивать репликацию только на физически вынесенную базу, также как и полностью выносить MySQL лучше на отдельный сервер. Виртуалка не катит!
ЗЫ к теме: В PEGAS сервере периодически выполняется запрос "check table ... quick" который загружает mysql при большом размере таблиц (в зависимости от производительности сервера, от 1 до неск. минут при размере таблицы порядка 1ГБ). В версии 2.2.1 заменён на "check table ... fast quick" - проверка только некорректно закрытых таблиц.
Re: Распределение нагрузки на MySQL
Возникли проблемы. Реплика создана, но появились проблемы с синхронизацией. На реплике возникла вот такая ошибка
В итоге ТМ не видит базу smk30
Дампились базы mysql,smk30,technology, но реплицицируется только smk30
Дамп снимался при остановленных ПО с заблокрованными в режим Read_only базами. Значение позиции бинлога не менялась.
Если нужен доступ - предоставлю в личку (кстати к БД у вас должен быть доступ)
Код: Выделить всё
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
Дампились базы mysql,smk30,technology, но реплицицируется только smk30
Дамп снимался при остановленных ПО с заблокрованными в режим Read_only базами. Значение позиции бинлога не менялась.
Если нужен доступ - предоставлю в личку (кстати к БД у вас должен быть доступ)
Re: Распределение нагрузки на MySQL
Скорее всего требуется вручную создать хранимую процедуру updcntr() (данные о хранимых процедурах не хранятся в директории с БД).fisher писал(а):Возникли проблемы...
Необходимо выполнить на 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;
Re: Распределение нагрузки на MySQL
Спасибо, но ...
Где-то кавычки надо ставить...
Код: Выделить всё
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
Re: Распределение нагрузки на MySQL
Вариант №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 ;
Re: Распределение нагрузки на MySQL
На счёт кавычек видимо форум подламывает и неправильные выдаёт. Поправил, но всё равно ошибку выдаёт
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
Re: Распределение нагрузки на MySQL
Можно попросить это sql-скриптом к сообщению првязать?
Удаление функции, кстати, сработало, потому как теперь новая ошибка
Удаление функции, кстати, сработало, потому как теперь новая ошибка
Last_Error: Error 'FUNCTION smk30.UPDCNTR does not exist' on query...
Re: Распределение нагрузки на MySQL
Ну вроде бы кое-что добился
Код: Выделить всё
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 ;
Re: Распределение нагрузки на MySQL
Думаю надо перед этим отключить репликацию. Если не под рутом - проверьте права доступа к smk30. Если не получится - кидайте в личку параметры подключения, будем выяснять.
Re: Распределение нагрузки на MySQL
Делал всё под рутом, потому с правами должно быть всё ОК.
Попробовал заново залить дамп, удалив базы. Ситуация повторилась - сначала 1457 ошибка, после попытки реанимировать функцию - 1305
Уже продумываю в качестве варианта повторить процедуру снятия дампа.
Данные по доступу к мускулам выслал в личку.
Попробовал заново залить дамп, удалив базы. Ситуация повторилась - сначала 1457 ошибка, после попытки реанимировать функцию - 1305
Уже продумываю в качестве варианта повторить процедуру снятия дампа.
Данные по доступу к мускулам выслал в личку.
Re: Распределение нагрузки на MySQL
Всё-таки снял дамп снова и залил на реплику. Правда теперь всё делал с слейв-сервера. Уж не знаю, что помогло, но теперь всё синхронизировалось
Перенастроил работу ТМ на реплику. Буду наблюдать как проходит синхронизация и как изменится нагрузка на сервер. Правда ещё остаются пользователи PEGAS, но .. это будет следующий этап - после окончательного вердикта, потому как данная схема - тестовая.
P.S. Кстати, реплика работает на виртуальной машине, но физически сервер другой.
Перенастроил работу ТМ на реплику. Буду наблюдать как проходит синхронизация и как изменится нагрузка на сервер. Правда ещё остаются пользователи PEGAS, но .. это будет следующий этап - после окончательного вердикта, потому как данная схема - тестовая.
P.S. Кстати, реплика работает на виртуальной машине, но физически сервер другой.
Re: Распределение нагрузки на MySQL
Утром обнаружил ошибку
UPD: А собственно проблему сам нашёл, но вот как решить - не знаю. Таблица Boards есть в обоих базах, но linux-овый MySQL различает регистры
Linux MySQL:
Windows MySQL
UPD2: Решается добавлением в конфиг MySQL опции
Видимо в формируемых 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)
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)
Код: Выделить всё
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)
Код: Выделить всё
lower_case_table_names = 1
Re: Распределение нагрузки на MySQL
Видимо в MySQL под linux имеет значение регистр символов в названии таблиц. Сейчас выложу PTServer 1.2.42, в котором это учтено.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)
Re: Распределение нагрузки на MySQL
Однозначно, что на дефолтных настройках linux-овый MySQL различает регистры. Как его отучить от этого я уже указал. Но думаю, для совместимости это было бы неплохо эту проблему устаранить в ПО и не только в PTServer
Re: Распределение нагрузки на MySQL
И снова прошу помощи.
Не знаю что произошло, но в основную базу перестали писаться измерения. Журнал событий пишется, а измерения застряли вчера между 12 и 13.
ТМ тоже ничего не видит.
Блин... Это таже ошибка, что и на реплике была
Не знаю что произошло, но в основную базу перестали писаться измерения. Журнал событий пишется, а измерения застряли вчера между 12 и 13.
ТМ тоже ничего не видит.
Блин... Это таже ошибка, что и на реплике была
SQL: error 1305('FUNCTION smk30.UPDCNTR does not exist')
Re: Распределение нагрузки на MySQL
Основной сервер отремонтирован установкой параметра
и добавлением самой функции.
Теперь на этой команде застрял слейв
Код: Выделить всё
SET GLOBAL log_bin_trust_function_creators = 1;
Теперь на этой команде застрял слейв
А что если убрать из логгирования таблицу smk30.locals?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. ...
Re: Распределение нагрузки на MySQL
Видимо копировали все БД целиком. БД mysql - служебная и отличается в 5.1 и 5.0.fisher писал(а):Основной сервер отремонтирован установкой параметраи добавлением самой функции.Код: Выделить всё
SET GLOBAL log_bin_trust_function_creators = 1;
Теперь на этой команде застрял слейвА что если убрать из логгирования таблицу smk30.locals?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. ...