Использование партиционирования в 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 репликации.

    Заключение


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

    Comments 11

      0
      Спасибо. Как раз собирался этим заняться после НГ
      Правда мне на лету это делать не особо и нужно. Производительности хватает пока.
      Я думаю просто начать создавать партиций начиная с определенного момента, а потом, через год, отключить housekeeping и начать дропать хвосты.

      кстати, как считаете, вот эта хранилка из wiki zabbix-а достаточно надежная для автоматизации обслуживания партицирования?
        0

        Хорошая статья с точки зрения технических подробностей.
        Странно что у нее ни плюсов ни минусов, в то время, как рядом всякий бред плюсуют.


        Зачем делать два прохода переливки, если можно в самом начале выполнить


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

        и дальше уже упражняться с копией данных как заблагорассудится?

          +1
          В MySQL DDL операции (ALTER, CREATE, RENAME TABLE и другие) не работают с транзакциями и выполняются сразу же, а не при комите.
          Чтобы выполнить RENAME атомарно следует делать так:
          CREATE TABLE history_tmp LIKE history;
          RENAME TABLE history TO history_old, history_tmp TO history; ;
          


          По теме самой статьи. Мы тоже некоторое время использовали партиционирование, но оно не удобно по следующим причинам:
          • Меняется схема данных. При обновлениях могут вылезать проблемы. (Актуально для версий <3.2, т.к. для них требовалась модификация индексов для работы партиционирования. )
          • Удаление данных происходит только целыми партициями. Нельзя одну метрику хранить неделю, а другую два года.


          Поэтому сейчас мы применяем не партиционирование, а храним историю в таблицах с движком RocksDB. Плюсы следующие:
          • Встроенный housekeeper работает даже на больших объемах данных. Можно гибко назначать метрикам время хранения.
          • Отличное сжатие. По сравнения с InnoDB компрессией данные стали занимать примерно в 2 раза меньше места.
          • Простая процедура миграции. Надо только поставить RocksDB плагин и сделать ALTER нужных таблиц.
          • Не меняется схема, только движок для нескольких таблиц. Пока еще ни разу не было проблем при апгрейде. (3.4 -> 4.0 -> 4.2)
          • Выборки из БД также работают быстрее.


          В общем для существующих инсталляций лучше использовать решение с RocksDB. Для новых лучше наверно использовать официальное решение с TimescaleDB.
            0
            Почитал про RocksDB. Ничего не нашёл про репликацию. Её нет? А как же отказоустойчивость?
              0
              Работает стандартная MySQL репликация.
              Обратите внимание, что RocksDB это только библиотека и она никак не завязана на MySQL и может применяться отдельно от него. А есть RocksDB плагины к MySQL (например у Percona он зовется MyRocks), которые реализуют интерфейс взаимодействия с библиотекой и интегрируют ее в MySQL.
              Почитать подробнее можно например тут.
            0
            Вначале статьи было написано, что необходимо было всё сделать «на лету». Если сделать так, как вы предлагаете (т.е. создать пустую таблицу, а затем лить туда данные из оригинальной), то на время переливки данных (которое может занять несколько часов) все эти данные окажутся недоступны. Т.е. не будет (как минимум) возможности смотреть и анализировать графики. Это было неприемлимо.
              0

              Где именно я предложил "создать пустую таблицу, а затем лить туда данные из оригинальной"?
              Я предложил только "создать пустую таблицу и сделать два переименования".
              Деталей реализации RENAME в MySQL я не знаю, но исхожу из предположения, что эта операция только редактирует метаданные таблиц (и эта операция выполняется за несколько милисекунд).


              Возможно вы заметили слово "копия данных" в конце сообщения. Это я неточно выразился. Я имел в виду таблицу history_old, которая получится в результате выполнения указанного мной скрипта.

            +1

            Победа над mysql это конечно всегда приятно и радует любого нормального админа. Особенно когда применяются новые решения. Однако, именно для таких случаев zabbix включил у себя поддержку timescale в рамках работы с postgresql. Там, по сути, используется автопартицирование "из коробки" и удаление чанков посуточно. Причём настройка крайне просто и хорошо описана в документации zabbix. Перенос данных из mysql в postgresql потребует не очень длительной остановки, но он вполне возможен.

              0

              Если поупражняться, то можно сделать плавный и бесшовный переезд за пару часов накидать скриптик.

              0

              Инструкция хорошая, но совсем не полная. Таблицы надо обслуживать, то есть удалять и добавлять патриции, и делать это надо никак не вручную.
              Ничего не сказано про обновлнния, которые на партиционированных таблицах могут не отрабатывать

                0
                По своей практике мне сказать, что лучше секционировать все таблицам истории, в т.ч. trends, ибо равно или поздно понадобится их очистка. Удобно делать на функциях в mysql для автоматического создания и удаления партиций, есть подробная официальная документация у zabbix.

                Only users with full accounts can post comments. Log in, please.