Очистка, оптимизация, настройка 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, кому как удобнее.

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

SELECT count(itemid) AS history FROM history WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

SELECT count(itemid) AS history_uint FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

SELECT count(itemid) AS history_str FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

SELECT count(itemid) AS history_text FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

SELECT count(itemid) AS history_log FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

SELECT count(itemid) AS trends FROM trends WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

SELECT count(itemid) AS trends_uint FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

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

DELETE FROM history WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

DELETE FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

DELETE FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

DELETE FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

DELETE FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

DELETE FROM trends WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

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

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

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

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

# systemctl stop zabbix-server

# /usr/bin/mysqldump --opt -v --databases zabbix -uzabbix -ppassword > /root/zabbix.sql

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

# mysql -uroot -ppassword

> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| zabbix |

+--------------------+

> drop database zabbix;

> drop database performance_schema;

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

# rm /var/lib/mysql/ibdata1

# rm /var/lib/mysql/ib_logfile0

# rm /var/lib/mysql/ib_logfile1

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

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.2/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

Устанавливаем обновление:

# yum install MariaDB-server MariaDB-client

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

# cat /etc/my.cnf.d/zabbix.cnf

[client]

port		= 3306

socket		= /var/lib/mysql/mysql.sock

default-character-set=utf8

[mysqld]

character_set_server=utf8

collation-server=utf8_bin

init_connect="SET NAMES utf8 collate utf8_bin"

port		= 3306

socket		= /var/lib/mysql/mysql.sock

back_log = 50

skip-networking

max_connections = 100

max_connect_errors = 10

table_open_cache = 2048

max_allowed_packet = 16M

binlog_cache_size = 2M

max_heap_table_size = 64M

read_buffer_size = 4M

read_rnd_buffer_size = 32M

sort_buffer_size = 16M

join_buffer_size = 16M

thread_cache_size = 4

ft_min_word_len = 4

memlock

default-storage-engine = InnoDB

thread_stack = 240K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 128M

log-bin=mysql-bin

binlog_format = mixed

expire_logs_days = 5

log_warnings

slow_query_log

long_query_time = 10

server-id = 1

innodb_file_per_table=1

innodb_file_format=barracuda

innodb_buffer_pool_size = 4G # внимание на параметр! установить примерно в 2 раза меньше объема оперативной памяти сервера

innodb_buffer_pool_instances=2

innodb_flush_log_at_trx_commit = 0

innodb_log_file_size = 512M

innodb_log_files_in_group = 3

innodb_flush_method=O_DSYNC

innodb_lock_wait_timeout = 120

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

safe-updates

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 8192

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

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

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

# systemctl start mariadb

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

[ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables

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

# rm /var/lib/mysql/aria_log.00000001

# rm /var/lib/mysql/aria_log_control

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

# systemctl status mariadb

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

# mysql -uroot -ppassword < /root/zabbix.sql

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

# mysql_upgrade -uroot -ppassword --force

Перезапускаем mariadb.

# systemctl restart mariadb

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

# systemctl start zabbix-server

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

Заключение

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