Использование партиционирования в MySQL для Zabbix с большим количеством объектов мониторинга

Для мониторинга серверов и служб у нас давно, и все еще успешно, используется комбинированное решение на базе Nagios и Munin. Однако эта связка имеет ряд недостатков, поэтому мы, как и многие, активно эксплуатируем Zabbix. В этой статье мы расскажем о том, как минимальными усилиями можно решить проблему с производительностью при увеличении числа снимаемых метрик и росте объемов БД MySQL

Проблемы использование БД MySQL совместно с Zabbix

Пока БД была маленькой и количество хранимых в ней метрик небольшим, всё было замечательно. Штатный процесс housekeeper, который запускает сам Zabbix Server успешно удалял устаревшие записи из БД, не давая ей расти. Однако, как только количество снимаемых метрик выросло и объём БД достиг определённого размера, всё стало хуже. Houserkeeper перестал успевать удалять данные за отведённый ему интервал времени, в БД стали оставаться старые данные. Во время работы 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 до момента выполнения второго 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 не пытался чистить данные сам, вам необходимо зайти в web-интерфейс 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 репликации.

Заключение

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

Источник: habr.com