Техническое обслуживание сервера

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

Содержание

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

Введение

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

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

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

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

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

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

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

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

  1. Очистка базы от ненужных данных.
  2. Увеличение производительности сервера mysql.

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

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

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

  1. Первым делом надо внимательно просмотреть все используемые шаблоны и отключить там все, что вам не нужно. Например, если вам не нужен мониторинг сетевых соединений windows, обязательно отключите автообнаружение сетевых интерфейсов. Оно само по себе находит десятки виртуальных соединений, которые возвращают нули при опросе и не представляют никакой ценности. Тем не менее, все эти данные собираются и хранятся, создавая лишнюю нагрузку. Если же вам нужен мониторинг сети в windows, зайдите в каждый хост и отключите руками лишние адаптеры, которые будут найдены. Этим вы существенно уменьшите нагрузку. По моему опыту, в стандартных шаблонах windows мониторинг всех сетевых интерфейсов дает примерно 2/3 всей нагрузки этих шаблонов.
  2. Отредактируйте в используемых стандартных шаблонах время опроса и хранения данных. Возможно вам не нужна та частота опроса и время хранения, которые заданы. Там достаточно большие интервалы хранения. Чаще всего их можно уменьшить. В целом, не забывайте в своих шаблонах ставить адекватные реальной необходимости параметры. Не нужно хранить годами информацию, которая теряет актуальность уже через неделю.
  3. После отключения лишних элементов в шаблонах, нужно очистить базу данных от значений неактивных итемов. По-умолчанию, они там остаются. Можно их очистить через web интерфейс, но это плохая идея, так как неудобно и очень долго. Чаще всего попытки очистить 50-100 элементов за раз будут сопровождаться ошибками таймаута или зависанием web интерфейса. Далее расскажу, как это сделать эффективнее.

Для очистки базы данных 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

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

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

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

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

Заключение

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

Помогла статья? Подписывайся на telegram канал автора

Онлайн курс по Linux

  • Знание архитектуры Linux.
  • Освоение современных методов и инструментов анализа и обработки данных.
  • Умение подбирать конфигурацию под необходимые задачи, управлять процессами и обеспечивать безопасность системы.
  • Владение основными рабочими инструментами системного администратора.
  • Понимание особенностей развертывания, настройки и обслуживания сетей, построенных на базе Linux.
  • Способность быстро решать возникающие проблемы и обеспечивать стабильную и бесперебойную работу системы.

Автор Zerox

47 комментариев

Все эти классыне команды не работают когда ваш забикс не сферический конь в ваккууме, который развернули с рутрекера и неделю дали "подышать".
В реальной жизни мы имеем дело с монстрами которые были запущены годы назад, с невыключеными обнаружениями сетевых интерфейсов. Или даже выключеными, но успевшими сьесть 100-200 гиг.

В общем неделя пота, крови, медных труб и велосипедов без седла дали свой результат:

Что делает скрипт
1. Копирует табличку истории (там все истории) и заливает в неё все данные после $time (в юниксе — генераторы есть в сети) В моём примере дата 1.07.21
2. Ушатывает старую таблицу
3. Переименовывает копию в боевую
4. mysqldump — дампит получившееся в /root/full (кому не нужно просто уберите)

У меня хистори_юнит весил 160 гиг, 36 весил хистори. Теперь весят 25 и 10. С этим уже можно работать.

Понятное дело, кода у тебя база 200 гигов обычными селектами по всему объему с ней не поработать. Это уже запущенный случай. Гораздо раньше нужно было партицирование настраивать, чтобы иметь возможность оперативно базу чистить. Странно, что дожили до такого размера базы и не заметили проблем раньше. Хаускипер явно ее уже не числил. Он на таких размерах уже не работает.

Очень интересный вариант, Александр не подскажите по подробнее о Вашем скрипте?
Я правильно понимаю, что в строке MDB=" " необходимо указать имя файла необходимой мне базы, к примеру history_uint?
У меня к примеру ничего не получается, кроме создания папки /root/full

Предлагаю всем запросы поправить ) Может даже в статье можно.

Я прождал много времени ожидания пока выполнятся SELECT и DELETE (SSD, Памяти 80 гигоВ ядер 16)
Выполнение запроса из статьи
SELECT count(itemid) AS history_uint FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status=’0′);
Заняло больше часа, но я не выдержал.

И мне тут подсказали добрые люди, что я из полка тех кто с радугой )
И в MySQL надо делать ТАК:

SELECT hu.* FROM history_uint as hu JOIN items as it ON hu.itemId = it.ItemId WHERE it.status ‘0’
и соответственно
DELETE hu FROM history_uint as hu JOIN items as it ON hu.itemId = it.ItemId WHERE it.status ‘0’
отрабатывает за секунды.

Точно
SELECT hu.* FROM history_uint as hu JOIN items as it ON hu.itemId = it.ItemId WHERE it.status ‘0’
а не
SELECT hu.* FROM history_uint as hu JOIN items as it ON hu.itemId = it.ItemId WHERE it.status=’0′
?

По разному пробовал — не работает. Забивает свап и умирает.

Весь инет перелазил — везде "делы" предлагают, разными способами, но в итоге тупо делы.

Ребят — а может есть способ скопировать из хистори* данные за последние дней 7-30 и подменить таблички?
У меня дамп весит 80 гигов — бекапит 5 часов, разворачивать будет сутки — вообще не вариант.

А в чем разница? Негде сейчас проверить эту тему.

Сразу после установки Mysql на Red Hat по умолчанию имеются файлы

/var/lib/mysql/ibdata1
/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

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

нужно ли включить опцию, удалить файлы ibdata1, ib_logfile0, ib_logfile1 и запустить Mysql

или можно просто включить опцию и запустить Mysql?

у меня не получилось( вроде бы все прошло гладко, но забикс стартовал с ошибкой. при входе настойчиво просил авторизацию локальную. а т.к. я сделал доменку то локальные учетные данные я забыл. ну в целом что то получилось. версия забикса 4.2, версия mirabd ставил уже 10.4.10 (в общем самую последнюю). сегодня восстановил снапшот на виртуалке,буду пробовать еще раз.

версия старой Mariadb 5.5.60 возможно я обновился на 10ю ветку и поэтому все загнулось. попробую без обновления базу почистить.

почистил без обновления mariadb. получил ошибку:
The frontend does not match Zabbix database. Current database version (mandatory/optional): 0/0. Required mandatory version: 4020000. Contact your system administrator.

база после чистки и резервного копирования похудела на 34 гига. была 40 стала 6. это как то очень странно. да, я до этого настроил фильтр обнаружения сетевых интерфейсов и сократил элементы данных на 2/3, но все же слишком уж сильно похудела база. забикс ругается что не может приконектиться к базе.

9050:20191120:051532.429 Starting Zabbix Server. Zabbix 4.2.4 (revision 059af02c82).
9050:20191120:051532.429 ****** Enabled features ******
9050:20191120:051532.429 SNMP monitoring: YES
9050:20191120:051532.429 IPMI monitoring: YES
9050:20191120:051532.429 Web monitoring: YES
9050:20191120:051532.430 VMware monitoring: YES
9050:20191120:051532.430 SMTP authentication: YES
9050:20191120:051532.430 Jabber notifications: NO
9050:20191120:051532.430 Ez Texting notifications: YES
9050:20191120:051532.430 ODBC: YES
9050:20191120:051532.430 SSH2 support: YES
9050:20191120:051532.430 IPv6 support: YES
9050:20191120:051532.430 TLS support: YES
9050:20191120:051532.430 ******************************
9050:20191120:051532.430 using configuration file: /etc/zabbix/zabbix_server.conf
9050:20191120:051532.433 [Z3005] query failed: [1146] Table ‘zabbix.users’ doesn’t exist [select userid from users limit 1]
9050:20191120:051532.433 cannot use database "zabbix": database is not a Zabbix database

Вы как-то странно почистили базу, что исчезла таблица с пользователями:

Table ‘zabbix.users’ doesn’t exist

Скорее всего исчезло и что-нибудь еще. В статье я кроме таблиц с history и trends ничего не трогаю.

в том то и дело что таблица была на месте. я зашел через консоль и отобразил список всех таблиц. ничего не изменилось. команды на удаление я вводил строго по инструкции. забикс как будто ее не видел

Делаю всё по инструкции.
На шаге mysql_upgrade ошибки:

Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos
Error : Table ‘mysql.gtid_slave_pos’ doesn’t exist in engine
status : Operation failed
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats
Error : Table ‘mysql.innodb_index_stats’ doesn’t exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table ‘mysql.innodb_table_stats’ doesn’t exist in engine
status : Operation failed
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK

Repairing tables
mysql.gtid_slave_pos
Error : Table ‘mysql.gtid_slave_pos’ doesn’t exist in engine
status : Operation failed
mysql.innodb_index_stats
Error : Table ‘mysql.innodb_index_stats’ doesn’t exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table ‘mysql.innodb_table_stats’ doesn’t exist in engine
status : Operation failed
Phase 2/7: Installing used storage engines. Skipped
Phase 3/7: Fixing views
Phase 4/7: Running ‘mysql_fix_privilege_tables’
ERROR 1932 (42S02) at line 592: Table ‘mysql.innodb_index_stats’ doesn’t exist in engine
ERROR 1243 (HY000) at line 593: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1932 (42S02) at line 595: Table ‘mysql.innodb_table_stats’ doesn’t exist in engine
ERROR 1243 (HY000) at line 596: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1932 (42S02) at line 600: Table ‘mysql.innodb_index_stats’ doesn’t exist in engine
ERROR 1932 (42S02) at line 604: Table ‘mysql.innodb_table_stats’ doesn’t exist in engine
ERROR 1932 (42S02) at line 607: Table ‘mysql.innodb_table_stats’ doesn’t exist in engine

С какой на какую версию обновляетесь? При обновлении в рамках одной ветки mysql_upgrade делать не надо, будут как раз подобные ошибки. Попробуйте просто запустить базу. Если в логе ошибок не будет, то ничего и делать не надо.


Источник: serveradmin.ru