🥇Използване на партициониране в MySQL за Zabbix с голям брой обекти за мониторинг

За наблюдение на сървъри и услуги отдавна и все още успешно използваме комбинирано решение на базата на Nagios и Munin. Въпреки това, това съчетание има редица недостатъци, затова ние, както и много други, активно експлоатираме Zabbix. В тази статия ще ви разкажем как с минимални усилия можете да разрешите проблема с производителността при увеличаване на броя на наблюдаваните метрики и растежа на обемите на MySQL базата данни

Проблеми при използването на MySQL база данни заедно със Zabbix

Докато базата данни беше малка и броят на съхраняваните в нея метрики беше малък, всичко беше чудесно. Стандартният процес housekeeper, който стартира самият Zabbix Server, успешно изтриваше остарели записи от базата данни, без да позволява нейния растеж. Обаче, когато броят на наблюдаваните метрики нарасна и обемът на базата данни достигна определен размер, всичко стана по-лошо. Housekeeper престана да успява да изтрива данните за отреденото му време, в базата данни останаха стари данни. По време на работата на housekeeper се получаваше повишено натоварване на Zabbix Server, което можеше да продължи дълго време. Стана ясно, че трябва да се реши възникналата ситуация.

Това е известен проблем, почти всеки, който е работил с големи обеми мониторинг в Zabbix, се е сблъсквал с това. Имаше и няколко решения: например, смяна на MySQL с PostgreSQL или дори Elasticsearch, но най-простото и доказано решение беше преминаването към партициониране на таблиците, съхраняващи данни за метриките в MySQL базата данни. Ние решихме да следваме точно този път.

Преминаване от обикновени MySQL таблици към партиционирани

Zabbix е добре документиран и таблиците, в които съхранява метрики, са известни. Това са таблиците: history, в които се съхраняват float стойности, history_str, в които се съхраняват кратки стрингови стойности, history_text, в които се съхраняват дълги текстови стойности и history_uint, в които се съхраняват цели числови стойности. Има и таблица trends, която съхранява динамиката на промените, но решихме да не я пипаме, защото нейният размер е малък и по-късно ще се върнем към нея.

Като цяло, беше ясно какви таблици трябва да се обработят. Решихме да правим партиции за всяка седмица, с изключение на последната, на базата на числата от месеца, т.е. по четири партиции на месец: от 1-во до 7-мо, от 8-мо до 14-то, от 15-то до 21-во и от 22-ро до 1-во (на следващия месец). Трудността беше в това, че трябваше да превърнем необходимите ни таблици в партиционирани „на лето“, без да прекъсваме работата на Zabbix Server и събирането на метрики.

Както ни се струва странно, самата структура на данните в таблиците дойде на помощ. Например таблицата history има следната структура:

`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',

в същото време

KEY `history_1` (`itemid`,`clock`)

Както виждаме, всяка метрика в крайна сметка се записва в таблицата с две много важни и удобни за нас полета itemid и clock. По този начин, спокойно можем да създадем временна таблица, например с име history_tmp, да настроим партиционирането за нея и след това да прехвърлим всички данни от таблицата history, а след това да преименуваме таблицата history в history_old, а таблицата history_tmp в history, след което да добавим тези данни, които не са били добавени от history_old в history и да изтрием history_old. Можем да направим това напълно безопасно, няма да загубим нищо, защото посочените по-горе полета itemid и clock осигуряват свързаност на конкретната метрика с конкретно време, а не с някакъв последователен номер.

Самата процедура на прехвърляне

Внимание! Много е желателно, преди да започнем с каквито и да било действия, да направим пълно архивно копие на базата данни. Ние всички сме живи хора и можем да допуснем грешка в набор на команди, което може да доведе до загуба на данни. Да, архивното копие няма да осигури максимална актуалност, но е по-добре да имаме такова, отколкото никакво.

И така, нищо не изключваме и не спираме. Важно е на самия MySQL сървър да има достатъчно свободно място на диска, т.е. за всяка от изброените по-горе таблици history, history_text, history_str, history_uint, поне да има достатъчно място за създаването на таблица със суфикс „_tmp“, като се има предвид, че тя ще е с такъв обем като оригиналната таблица.

Няма да описваме всичко няколко пъти за всяка от гореспоменатите таблици и ще разгледаме всичко на примера само на една от тях — таблицата history.

И така, създаваме празна таблица history_tmp на базата на структурата на таблицата history.

CREATE TABLE `history_tmp` LIKE `history`;

Създаваме необходимите ни партиции. За примера, нека направим това за един месец. Всяка партиция се създава въз основа на правило за партициониране, базирано на стойността на полето clock, което сравняваме с времевата отметка:

ALTER TABLE `history_tmp` PARTITION BY RANGE( clock ) (
PARTITION p20190201 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-01 00:00:00")),
PARTITION p20190207 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-07 00:00:00")),
PARTITION p20190214 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-14 00:00:00")),
PARTITION p20190221 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-21 00:00:00")),
PARTITION p20190301 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-01 00:00:00"))
);

Тази операция добавя партициониране за създадената от нас таблица history_tmp. Нека уточним, че данните, при които стойността на полето clock е по-малка от „2019-02-01 00:00:00“ ще попаднат в партиция p20190201, след като данните, при които стойността на полето clock е по-голяма от „2019-02-01 00:00:00“, но по-малка от „2019-02-07 00:00:00“ ще попаднат в партиция p20190207 и така нататък.

Важно забележка: Какво ще се случи, ако в нашата партиционирана таблица се появят данни, при които стойността на полето clock е по-голяма или равна на „2019-03-01 00:00:00“? Тъй като за тези данни няма подходяща партиция, те няма да попаднат в таблицата и ще бъдат загубени. Затова, трябва да не забравяте своевременно да създавате допълнителни партиции, за да избегнете подобни загуби на данни (за което ще говорим по-долу).

И така, временната таблица е подготвена. Зареждаме данните. Процесът може да отнеме известно време, но за щастие той не блокира никакви други заявки, така че просто трябва да запазите търпение:

INSERT IGNORE INTO `history_tmp` SELECT * FROM history;

Ключовата дума IGNORE при начален запис не е задължителна, тъй като в таблицата все още няма данни, обаче тя ще ви е нужна при дозареждане на данни. Освен това, може да се окаже полезна, ако по време на зареждането на данни се наложи да прекъснете този процес и да започнете отново.

И така, след известно време (може би дори след няколко часа), първото зареждане на данни е минало. Както разбирате, сега таблицата history_tmp съдържа не всички данни от таблицата history, а само тези, които бяха в нея в момента на стартиране на заявката. Тук всъщност имате избор: или правим още един проход (ако процесът на зареждане е продължил дълго), или веднага преминаваме към преименуването на таблиците, за което бе споменато по-горе. Нека най-напред за втория проход. Първо трябва да разберем времето на последната вмъкната заповед в history_tmp:

SELECT max(clock) FROM history_tmp;

Предположим, че сте получили: 1551045645. Сега използваме получената стойност в втория проход на зареждане на данни:

INSERT IGNORE INTO `history_tmp` SELECT * FROM history WHERE clock>=1551045645;

Този проход трябва да завърши значително по-бързо. Но ако първият проход е продължил часове, а вторият също е отнел дълго време, възможно е да е справедливо да направим и трети проход, който се изпълнява напълно по аналогичен начин на втория.

В края отново изпълняваме операцията за получаване на времето на последното записване в history_tmp, като изпълняваме:

SELECT max(clock) FROM history_tmp;

Да предположим, че сте получили 1551085645. Запазете тази стойност — ще ни е необходима за допълнителното зареждане.

А сега собствено, когато основното зареждане на данни в history_tmp е приключило, преминаваме към преименуването на таблиците:

BEGIN;
RENAME TABLE history TO history_old;
RENAME TABLE history_tmp TO history;
COMMIT;

Оформяхме този блок като една транзакция, за да избегнем момент на вмъкване на данни в несъществуваща таблица, тъй като след първото RENAME таблицата history няма да съществува. Но дори и ако между операциите RENAME в таблицата history дойдат някакви данни, а самата таблица все още не съществува (поради преименуването), ще получим малък брой грешки при вмъкването, които можем да пренебрегнем (имаме мониторинг, а не банка).

Сега имаме нова таблица history с партициониране, но в нея липсват данните, получени по време на последния проход за вмъкване на данни в таблицата history_tmp. Но тези данни ги имаме в таблицата history_old и сега ще ги допълним оттам. За целта ще ни е необходима предварително запазената стойност 1551085645. Защо запазихме тази стойност, а не използвахме максималното време на зареждане от текущата таблица history? Потому что новые данные уже в неё поступают и мы получим неверное время. Итак, дозаливаем данные:

INSERT IGNORE INTO `history` SELECT * FROM history_old WHERE clock>=1551045645;

След приключването на тази операция, в новата, партиционирана таблица history имаме всички данни, които бяха в старата, плюс онези, които са дошли след преименуването на таблицата. Таблицата history_old вече не е необходима. Може да я изтрием веднага, или преди да я изтрием, да направим резервно копие (ако имате параноя).

Целият гореспоменат процес трябва да се повтори за таблиците history_str, history_text и history_uint.

Какво трябва да се поправи в настройките на Zabbix Server

Сега управлението на базата данни по отношение на историята на данните лежи на нашите плещи. Това означава, че Zabbix вече не трябва да изтрива стари данни - ние ще се погрижим за това. За да предотвратите автоматичното почистване на данни от Zabbix Server, трябва да влезете в уеб интерфейса на Zabbix, да изберете менюто «Администриране», след това подменюто «Общи» и след това от падащото меню вдясно да изберете «Почистване на историята». На появилата се страница трябва да снимете всички отметки за групата «История» и да натиснете бутона «Актуализиране». Това ще предотврати ненужното почистване на таблиците. history* чрез housekeeper.

Обърнете внимание на същата страница на групата «Динамика на промените». Това е таблицата, trends, към която обещахме да се върнем. Ако и тя е станала твърде голяма и се нуждае от партициониране, махнете отметките и в тази група, след което обработете тази таблица точно както беше направено за таблиците. history*.

По-нататъшно управление на базата данни

Както беше споменато по-рано, за нормалната работа с партиционирани таблици е необходимо редовно да се създават партиции. Можете да направите това по следния начин:

ALTER TABLE `history` ADD PARTITION (PARTITION p20190307 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-07 00:00:00")));

Освен това, тъй като създадохме партиционирани таблици и забранихме на Zabbix Server да ги почиства, изтриването на стари данни е наша грижа. За щастие, тук изобщо няма проблеми. Това става просто чрез изтриване на тази партиция, чийто данни вече не ни трябват.

Например:

ALTER TABLE history DROP PARTITION p20190201;

В противовес на операторите DELETE FROM с указване на диапазон дати, DROP PARTITION се изпълнява за секунди, без да натоварва сървър и също така работи безпроблемно в случай на използване на MySQL репликация.