Оптимизация базы данных MySQL для Zabbix

Всем привет! В предыдущей статье мы с вами установили наш сервер мониторинга Zabbix на Ubuntu Server 14.04. В моем случае это физический сервер со следующими довольно скромными характеристиками:


Версия ОС: Ubuntu Server 14.04.3 LTS x64
Версия Zabbix: 2.4.6

Платформа: Supermicro
Процессор: Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
Память: 4G
Дисковая подсистема: 2x2Tb SATA Raid1
Файловая система: Ext4

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

1. Система мониторинга Zabbix - Оптимизация базы данных MySQL
2. Performance Tuning for Zabbix
3. MySQL performance tips for Zabbix
4. Масштабируя Zabbix
5. Что нужно настроить в mySQL сразу после установки?
6. Настройка MySQL
7. Оптимизация MySQL

Кратко об основных рекомендациях.

• Используйте мощный сервер, если у вас много устройств для мониторинга (это мне не подходит)
• Используйте по одному innodb-файлу на таблицу
• Используйте с умом выбранные параметры для InnoDB
• Используйте Percona Server
• Используйте файловую систему tmpfs для временных файлов

Итак, начинаем. Первым делом обновим систему.

apt-get update
apt-get upgrade
apt-get dist-upgrade
apt-get autoremove

Используем файловую систему tmpfs для временных файлов в MySQL. Создаем точку монтирования.

mkdir /tmp/mysqltmp

Добавляем в файл /etc/fstab строку

tmpfs /tmp/mysqltmp tmpfs rw,uid=mysql,gid=mysql,size=384M,nr_inodes=10k,mode=0700 0 0

Перезагружаем для проверки работоспособности.

reboot

Стопим Zabbix, Apache и все, что использует MySQL на сервере.

service apache2 stop
/etc/init.d/zabbix-server stop

Создаем полный дамп базы zabbix.

mysqldump -u root -p zabbix > zabbix.sql

Подключаемся к MySQL и удаляем базу zabbix.

mysql -u root -p
mysql> drop database zabbix;
mysql> quit;

Останавливаем MySQL.

service mysql stop

Удаляем следующие файлы InnoDB.

rm /var/lib/mysql/ibdata*
rm /var/lib/mysql/ib_logfile*

Приведу сразу мой готовый файл настроек /etc/mysql/my.cnf. Сразу скажу, что он не эталон, ибо конфигурации серверов у всех разные, и будет меняться в процессе эксплуатации сервера. Скажем так, это стартовый конфиг для дальнейшей работы.

[client]
port                            = 3306
socket                          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                          = /var/run/mysqld/mysqld.sock
nice                            = 0

[mysqld]
# path
datadir                         = /var/lib/mysql
tmpdir                          = /tmp/mysqltmp
pid-file                        = /var/run/mysqld/mysqld.pid
socket                          = /var/run/mysqld/mysqld.sock
basedir                         = /usr
lc-messages-dir                 = /usr/share/mysql
# network
connect_timeout                 = 60
wait_timeout                    = 28800
max_connections                 = 300
max_allowed_packet              = 64M
max_connect_errors              = 1000
# limits
tmp_table_size                  = 256M
max_heap_table_size             = 256M
table_open_cache                = 1024
# logs
log_error                       = /var/log/mysql/mysql-error.log
slow_query_log_file             = /var/log/mysql/mysql-slow.log
slow_query_log                  = 1
long_query_time                 = 5
# innodb
innodb_data_file_path           = ibdata1:128M;ibdata2:128M:autoextend:max:4096M
innodb_file_per_table           = 1
innodb_status_file              = 1
innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size         = 2G
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 2000
innodb_flush_log_at_trx_commit  = 2
innodb_support_xa               = 0
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
# other stuff
user                            = mysql
port                            = 3306
skip-external-locking
bind-address                    = 127.0.0.1
event_scheduler                 = 1
query_cache_type                = 0
join_buffer_size                = 256K
read_buffer_size                = 256K
read_rnd_buffer_size            = 256K
sort_buffer_size                = 256K
key_buffer_size                 = 16M
thread_stack                    = 192K
thread_cache_size               = 8
myisam-recover-options          = BACKUP
query_cache_limit               = 2M
query_cache_size                = 128M
expire_logs_days                = 10
max_binlog_size                 = 100M

[mysqldump]
quick
quote-names
max_allowed_packet              = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer                      = 16M

Секции [client] [mysqld_safe] [mysqldump] [mysql] [isamchk] стандартные. Все настройки произведены для секции [mysqld].
Приведу описание наиболее важных параметров:
max_connections - максимальное количество параллельных соединений к серверу. Увеличьте его, если сталкиваетесь с проблемой «Too many connections». Default - 100.
max_allowed_packet - максимальный размер данных, которые могут быть переданы за один запрос. Следует увеличить, если столкнетесь с ошибкой «Packet too large».
tmp_table_size - если размер временной превышает размер, установленный этой переменной, она сбрасывается на диск, при наличии достаточного количества памяти на сервере, рекомендуется повысить значение данной переменной, по ускорения запросов с конструкцией GROUP BY.
max_heap_table_size - данная переменная устанавливает максимальный размер таблиц типа MEMORY ( HEAP ), изменение значения этой переменной никак не отражается на уже существующих в памяти таблицах и влияет только на те, которые будут созданы заново.
innodb_file_per_table - храним каждую таблицу в отдельном файле.
innodb_additional_mem_pool_size — данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.
innodb_buffer_pool_size — важная настройка для InnoDB, обязательно обратите на неё внимание, если собираетесь использовать в основном InnoDB-таблицы, т.к. они значительно более чувствительны к размеру буфера, чем MyISAM-таблицы. Общая рекомендация по данному параметру - 70-80% от объема оперативной памяти, но т.к. у меня всего 4G, то я выбрал значение 2G
innodb_log_file_size - крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах;большие размеры увеличивают быстродействие, однако будьте осторожны — увеличится и время восстановления данных.
innodb_log_buffer_size - стандартное значение данной опции вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями.

Запускаем MySQL.

service mysql start

Смотрим логи.

cat /var/log/mysql/mysql-error.log

Восстанавливаем базу zabbix.

mysql -u root -p
mysql> create database zabbix;
mysql> quit;
mysql -u root -p zabbix < zabbix.sql

Смотрим, создались ли файлы *.frm и *.idb для таблиц базы.

root@zabbix1:/home/sysop# ls -lh /var/lib/mysql/zabbix/
total 24M
-rw-rw---- 1 mysql mysql 8.6K Nov 11 18:15 acknowledges.frm
-rw-rw---- 1 mysql mysql 144K Nov 11 18:15 acknowledges.ibd
-rw-rw---- 1 mysql mysql  13K Nov 11 18:15 actions.frm
-rw-rw---- 1 mysql mysql 128K Nov 11 18:15 actions.ibd

Запускаем обратно Zabbix и Apache.

/etc/init.d/apache2 start
/etc/init.d/zabbix-server start

В процессе эксплуатации необходимо наблюдать, как будет работать система и вносить требуемые коррективы.
Продолжение следует...