zabbix как очистить историю

Улучшение производительности Zabbix + PostgreSQL при помощи партиционирования и индексирования

Примерно год назад передо мной и моими коллегами была поставлена задача разобраться с использованием популярной системы мониторинга сетевой инфраструктуры — Zabbix. После изучения документации мы сразу же перешли к нагрузочному тестированию: хотели оценить с каким количеством параметров может работать Zabbix без заметных падений производительности. В качестве СУБД использовали только PostgreSQL.

В ходе тестов были выявлены некоторые архитектурные особенности разметки БД и поведения самой системы мониторинга, которые по умолчанию не позволяют выйти системе мониторинга на свою максимальную мощность работы. В результате были разработаны, проведены и апробированы некоторые оптимизационные мероприятия в основном в части настройки БД.

О результатах проделанной работы я и хочу поделиться в данной статье. Статья будет полезна как администраторам Zabbix, так и PostgreSQL DBA, а также всем желающим лучше понять и разобраться в популярной СУБД PosgreSQL.

Небольшой спойлер: на слабой машине при нагрузке в 200 тысяч параметров в минуту нам удалось снизить показатель CPU iowait с 20% до 2%, уменьшить время записи порциями в таблицы первичных данных в 250 раз и в таблицы агрегированных данных в 32 раза, уменьшить размер индексов в 5-10 раз и ускорить получение исторических выборок в некоторых случаях до 18 раз.

Нагрузочное тестирование

Нагрузочное тестирование проводилось по схеме: один сервер Zabbix, один активный Zabbix proxy, два агента. Каждый агент был настроен чтобы отдавать по 50 т. целочисленных и 50 т. строковых параметров в минуту (суммарно с двух агентов получается 200 т. параметров в минуту или по 3333 параметра в секунду). Для генерации параметров агента мы использовали плагин для Zabbix Для проверки того, какое максимальное количество параметров может генерировать агент, нужно использовать специальный скрипт от того же автора плагина zabbix_module_stress. Web-админка Zabbix имеет сложности с регистрацией больших шаблонов, поэтому мы разбили параметры на 20 шаблонов по 5 т. параметров (2500 числовых и 2500 строковых).

Метрика cpu iostat служит хорошим показателем производительности Zabbix — она отражает долю единицы времени, в течение которой процессор ожидает доступа к диску. Чем она выше — тем больше диск занят операциями чтения и записи, что косвенно влияет на ухудшение производительности системы мониторинга в целом. Т.е. это верный признак того, что с мониторингом что-то не в порядке. Кстати, на просторах сети довольно популярный вопрос «как убрать триггер iostat в Zabbix», так что это наболевшая тема, потому что существует множество причин повышения значения метрики iowait.

Вот какую картину для метрики cpu iowait мы получили спустя три дня изначально:

А вот какую картину для этой же метрики мы получили также в течение трёх дней в итоге после всех проделанных оптимизационных мероприятий, речь о которых будет идти ниже:

Как видно из графиков показатель cpu iowait упал практически с 20% до 2%, что косвенно ускорило время выполнения всех запросов на добавление и чтение данных. Теперь давайте разберёмся почему при стандартных настройках БД происходит падение общей производительности системы мониторинга и как это исправить.

Причины падения производительности Zabbix

При накоплении более 10 млн значений параметров в каждой таблице первичных данных было замечено, что быстродействие системы мониторинга резко падает, что связано со следующими причинами:

Небольшая справка по организации хранилища данных мониторинга в Zabbix. Он хранит первичные данные и агрегированные данные в разных таблицах, причём с разделением по типам параметров. Каждая таблица хранит поле itemid (неявная ссылка на зарегистрированный элемент данных в системе), временную метку регистрации значения clock в формате unix timestamp (миллисекунды в отдельном столбце) и значение в отдельном столбце (исключением является таблица логов, в ней больше полей — подобие журнала событий):

Оптимизационные мероприятия

Для повышения производительности БД PostgreSQL были проведены различные оптимизационые мероприятия, основными из которых являются партиционирование и изменение индексов. Однако стоит упомянуть парой слов ещё о нескольких важных и полезных мероприятиях, способных ускорить работу любой БД под СУБД PostgreSQL.

Важное замечание. На момент сбора материала статьи нами использовался Zabbix версии 4.0, хотя сейчас уже вышла версия 4.2 и готовится к выходу версия 4.4. Почему об этом важно упомянуть? Потому что начиная с версии 4.2 Zabbix стал поддерживать специальное мощное расширение для работы с временными рядами TimescaleDB, но пока в экспериментальном режиме: при всех достоинствах использования этого расширения есть мнение, что некоторые запросы стали работать медленнее и имеются пока ещё не решённые проблемы производительности (будут решены в версии 4.4) — прочтите эту статью. В следующей статье планирую написать о результатах нагрузочного тестирования уже с использованием расширения TimescaleDB в сравнении с данным кейсом решений. Версия PostgreSQL использовалась 10, но вся приведённая информация актуальна и для 11 и 12 версий (ждём!).

Поэтому обо всём по порядку:

Настройка конфигурационного файла с помощью утилиты pgtune

На самом деле PostgreSQL — довольно легковесная СУБД. Её конфигурационный файл по умолчанию настроен так, чтобы, как говорит мой коллега, «работать даже на кофеварке», т.е. на весьма скромном железе. Поэтому обязательно нужно настраивать PostgreSQL под конфигурацию сервера, учитывая объём памяти, количество процессоров, тип предполагаемого использования БД, тип диска (HDD или SSD) и количество подключений.

Увы, не существует единой формулы настройки всех СУБД, но есть определённые правила и закономерности, подходящие для большинства конфигураций (более тонкая настройка — уже дело рук эксперта). Для упрощения жизни DBA была написана утилита pgtune, которая была дополнена web версией пользователем le0pard — автором интересной и полезной книги по администрированию PostgreSQL.

Пример запуска утилиты в консоли с указанием 100 подключений (у Zabbix требовательная Web админка) под тип приложения «Data warehouses»:

Вынесение БД на отдельный физический диск

Данный пункт не обязателен и скорее является переходным решением на пути к полноценному распределённому кластеру, но знать о такой возможности будет полезно. Для ускорения работы БД можно вынести её на отдельный диск. Мы смонтировали диск целиком в каталог base, где хранятся все БД PostgreSQL, но вообще можно сделать по-другому: создать новый tablesbase и вынести БД (или даже только её часть — таблицы первичных и агрегированных данных мониторинга) в этот tablesbase на отдельный диск.

Предварительно нужно отформатировать диск с файловой системой ext4 и подключить его к серверу. Монтировать диск для БД нужно с меткой noatime:

Для постоянного монтирования нужно добавить в файл /etc/fstab строку:

Партиционирование таблиц истории с помощью pg_pathman

Одна из проблем, с которой мы столкнулись при нагрузочном тестировании Zabbix — PostgreSQL не успевает удалять устаревшие данные из БД. С помощью партиционирования можно разбить таблицу на составные части, тем самым уменьшить размер индексов и составных частей супертаблицы, что положительно сказывается на быстродействии БД в целом.

Партиционирование решает сразу две проблемы:

1. ускорение удаления устаревших данных путём удаления целых таблиц

2. дробление индексов под каждую составную таблицу

Для партиционирования в PostgreSQL есть четыре механизма:

1. стандартный constraint_exclusion

2. расширение pg_partman (не путайте с pg_pathman)

4. вручную создавать и поддерживать партиции самим

Наиболее удобным, надёжным и оптимизированным решением для партиционирования, по нашему мнению, является расширение pg_pathman. При таком методе партиционирования планировщик запросов гибко определяет в каких партициях искать данные. Поговаривают, что в 12ой версии PostgreSQL будет отличное партиционирование уже «из коробки».

Таким образом, мы стали писать данные мониторинга за каждый день в отдельную унаследованную таблицу от супертаблицы и удаление устаревших значений параметров стало происходить через удаление сразу всех устаревших таблиц целиком, что гораздо проще для СУБД по трудозатратам. Удаление сделали через вызов пользовательской функции БД как параметр мониторинга Zabbix сервера в 2 часа ночи с указанием допустимого диапазона хранения статистики.

Устанавливаем и настраиваем расширение pg_pathman из стандартного репозитория ОС (инструкцию по сборке последней версии расширения из исходников ищите в этом же репозитории на github):

Перезагружаем СУБД, создаём расширение для БД и выполняем настройку партиционирования (1 день для первичных данных мониторинга и 3 дня для агрегированных данных мониторинга — можно было сделать и по 1 дню):

Если в какой-то из таблиц ещё нет данных, то нужно при вызове функции create_range_partitions передать ещё один дополнительный аргумент p_count = 0_.

Полезные запросы для наблюдения и управления партиционированием:

Полезный скрипт для просмотра статистики по количеству и размеру партиций:

Чтобы настроить автоудаление партиций, нужно создать функцию в БД
(широкий текст, поэтому пришлось убрать подсветку синтаксиса):

Для автоматического вызова функции автоочистки партиций нужно создать один элемент данных для хоста сервера zabbix типа «Монитор БД» со следующими настройками:

В результате будем получать статистику по очисткам партиций примерно такого вида:

Изменение типов индексов таблиц истории на brin (clock) и btree-gin (itemid)

Отдельное спасибо пользователю erogov за прекрасный цикл обзорных статей про индексы PostgreSQL. Да и вообще всей команде PostgresPRO. Под впечатлением этих статей мы поигрались с разными типами индексов на таблицах данных мониторинга и пришли к выводу какие типы индексов на каких полях дадут максимальный прирост производительности.

Было замечено, что на всех таблицах данных мониторинга по умолчанию создаётся составной индекс btree(itemid, clock) — он быстрый для поиска, особенно для монотонно упорядоченных значений, но сильно «пухнет» на диске, когда данных много — более 10 млн.

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

В ходе тестирования различных индексов было выявлено наиболее удачное сочетание индексов: индекс brin на поле clock и индекс btree-gin на поле itemid для всех таблиц данных мониторинга.

Индекс brin идеально подходит для монотонно возрастающих данных, таких как временная метка факта какого-либо события, т.е. для временных рядов. А индекс btree-gin — это по сути gin индекс над стандартными типами данных, что в целом намного быстрее классического индекса btree т.к. gin индекс не перестраивается в ходе добавления новых значений, а лишь дополняется ими. Индекс btree-gin ставится как расширение к PostgreSQL.

Сравнение скорости выполнения выборок для этой стратегии индексирования и для индексов в БД Zabbix по умолчанию приведён ниже. В ходе нагрузочных тестов мы накопили данные за три дня по трём партициям:

Для оценки результатов выполнялись три вида запросов:

Результаты теста были сведены в таблицу ниже:

* размер в МБ указан суммарно для трёх партиций
** запрос типа 1 — данные за 3 дня, запрос типа 2 — данные за 12 часов, запрос типа 3 — данные за один час

Из сравнительной таблицы видно, что для больших таблиц данных с количеством записей более 100 млн чётко прослеживается, что изменение стандартного составного индекса btree на два индекса brin и btree-gin благотворно отразилось на уменьшении размера индексов и ускорении времени выполнения запросов.

Эффективность индексирования и партиционирования показана ниже на примере запроса добавления новых записей в таблицы history_uint и trends_uint (добавления происходят в среднем по 2000 значений за запрос).

Обобщая результаты тестов различных конфигураций индексов для таблиц данных мониторинга системы zabbix можно сказать, что подобное изменение стандартного индекса для таблиц данных мониторинга системы zabbix положительно сказывается на быстродействии системы в целом, что сильнее всего ощущается при накоплении объёмов данных в размере от 10 млн. Также не стоит забывать о косвенном эффекте «разбухания» стандартного btree индекса по умолчанию — частые перестроения многогигобайтного индекса приводит к сильной загрузке жёсткого диска (метрика utilization), что в конечном итоге повышает время операций с диском и время ожидания доступа к диску со стороны CPU (метрика iowait).

Но, чтобы индекс btree-gin мог работать с типом данных bigint (in8), которым является столбец itemid, нужно выполнить регистрацию семейства операторов типа bigint для индекса btree-gin.

Для индекса brin для нашего объёма данных при интенсивности в 100 т. параметров в минуту (100 т. в history и 100 т. в history_uint) было замечено, что на таблицах первичных данных мониторинга индекс при размере зоны в 512 страниц работает в два раза быстрее, чем при стандартном размере в 128 страниц, но это индивидуально и зависит от размера таблиц и конфигурации сервера. В любом случае индекс brin занимает очень мало места, а вот скорость его работы может быть чуть-чуть увеличена с помощью тонкой настройки размера зоны, но при условии, что интенсивность потока данных не сильно меняется.

В качестве итога стоит отметить, что существует ограничение, связанное с архитектурой самого Zabbix: на вкладке «Последние данные» собираются по два последних значения для каждого параметра с учётом фильтрации. По каждому параметру значения запрашиваются в БД отдельно. Поэтому чем больше таких параметров будет отобрано, тем дольше будет выполняться запрос. Наиболее быстро последние данные ищутся, когда на таблицах history установлен индекс btree(itemid, clock desc) именно с обратной сортировкой по времени, но при этом сам индекс конечно «пухнет» на диске и в целом косвенно замедляет работу с БД, что вызывает проблему, описанную выше.

Поэтому есть три выхода из положения:

Сбор и анализ статистики выполнения запросов pg_stat_statements

Pg_stat_statements — расширение для сбора статистики выполнения запросов в рамках всего сервера. Преимущество данного расширения в том, что ему не требуется собирать и парсить логи PostgreSQL.

Установка расширения через psql:

Настройки для расширения в конфигурационном файле postgresql.conf:

Полезный запрос для просмотра среднего времени выполнения операций на таблицах:

Настройка параметров мониторинга физических дисков

Для мониторинга жёстких дисков в Zabbix из коробки предусмотрены только параметры vfs.dev.read и vfs.dev.write. Эти параметры не дают информации о загруженности дисков. Полезными критериями для поиска проблем с производительностью жёстких дисков являются показатели коэффициента загруженности utilization, время выполнения запроса await и загруженность очереди запросов к диску.

Как правило высокая загруженность диска коррелирует с высоким iowait самого cpu и с ростом времени выполнения sql запросов, что и было установлено при нагрузочном тестировании zabbix сервера со стандартной конфигурацией без партиционирования и без настройки альтернативных индексов. Добавить эти параметры мониторинга жёстких дисков можно с помощью следующих действий, которые были подсмотрены в статье у товарища lesovsky и улучшены: теперь параметры iostat собираются отдельно по каждому диску в json временный параметр, откуда по настройках постобработки уже раскладываются в конечные параметры мониторинга.

Пока Pull request ожидает на рассмотрении, вы можете попробовать развернуть мониторинг параметров дисков по подробной инструкции через мой fork.

После всех описанных действий можно добавить на главную панель мониторинга сервера Zabbix кастомный график с iowait cpu и параметрами utiliztion для системного диска и диска с БД (если они разные). Результат может выглядеть так (sda — основной диск, sdc — диск с БД):

Аппаратное улучшение производительности

После настройки СУБД, индексирования и партиционирования можно приступить к вертикальному масштабированию — улучшить аппаратные характеристики сервера: добавить оперативной памяти, поменять накопители на твёрдотельные и добавить процессорных ядер. Это гарантированный прирост производительности, но лучше это сделать только после программной оптимизации.

Создание распределённого кластера

После умеренного вертикального масштабирования нужно приступать к горизонтальному — создавать распределённый кластер: делать либо шардирование, либо репликации мастер-слейв. Но это уже отдельная тема и материал отдельной статьи (как слепить кластер из говна и палок), как и сравнение вышеописанного методики оптимизации БД Zabbix с использованием pg_pathman и индексирования с методикой применения расширения TimescaleDB.

А пока остаётся надеяться, что материал данной статьи оказался полезным и познавательным!

Источник

Очистка, оптимизация, настройка mysql базы Zabbix

Я работаю с серверами малого и среднего бизнеса, небольших компаний, где требования к объему и производительности базы данных не высоки. Чаще всего хватает дефолтных настроек, поэтому какого-то особенного внимания бд я не уделял. Но сегодня я разберу вопрос большого размера базы данных zabbix, расскажу как ее уменьшить и как оптимизировать ее работу для увеличения быстродействия.

Введение

В своей инструкции по установке и настройке zabbix я вообще не затрагиваю вопрос базы данных mysql или производительности сервера в целом. Я просто беру дефолтные настройки mariadb, которые идут с установкой и использую их. Когда у вас не очень большая инфраструктура на мониторинге этого вполне достаточно, чтобы нормально пользоваться системой.

Если вы активно используете zabbix и внедряете его повсеместно во все используемые системы (а я рекомендую так делать), то вы рано или поздно столкнетесь с вопросом производительности системы мониторинга и размера базы данных zabbix.

Тема производительности zabbix очень индивидуальная. Она напрямую зависит от того, как вы его используете, а схемы мониторинга могут быть очень разные. Одно дело мониторить несколько серверов, а другое дело нагруженные свичи на 48 портов со съемом метрик с каждого порта раз в 30 секунд.

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

Как спланировать нагрузку на Zabbix

Под небольшой структурой, упомянутой в начале, я подразумеваю 50-100 узлов (не сетевое оборудование с десятками портов) сети на мониторинге и примерно 2000-4000 активных элементов данных, которые записывают 20-40 новых значений в секунду. Под такую сеть вам будет достаточно небольшой виртуальной машины с 2 ядрами и 4 гб памяти. База данных на преимущественно стандартных шаблонах будет расти примерно на 2-4 Гб в год. Дальше еще меньше, так как будет автоматически очищаться.

Для мониторинга такой сети можно вообще не выполнять никаких дополнительных настроек. Мониторинг будет вполне нормально работать. Если же нагрузка начнет расти, то первое, с чем вы столкнетесь — это с размером и производительностью базы данных. База zabbix будет расти пропорционально подключению к ней хостов. И с этим придется что-то делать.

Для решения вопроса производительности нужно будет двигаться в двух направлениях:

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

Очистка и уменьшение mysql базы zabbix

Начнем с очистки базы данных zabbix от ненужных данных. Рассмотрим по пунктам в той последовательности, в которой это нужно делать.

Для очистки базы данных zabbix от значений неактивных итемов, можно воспользоваться следующими запросами. Запускать их можно как в консоли mysql сервера (максимально быстрый вариант), так и в phpmyadmin, кому как удобнее.

Данными запросами можно прикинуть, сколько данных будет очищено:

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

Данными запросами вы очистите базу данных zabbix от значений неактивных элементов данных. Но реально размер базы данных у вас не уменьшится, потому что в дефолтной настройке базы данных используется формат innodb. Все данные хранятся в файле ibdata1, который автоматически не очищается после удаления данных из базы.

Администрировать такую базу неудобно. Нужно как минимум настроить хранение каждой таблицы в отдельном файле. Этим мы далее и займемся, а заодно обновим сервер базы данных до свежей версии mariadb и реально очистим базу, уменьшив ее размер.

Обновление и настройка сервера mysql

В данном примере я использую сервер CentOS 7. Из стандартных репозиториев устанавливается достаточно старая версия MariaDB 5.5. Для начала обновим ее до последней стабильной на момент написания статьи версии 10.2. Перед этим сделаем полный бэкап базы данных zabbix, предварительно остановив сервер.

Теперь удалим с сервера mysql все базы данных, кроме системных.

Удаляем старые файлы базы zabbix.

Начинаем обновлять сервер. Добавляем репозиторий MariaDB в систему. Для этого создаем файл /etc/yum.repos.d/mariadb.repo следующего содержания:

Далее предлагаю свой вариант конфига для mysql сервера. Положите его в директорию /etc/my.cnf.d.

Сразу предупреждаю, что универсальных настроек для mariadb не существует. Я не большой специалист по тонкой настройке mysql и детально не вникал в оптимизацию работы с zabbix. И тем более не тестировал производительность с разными параметрами. Данный пример это набор рекомендаций, полученных из разных источников. Я сам использую этот конфиг и каких-то нареканий к нему у меня нет, поэтому делюсь с вами. Возможно, тут есть что-то, что совершенно не подходит и надо поменять. Если вы увидите такое, прошу поделиться информацией.

Будет вообще здорово, если кто-то предложит свой более оптимальный конфиг для zabbix. Хотя я понимаю, что настройки будут сильно зависеть от параметров сервера (память и cpu в основном). Их нужно подбирать в каждом конкретном случае. В ситуации со стандартной установкой zabbix, когда проблем с производительностью нет, мне не хочется этим заниматься.

Запускаем сервер mariadb.

Если сервер не стартует, а в логе /var/log/messages ошибка:

Удалите файлы aria_log.

И снова запускайте mariadb. Проверить статус запуска можно командой:

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

Запускаем утилиту mysql_upgrade для генерации новой базы performance_schema.

Запускаем сервер zabbix.

Проверяем работу. По идее, все должно быть в порядке. Теперь база данных zabbix хранится в директории /var/lib/mysql/zabbix. Она уменьшилась в размере, и каждая таблица хранится в отдельном файле.

Заключение

Я описал простой способ очистки и небольшого увеличения производительности базы zabbix. Кардинально этот вопрос решают с помощью партицирования базы данных mysql. Подробнее об этом можно почитать на сайте заббикса в wiki в разделе howto. Ничего сложного в этом нет, примеров в сети много, но лично я сам не настраивал никогда, не было необходимости.

Источник