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

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

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

Если у вас есть желание научиться администрировать системы на базе Linux, рекомендую познакомиться с онлайн-курсом «Linux для начинающих» в OTUS. Курс для новичков, для тех, кто с Linux не знаком. Подробная информация.

Введение

В своей инструкции по установке и настройке 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

Сервер запустился. Есть пару замечаний, мы их позже исправим. Теперь восстанавливаем базу данных 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. Ничего сложного в этом нет, примеров в сети много, но лично я сам не настраивал никогда, не было необходимости.

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

Анонсы всех статей, плюс много другой полезной и интересной информации, которая не попадает на сайт.

 

Если у вас есть желание научиться администрировать системы на базе Linux, но вы с ними никогда не работали и не знакомы, то рекомендую начать с онлайн-курса «Linux для начинающих» в OTUS. Курс для новичков, для тех, кто с Linux не знаком. Цена за курс минимальная (символическая). Информация о курсе и цене.

Автор Zerox

Владимир, системный администратор, автор сайта. Люблю настраивать сервера, изучать что-то новое, делиться знаниями, писать интересные и полезные статьи. Открыт к диалогу и сотрудничеству. Если вам интересно узнать обо мне побольше, то можете послушать интервью. Запись на моем канале - https://t.me/srv_admin/425 или на сайте в контактах.

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

  1. Михаил

    Автору спасибо большое за статью, я в силу своей неопытности, не понимаю, что даёт разделение одного большого файла базы данных, на много маленьких (кроме history.ibd и history_uint.ibd, эти совсем не маленькие). Точнее понимаю что в дальнейшем если что-то произойдёт с одним из *.ibd файлов, его как-то проще восстановить или создать новый, не теряя при этом все остальные данные (но это исключительно моё предположение). Больше интересно как теперь сжимать базу когда она состоит из 176 файлов *.ibd?

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

      А сжимаются базы данных независимо от того, как они хранятся в файлах, так как это делает сама СУБД с помощью передаваемых к ней команд.

      • Михаил

        Ок, спасибо, вроде понятно.
        Вопрос по поводу очищения данных в базе. У меня база на диске занимает 329 Gb, разделена на файлы:

        178,4 GiB history_uint.ibd
        131,7 GiB history.ibd
        13,6 GiB history_str.ibd
        2,5 GiB history_text.ibd
        1,4 GiB trends_uint.ibd
        816,0 MiB trends.ibd

        Остальные мелкие, я правильно понимаю если я выполню команды типа
        DELETE FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

        То, после удаления части не нужных данных, файл history_uint.ibd станет меньшего размера, или же размер самого файла останется тот же (хоть и данных в нём будет меньше)?

        • Точно не помню, освободится ли место после удаления. Скорее всего придётся ещё выполнить оптимизацию и сжатие, чтобы точно высвободилось незанимаемое место.

  2. Дмитри

    Добрый день. Не могу удалить базу данных "performance_schema" выдает ошибку.
    ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'performance_schema'
    Подскажите пожалуйса, как победить.

    • А зачем её удалять? Это служебная база.

      • Дмитри

        Может я чтото не правильно понял. У вас написано:
        > show databases;
        +--------------------+
        | Database |
        +--------------------+
        | information_schema |
        | mysql |
        | performance_schema |
        | zabbix |
        +--------------------+
        > drop database zabbix;
        > drop database performance_schema;
        Для очистки.
        С линуксом очень плохо знаком. Если не сложно, подскажите пожалуйста.

        • Если честно, уже не помню в контексте чистки базы данных Zabbix, зачем я и performance_schema удалял. Там хранится служебная информация по статистике mysql сервера. Непосредственно на размер базы данных Zabbix это не влияет. У вас конкретно юзер 'root'@'localhost' почему-то не имеет доступа к этой базе. Это обычное дело, надо просто список пользователей и права доступа к базе посмотреть. Возможно там доступ сделан для пользователя 'root'@'127.0.0.1' или как-то еще. По сути это одно и то же, но для mysql сервера разные пользователи. Если не получится разобраться, то просто оставьте эту базу данных без изменений.

  3. Алекскндр

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

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

    ### MySQL Server Login Info ###
    MUSER=" "
    MPASS=" "
    MDB=" "
    time="1625011200"
    copy="_copy"
    TABLES_INNODB="$(mysql -u$MUSER -p$MPASS -h127.0.0.1 -Bse "select table_name from information_schema.tables where table_schema = '$MDB' and engine = 'InnoDB'")"
    ### Job ###
    
    mkdir -p /root/full/
    cd /root/full
    
    #InnoDB Dump
    for table_i in $TABLES_INNODB
     do
      if [[ $table_i == "history"* ]] || [[ $table_i == "trends"* ]]
    	then
    		echo "Create table as $table_i started $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
    			mysql -u$MUSER -p$MPASS -h127.0.0.1 $MDB -Bse "CREATE TABLE $table_i$copy LIKE $table_i;"
    		echo "Create table as $table_i ended $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
    		echo "Clone as $table_i started $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
    			mysql -u$MUSER -p$MPASS -h127.0.0.1 $MDB -Bse "INSERT INTO $table_i$copy SELECT * FROM $table_i WHERE clock > $time;"
    		echo "Clone table $table_i ended $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
    		echo "Drop table $table_i started $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
    			mysql -u$MUSER -p$MPASS -h127.0.0.1 $MDB -Bse "DROP TABLE $table_i;"
    		echo "Drop table $table_i ended $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
    		echo "Rename table $table_i started $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
    			mysql -u$MUSER -p$MPASS -h127.0.0.1 $MDB -Bse "RENAME TABLE $table_i$copy TO $table_i;"
    		echo "Rename table $table_i ended $(date +"%d %B %Y") at $(date +"%H:%M:%S")" 
    		echo "Dump $table_i started $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
    			mysqldump -u$MUSER -p$MPASS -h127.0.0.1 $MDB --skip-lock-tables --single-transaction --quick --tables $table_i | gzip -9 > $table_i.sql.gz
    		echo "Dump $table_i ended $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
    	else
    		echo "Dump $table_i started $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
    			mysqldump -u$MUSER -p$MPASS -h127.0.0.1 $MDB --skip-lock-tables --single-transaction --quick --tables $table_i | gzip -9 > $table_i.sql.gz
    		echo "Dump $table_i ended $(date +"%d %B %Y") at $(date +"%H:%M:%S")"
      fi
     done

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

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

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

    • Михаил

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

  4. Михаил

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

    Я прождал много времени ожидания пока выполнятся 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 часов, разворачивать будет сутки - вообще не вариант.

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

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

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

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

    innodb_file_per_table=1

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

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

  6. у меня не получилось( вроде бы все прошло гладко, но забикс стартовал с ошибкой. при входе настойчиво просил авторизацию локальную. а т.к. я сделал доменку то локальные учетные данные я забыл. ну в целом что то получилось... версия забикса 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 ничего не трогаю.

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

  7. Делаю всё по инструкции.
    На шаге 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 делать не надо, будут как раз подобные ошибки. Попробуйте просто запустить базу. Если в логе ошибок не будет, то ничего и делать не надо.

    • Второй вариант - не удалили ibdata файлы.

  8. Сергей

    Бэкап БД очень долгий, есть ли способ ускорить?

    • Общее быстродействие сервера надо увеличивать. Время дампа напрямую зависит от производительности сервера. Для того, чтобы таблицы не блокировались во время дампа, можно использовать ключ --single-transaction в mysqldump.

  9. Аноним

    Сегодня обновился по статье, все отлично. Правда ставил уже 10.3.13 Спасибо большое!!!

  10. Аноним

    Проблема у человека выше возникла скорее всего из за того. что запрос на очистку был на выборку itemid с status=0 а например в 3.х отключенные это status=1, а активные наоборот status=0. Так что пацанчик скорее всего снес к праотцам все итемы активные. Благо я просто сделал запрос и сравнил вывод с веб интерфейсом. Копипаста зло, думайте башкой люди, а то однострочник на перл сделает ваши волосы кучерявыми.

  11. Добрый день, в чем преимущество апгрейда с 5.5 до 10 версии мускла?

    • Я не проверял. Есть шанс, что будет работать быстрее. По крайней мере к этому стремятся разработчики.

  12. Всем доброго дня, если решите ставить обновлять MariaDB, то начиная с версии 10.3.* помните, что innodb_file_format удалён. В следствии чего сервер БД не стартанёт.

  13. Сергей

    На версии 3.4 веб морда перестала работать после всех манипуляций. База запущена, коннект есть. Заббикс отправляет оповещения в телеграмм. Не подскажите куда копать в плане веб интерфейса?

    • Тут вообще веб интерфейс ни коим образом не трогается. Вспоминайте, что делали. Максимум можно сломать базу, но веб интерфейс будет работать и напишет, что нет коннекта к серверу.

      • Сергей

        Действительно моя ошибка. Не стартовал httpd, так как я снес папку и лог файл.
        Теперь все работает!
        Спасибо Вам за такой отличный сайт!

  14. В статье перед удалением старых файлов БД

    # rm /var/lib/mysql/ibdata1
    # rm /var/lib/mysql/ib_logfile0
    # rm /var/lib/mysql/ib_logfile1

    саму базу данных забыли остановить ("systemctl stop mariadb")

  15. Александр

    Для СУБД MySQL/MariaDB есть более изящный способ сжатия/уменьшения размера датафайлов, но только при условии, что система настроена на создание отдельных датафайлов для каждой таблицы, а не одного большого файла ibdata1 для всей базы. Данную задачу можно решить используя команду:
    optimize table имя_таблицы;
    Данная команда производит дефрагментацию указанной таблицы. В процессе оптимизации, MySQL/MariaDB создает временный файл для таблицы, и после оптимизации удаляет исходный файл таблицы, одновременно переименовывая временный файл таблицы в исходный файл таблицы. В новом файле сохраняются только данные, все пустые строки отбрасываются. В результате датафайл таблицы значительно сокращается в размере.
    Ниже приводится детальное описание всего процесса на примере самых крупных таблиц БД системы Zabbix

    --Что нужно для сжатия датафайлов таблиц в СУБД MySQL/MariaDB
    --тип движка базы данных (storage engine) должен быть InnoDB
    show variables like 'storage_engine'\G;
    *************************** 1. row ***************************
    Variable_name: storage_engine
    Value: InnoDB
    1 row in set (0.00 sec)

    --параметр innodb_file_per_table должен быть включен
    show variables like 'innodb_file_per_table'\G;
    *************************** 1. row ***************************
    Variable_name: innodb_file_per_table
    Value: ON
    1 row in set (0.00 sec)

    --Проверяем текущие размеры датафайлов таблиц БД Zabbix на файловой системе
    ls -lah /var/lib/mysql/zabbix/history*
    ls -lah /var/lib/mysql/zabbix/trends*

    --Подключаемся к MySQL/MariaDB
    mysql -u root -p

    --Выбираем БД zabbix
    use zabbix;

    --Просматриваем поля таблицы history_uint
    desc history_uint;

    --Просматриваем даты 5 последних строк в таблице history_uint старше 7 дней
    select FROM_UNIXTIME(clock) from history_uint WHERE FROM_UNIXTIME(clock) < DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY itemid DESC LIMIT 5;

    --Определяем общее количество строк в таблице history_uint
    select count(*) from history_uint;

    --Определяем количество строк в таблице history_uint старше 7 дней
    select count(*) from history_uint WHERE FROM_UNIXTIME(clock) < DATE_SUB(NOW(), INTERVAL 7 DAY);

    --Удаляем все записи старше 7 дней в таблице history_uint
    delete from history_uint WHERE FROM_UNIXTIME(clock) < DATE_SUB(NOW(), INTERVAL 7 DAY);

    --Сохраняем изменения
    commit;

    --Производим оптимизацию таблицы history_uint путем дефрагментации данных. В процессе оптимизации, MySQL/MariaDB создает временный файл для таблицы, и после оптимизации удаляет исходный файл таблицы, одновременно переименовывая временный файл таблицы в исходный файл таблицы
    optimize table history_uint;

    --В результате мы увидим сообщение:
    +---------------------+----------+----------+-------------------------------------------------------------------+
    | Table | Op | Msg_type | Msg_text |
    +---------------------+----------+----------+-------------------------------------------------------------------+
    | zabbix.history_uint | optimize | note | Table does not support optimize, doing recreate + analyze instead |
    | zabbix.history_uint | optimize | status | OK |
    +---------------------+----------+----------+-------------------------------------------------------------------+
    2 rows in set (8.53 sec)

    --Проверяем процесс формирования нового датафайла в процессе оптимизации таблицы history_uint
    ls -lah /var/lib/mysql/zabbix/#*

    --Определяем общее количество строк в таблице history_uint после удаления записей
    select count(*) from history_uint;

    --Аналогичные действия выполняем и для таблиц trends
    --Просматриваем поля таблицы trends_uint
    desc trends_uint;

    --Просматриваем даты 5 последних строк в таблице history_uint старше 30 дней
    select FROM_UNIXTIME(clock) from trends_uint WHERE FROM_UNIXTIME(clock) < DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY itemid DESC LIMIT 5;

    --Определяем общее количество строк в таблице trends_uint
    select count(*) from trends_uint;

    --Определяем количество строк в таблице trends_uint старше 30 дней
    select count(*) from trends_uint WHERE FROM_UNIXTIME(clock) < DATE_SUB(NOW(), INTERVAL 30 DAY);

    --Удаляем все записи старше 30 дней в таблице history_uint
    DELETE FROM trends_uint WHERE FROM_UNIXTIME(clock) < DATE_SUB(NOW(), INTERVAL 30 DAY);

    --Сохраняем изменения
    commit;

    --Производим оптимизацию таблицы trends_uint путем дефрагментации данных. В процессе оптимизации, MySQL/MariaDB создает временный файл для таблицы, и после оптимизации удаляет исходный файл таблицы, одновременно переименовывая временный файл таблицы в исходный файл таблицы
    optimize table trends_uint;

    --В результате мы увидим сообщение:
    +---------------------+----------+----------+-------------------------------------------------------------------+
    | Table | Op | Msg_type | Msg_text |
    +---------------------+----------+----------+-------------------------------------------------------------------+
    | zabbix.trends_uint | optimize | note | Table does not support optimize, doing recreate + analyze instead |
    | zabbix.trends_uint | optimize | status | OK |
    +---------------------+----------+----------+-------------------------------------------------------------------+
    2 rows in set (8.53 sec)

    --Проверяем процесс формирования нового датафайла в процессе оптимизации таблицы trends_uint
    ls -lah /var/lib/mysql/zabbix/#*

    --Определяем общее количество строк в таблице trends_uint после удаления записей
    select count(*) from trends_uint;

  16. Ошибка:
    Вместо
    # rm /var/log/mysql/aria_log.00000001
    # rm /var/log/mysql/aria_log_control
    Нужно
    # rm /var/lib/mysql/aria_log.00000001
    # rm /var/lib/mysql/aria_log_control

    • Спасибо, существенная поправка. Явно никто не пытался повторить :)

      • Я повторил и все получилось как описано.
        Никаких ошибок.
        Единственное вся база у меня была на CHARSET=latin1_swedish (установка не моя была)
        Чтобы сменить кодировку воспользовался способом который описан здесь https://saradmin.ru/?p=1508
        Если считаете что стоит включить что-то про кодировку то было бы не плохо.

  17. Отличная статья! Спасибо автору!
    Только Zabbix 3.4 после такой чистки умер.

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

      • Из бэкапов восстановил. Но сам факт что после такой чистки веб морда заббикса отказывалась работать - не загружалась выдавая ошибку на работу с базой.

        • Так может банально база не стартанула из-за ошибки в конфиге? Или импорт базы после удаления не отработал нормально. Все, что делается с базой, это очистка таблиц с историей и трендами. Максимум, что можно потерять, это данные в этих таблицах. На работу самого сервера заббикса это никак не может повлиять. Команды все простые, их полный текст приведен. Можно убедиться в том, что они делают. Так что причина явно в чем-то другом.

          • База стартовала это 100%. Проверял. Коннект к ней тоже не нарушался - кофиги никто не менял. ИМХО: надо потестить на нескольких версиях Заббикса этот метод. Как будет время попробую провернуть это дело.

  18. Доброе.
    Хорошая статья. А почему не использовать postgresql ? У него и VACUUM есть. Причем автоматически.
    https://lob.com/blog/supercharge-your-postgresql-performance
    http://www.vertabelo.com/blog/technical-articles/dirty-postgresql-database-clean-it-up-with-a-vacuum

    • Я нигде не видел тестов производительности заббикса на mysql и postgres. Достоверно мне не известно, что postgres лучше, чем mysql в данном случае. Если есть какие-то тесты на эту тему, было бы любопытно посмотреть. То, что видел я в нагруженных конфигурациях заббикс, чаще это партицирование на mysql, нежели postgres. Mysql лично мне просто привычнее. Хотя с postgres я тоже много работаю, но в связке с 1С.

  19. Андрей

    данный конфиг сколько узлов держит в жабиксе и и сколько новый в значений в секунды у забикса?

  20. Андрей

    зачем занижать кол подключений, по сравнению с по умолчанию
    max_connections = 100
    по умолчанию 150
    обычно увеличивают чтобы можно запустить больше пулеров и увеличивать как раз бытродействие

    • Даже 100 подключений мне хватает за глаза, поэтому стоит такое значение. 100 поллеров это очень большая сеть, сотни хостов. У меня таких нет.

  21. Андрей

    log-bin=mysql-bin
    binlog_format=mixed
    server-id = 1
    вот три параметра из Вашего конфига которые косвенно свидетельствую что вы намерены делать репликацию
    а так же
    innodb_flush_method=O_DSYNC опять же косвенно говорит что включение создания bin файлов
    так как из них можно всегда восстановить базу на определенный момент времени,а использовать O_DSYNC без регулярного бекапа не рекомендуется.
    Хотелось из статьи еще понять почему вы начали делать оптимизацию mysql, что в zabbix стало работать хуже...было так сделал это получил то то

    • В данном случае непосредственно к репликации относится только параметр server-id, он остался по старой памяти. Иногда я настраиваю репликацию. А бинарные логи и без репликации нужны. Хранить их или нет вопрос индивидуальный. Насколько я понимаю, они немного снижают производительность, но позволяют иногда восстановить базу после аварийных выключений. Я предпочитаю их оставлять.

      Бэкапы в любом случае делаются регулярно, об этом я даже не упоминаю.

  22. Андрей

    Судя по настройкам mysql включена репликация, зачем?

    • Не понял, из чего сделан такой вывод? Репликация запускается на втором сервере, настраивать надо его и руками включать репликацию. В данном случае у меня только один сервер.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Нажимая кнопку "Отправить комментарий" Я даю согласие на обработку персональных данных.
Используешь Telegram? Подпишись на канал автора →
This is default text for notification bar