Диагностика и оптимизация Zabbix: ошибки БД, зависания и очистка

mysql -u zabbix -p zabbix -e "
SELECT 
    table_name AS 'Таблица',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Размер (MB)',
    table_rows AS 'Количество записей',
    ROUND((data_length / 1024 / 1024), 2) AS 'Данные (MB)',
    ROUND((index_length / 1024 / 1024), 2) AS 'Индексы (MB)'
FROM information_schema.TABLES 
WHERE table_schema = 'zabbix'
ORDER BY (data_length + index_length) DESC
LIMIT 20;"

mysql -u zabbix -p zabbix -e "
SELECT 
    'history_uint' AS table_name,
    MIN(FROM_UNIXTIME(clock)) AS oldest_record,
    MAX(FROM_UNIXTIME(clock)) AS newest_record,
    COUNT(*) AS total_records,
    DATEDIFF(NOW(), FROM_UNIXTIME(MIN(clock))) AS days_span
FROM history_uint
UNION ALL
SELECT 
    'history',
    MIN(FROM_UNIXTIME(clock)),
    MAX(FROM_UNIXTIME(clock)),
    COUNT(*),
    DATEDIFF(NOW(), FROM_UNIXTIME(MIN(clock)))
FROM history
UNION ALL
SELECT 
    'trends',
    MIN(FROM_UNIXTIME(clock)),
    MAX(FROM_UNIXTIME(clock)),
    COUNT(*),
    DATEDIFF(NOW(), FROM_UNIXTIME(MIN(clock)))
FROM trends;"

mysql -u zabbix -p zabbix -e "
SELECT 
    i.itemid,
    i.name AS 'Элемент',
    h.name AS 'Хост',
    i.key_ AS 'Ключ',
    i.delay AS 'Интервал (сек)',
    COUNT(*) AS 'Записей за сутки',
    ROUND(COUNT(*) / (24 * 3600 / i.delay), 2) AS 'Эффективность %'
FROM history his
JOIN items i ON his.itemid = i.itemid
JOIN hosts h ON i.hostid = h.hostid
WHERE his.clock > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
GROUP BY i.itemid, i.name, h.name, i.key_, i.delay
HAVING COUNT(*) > 10000
ORDER BY COUNT(*) DESC
LIMIT 10;"

mysql -u zabbix -p zabbix -e "
SELECT 
    table_name,
    ROUND((data_free / 1024 / 1024), 2) AS fragmented_mb,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS total_mb,
    ROUND((data_free * 100 / (data_length + index_length + data_free)), 2) AS fragmentation_percent
FROM information_schema.TABLES 
WHERE table_schema = 'zabbix' 
    AND table_name LIKE 'history%'
    AND data_free > 0
ORDER BY fragmentation_percent DESC;"

mysql -u zabbix -p zabbix -e "OPTIMIZE TABLE history, history_uint, history_text, history_str;"

mysql -u zabbix -p zabbix -e "
DELETE FROM history_uint 
WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
LIMIT 10000;"

#!/bin/bash
DB_USER="zabbix"
DB_PASS="ваш_пароль"
DB_NAME="zabbix"
DAYS_TO_KEEP=30
BATCH_SIZE=10000

for TABLE in history_uint history history_text history_str; do
    echo "Очистка $TABLE..."
    while true; do
        DELETED=$(mysql -u$DB_USER -p$DB_PASS $DB_NAME -s -N -e \
            "DELETE FROM $TABLE WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL $DAYS_TO_KEEP DAY)) LIMIT $BATCH_SIZE; SELECT ROW_COUNT();")
        echo "Удалено записей из $TABLE: $DELETED"
        if [ "$DELETED" -eq "0" ]; then
            break
        fi
        sleep 1
    done
done

echo "Оптимизация таблиц..."
mysql -u$DB_USER -p$DB_PASS $DB_NAME -e "OPTIMIZE TABLE history_uint, history, history_text, history_str;"
echo "Очистка завершена!"

sudo crontab -e

Добавьте строку:

0 2 * * 0 /usr/local/bin/clean_zabbix_history.sh >> /var/log/zabbix_clean.log 2>&1

#!/bin/bash
echo "=== Zabbix Health Check ==="
echo "1. Services status:"
systemctl is-active mariadb && echo "MariaDB: OK" || echo "MariaDB: FAILED"
systemctl is-active zabbix-server && echo "Zabbix Server: OK" || echo "Zabbix Server: FAILED"

echo -e "\n2. Database connections:"
mysql -u zabbix -pваш_пароль -e "SHOW PROCESSLIST;" | grep zabbix | wc -l

echo -e "\n3. Database size:"
mysql -u zabbix -pваш_пароль zabbix -s -N -e "
SELECT 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as 'Total DB Size (MB)'
FROM information_schema.TABLES 
WHERE table_schema = 'zabbix';"

echo -e "\n4. Data ingestion rate (last hour):"
mysql -u zabbix -pваш_пароль zabbix -s -N -e "
SELECT 
    (SELECT COUNT(*) FROM history WHERE clock > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 HOUR))) as history_last_hour,
    (SELECT COUNT(*) FROM history_uint WHERE clock > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 HOUR))) as history_uint_last_hour;"

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Добавьте:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

sudo mysqldumpslow /var/log/mysql/slow.log | head -20

sudo systemctl edit mariadb

Вставьте:

[Service]
Restart=on-failure
RestartSec=10

#!/bin/bash
THRESHOLD=90
USAGE=$(df /var/lib/mysql | awk 'NR==2 {print $5}' | sed 's/%//')

if [ $USAGE -gt $THRESHOLD ]; then
    echo "WARNING: Disk usage is ${USAGE}%"
    echo "Cleaning old Zabbix data..."
    # Автоматически запускаем очистку
    /usr/local/bin/clean_zabbix_history.sh
fi