Настройки MySQL для работы с Zabbix 7.0 LTS

"В этой статье рассказываю параметрах настройки СУБД MySQL, чтобы Zabbix работал хорошо"

Александр Романюк

автор, инженер-проектировщик систем мониторинга

На днях мы проводили опрос в нашем телеграм-канале, где уточняли какую базу данных используют подписчики для своих инсталляций. Примерно четверть ответили, что это MySQL-подобная БД.

В этой статье мы собрали рекомендации по настройке параметров производительности MySQL с рекомендациями по настройке. Вы можете их сравнить со своими и проверить как изменится производительность БД после их применения. Вперед!

Параметры конфигурации MySQL

Проблемы, которые чаще всего возникают обычно связаны с disk IO. Поэтому для начала рассмотрим опции тюнинга производительности именно с этого поинта. Первое, на что нужно посмотреть — тип диска, который у вас задействован под хранение дата-файлов. Использование HDD не совсем хороший кейс, поэтому поскорее рассмотрите его замену на что-то более быстрое, например, SSD. А теперь перейдем к рассмотрению настроек в my.cnf.

— innodb_buffer_pool_size – размер буфера. Если у вас выделенный сервер MySQL, установите его как можно более высоким (потолок — 75% от общего объема доступной RAM). Случай, когда на сервере БД находиться что-то ещё мы не рассматривает, т.к. это нерабочий кейс для высоконагруженных систем.

— innodb_buffer_pool_instances — количество экземпляров буфера. Максимальное значение 64, рекомендуем начать с 16.

— innodb_flush_log_at_trx_commit — стратегия сброса данных на диск при работе MySQL. Значение “0” даст наибольшую производительность.

– innodb_flush_method — определяет метод сброса данных из памяти на диск. Выбор подходящего значения – это выбор между скоростью и надежностью. Поставьте значение O_DIRECT.

– innodb_log_file_size —параметр определяет размер redo лога MySQL. Чем больше размер этого лога, тем меньше дисковых операций с данными делает MySQL. А значит увеличение размера этого файла приводит к росту производительности операций записи. Рекомендуемое значение 1G. После изменения этого параметра, необходимо перезапустить MySQL и очистить текущие логи.

— innodb_read_io_threads, innodb_write_io_threads — количество потоков ввода/вывода файлов. Не переусердствуйте с этими показателями, просто установите эти значения на 8 и оставьте так.

— innodb_old_blocks_time — время хранения в памяти старых блоков данных. Установите 1000 — это поможет вам предотвратить засорение буферного пула из-за случайных сканирований.

— innodb_io_capacity – количество IOPS. Можно установить для этого параметра столько операций записи, сколько может выдержатьдисковая подсистема. Для твердотельных накопителей это число должно составлять не менее нескольких тысяч (хорошим началом может быть 2000) Лучше всего протестировать диски или выполнить математические расчеты для реальных чисел, но значение по умолчанию, равное 200, слишком мало для большинства современных систем.

— sync_binlog=0 — это настройка синхронизации двоичных журналов, но на всякий случай отключите ее, если только не запускаете на сервере что-то еще, кроме Zabbix.

— query_cache_size=0, query_cache_type=0 — это отключит кэш запросов. В большинстве случаев кэш запросов не нужен.

— sort_buffer_size, join_buffer_size, read_rnd_buffer_size — удалите эти параметры или закомментируйте. Параметры добавляют дополнительный функционал работы с буфером, который никак не улучшит производительность.

— innodb_file_per_table=1 — установите значение в 1, чтобы СУБД хранила каждую таблицу в отдельном файле.

Помимо перечисленной конфигурации важно настроить партиционирование таблиц:


# mysql -p
# MariaDB [(none)]> use zabbix;
# CREATE EVENT Partitions ON SCHEDULE EVERY 1 DAY DO CALL partition_maintenance_all('zabbix');

Пример конфигурации

Приведем конфигурацию MySQL для 40 000 значений в секунду на базе виртуального сервера со следующими аппаратными характеристиками: 16cpu, 64 GB RAM, 400 GB SAS HDD.


open_files_limit 		= 16364
max_connections                 = 500
event_scheduler                 = 1
wait_timeout 			= 86400
default_storage_engine		= InnoDB

log_error                       = /var/log/mariadb/mariadb-error.log
slow_query_log_file             = /var/log/mariadb/mariadb-slow.log
general_log_file                = /var/log/mariadb/mariadb.log
general_log                     = 0
skip-name-resolve		= 1
performance_schema		= ON

innodb_autoinc_lock_mode	= 2
innodb_flush_log_at_trx_commit	= 0
innodb_autoextend_increment 	= 256
innodb_buffer_pool_instances 	= 12
innodb_buffer_pool_size 	= 48G
innodb_change_buffer_max_size 	= 50
innodb_concurrency_tickets 	= 5000
innodb_file_per_table 		= 1
innodb_flush_method 		= O_DIRECT
innodb_log_file_size 		= 512M
innodb_log_files_in_group 	= 4
innodb_old_blocks_time 		= 1000
innodb_open_files 		= 2048
innodb_stats_on_metadata 	= OFF
innodb_lock_wait_timeout 	= 50
innodb_io_capacity 		= 2000

large-pages
binlog-row-event-max-size 	= 8192
character_set_server 		= utf8
collation_server 		= utf8_bin
expire_logs_days 		= 1
join_buffer_size 		= 262144
max_allowed_packet 		= 32M
query_cache_type 		= 0
query_cache_size 		= 0
slow-query-log 			= ON
table_open_cache 		= 2048
thread_cache_size 		= 64
tmp_table_size 			= 134217728
thread_pool_size		= 12

# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0

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

Что дальше

Приглашаем наши тренинги по Zabbix, OpenSearch, ElasticSearch

Максимум знаний за короткое время

Посмотреть расписание