Fixing Slow Zabbix DB: Practical MySQL Partition Automation (Part 4)

— Automating Daily and Monthly Partitions with Cron and Shell Scripts

Overview

This article explains how to automatically maintain and manage the partition structure created in Part 3.
Because Zabbix collects a massive amount of data, manually adding or dropping partitions every day or month is inefficient and error-prone.

Here, we implement full automation using two maintenance scripts:

• /usr/local/bin/zbx-part-history.sh — creates daily (history) partitions and drops data older than 90 days.
• /usr/local/bin/zbx-part-trends.sh — creates monthly (trends) partitions and drops data older than 12 months.

1) Prerequisites

• Steps 1 – 3 completed (partition structure applied for history and trends)
• MySQL user: zabbix
• /data/history and /data/trends directories exist
• All operations run in UTC (the cron timezone must be fixed to UTC)

2) Daily Automation Script for History

File path: /usr/local/bin/zbx-part-history.sh

#!/usr/bin/env bash
set -euo pipefail

# ===== Basic Settings =====
DB="zabbix"
DB_USER="zabbix"
DB_PASS="your_database_password_here"
DB_HOST="127.0.0.1"
TARGET_DIR="/data/history"
DAYS_KEEP=90   # Retention in days

MYSQL() {
  mysql -u"${DB_USER}" -p"${DB_PASS}" -h "${DB_HOST}" --protocol=TCP "${DB}" -Nse "$@" 2>/dev/null
}

# ===== Calculate Today's UTC Boundary =====
TODAY_UTC=$(date -u +%Y-%m-%d)
CURRENT_EPOCH=$(date -u -d "${TODAY_UTC} 00:00:00" +%s)
NEXT_BOUNDARY_EPOCH=$((CURRENT_EPOCH + 86400))   # Next midnight
NEXT_TAG=$(date -u -d "@${CURRENT_EPOCH}" +%Y%m%d)

# ===== Check for Existing Partition =====
EXISTS=$(MYSQL "
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA='${DB}' AND TABLE_NAME='history'
    AND PARTITION_NAME='p${NEXT_TAG}';
")

# ===== Create New Partition if Missing =====
if [[ "${EXISTS:-0}" -eq 0 ]]; then
  MYSQL "
    ALTER TABLE ${DB}.history
    REORGANIZE PARTITION pMAX INTO (
      PARTITION p${NEXT_TAG}
        VALUES LESS THAN (${NEXT_BOUNDARY_EPOCH})
        DATA DIRECTORY='${TARGET_DIR}',
      PARTITION pMAX VALUES LESS THAN (MAXVALUE)
        DATA DIRECTORY='${TARGET_DIR}'
    );
  "
fi

EXISTS_UINT=$(MYSQL "
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA='${DB}' AND TABLE_NAME='history_uint'
    AND PARTITION_NAME='p${NEXT_TAG}';
")

if [[ "${EXISTS_UINT:-0}" -eq 0 ]]; then
  MYSQL "
    ALTER TABLE ${DB}.history_uint
    REORGANIZE PARTITION pMAX INTO (
      PARTITION p${NEXT_TAG}
        VALUES LESS THAN (${NEXT_BOUNDARY_EPOCH})
        DATA DIRECTORY='${TARGET_DIR}',
      PARTITION pMAX VALUES LESS THAN (MAXVALUE)
        DATA DIRECTORY='${TARGET_DIR}'
    );
  "
fi

# ===== Drop Old Partitions Automatically =====
CUTOFF_EPOCH=$(date -u -d "${DAYS_KEEP} days ago 00:00:00" +%s)

TO_DROP=$(MYSQL "
  SELECT GROUP_CONCAT(PARTITION_NAME)
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA='${DB}' AND TABLE_NAME='history'
    AND PARTITION_NAME REGEXP '^p[0-9]{8}$'
    AND PARTITION_DESCRIPTION < ${CUTOFF_EPOCH};
" || true)

if [[ -n "${TO_DROP:-}" && "${TO_DROP}" != "NULL" ]]; then
  MYSQL "ALTER TABLE ${DB}.history DROP PARTITION ${TO_DROP};"
fi

TO_DROP2=$(MYSQL "
  SELECT GROUP_CONCAT(PARTITION_NAME)
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA='${DB}' AND TABLE_NAME='history_uint'
    AND PARTITION_NAME REGEXP '^p[0-9]{8}$'
    AND PARTITION_DESCRIPTION < ${CUTOFF_EPOCH};
" || true)

if [[ -n "${TO_DROP2:-}" && "${TO_DROP2}" != "NULL" ]]; then
  MYSQL "ALTER TABLE ${DB}.history_uint DROP PARTITION ${TO_DROP2};"
fi

Grant execution permission:

# chmod +x /usr/local/bin/zbx-part-history.sh

3) Monthly Automation Script for Trends

File path: /usr/local/bin/zbx-part-trends.sh

#!/usr/bin/env bash
set -euo pipefail

# ===== Basic Settings =====
DB="zabbix"
DB_USER="zabbix"
DB_PASS="your_database_password_here"
DB_HOST="127.0.0.1"
TARGET_DIR="/data/trends"
MONTHS_KEEP=12   # Retention in months

MYSQL() {
  mysql -u"${DB_USER}" -p"${DB_PASS}" -h "${DB_HOST}" --protocol=TCP "${DB}" -Nse "$@"
}

# ===== Calculate Month Boundaries =====
CURRENT_MONTH=$(date -u +%Y-%m-01)
CURRENT_EPOCH=$(date -u -d "${CURRENT_MONTH}" +%s)
NEXT_MONTH_EPOCH=$(date -u -d "${CURRENT_MONTH} +1 month" +%s)
NEXT_TAG=$(date -u -d "${CURRENT_MONTH}" +%Y%m)

# ===== Create New Partition if Missing =====
EXISTS=$(MYSQL "
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA='${DB}' AND TABLE_NAME='trends'
    AND PARTITION_NAME='p${NEXT_TAG}';
")

if [[ "${EXISTS:-0}" -eq 0 ]]; then
  MYSQL "
    ALTER TABLE ${DB}.trends
    REORGANIZE PARTITION pMAX INTO (
      PARTITION p${NEXT_TAG}
        VALUES LESS THAN (${NEXT_MONTH_EPOCH})
        DATA DIRECTORY='${TARGET_DIR}',
      PARTITION pMAX VALUES LESS THAN (MAXVALUE)
        DATA DIRECTORY='${TARGET_DIR}'
    );
  "
fi

EXISTS_UINT=$(MYSQL "
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA='${DB}' AND TABLE_NAME='trends_uint'
    AND PARTITION_NAME='p${NEXT_TAG}';
")

if [[ "${EXISTS_UINT:-0}" -eq 0 ]]; then
  MYSQL "
    ALTER TABLE ${DB}.trends_uint
    REORGANIZE PARTITION pMAX INTO (
      PARTITION p${NEXT_TAG}
        VALUES LESS THAN (${NEXT_MONTH_EPOCH})
        DATA DIRECTORY='${TARGET_DIR}',
      PARTITION pMAX VALUES LESS THAN (MAXVALUE)
        DATA DIRECTORY='${TARGET_DIR}'
    );
  "
fi

# ===== Drop Old Monthly Partitions =====
CUTOFF_EPOCH=$(date -u -d "${CURRENT_MONTH} -${MONTHS_KEEP} months" +%s)

TO_DROP=$(MYSQL "
  SELECT GROUP_CONCAT(PARTITION_NAME)
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA='${DB}' AND TABLE_NAME='trends'
    AND PARTITION_NAME REGEXP '^p[0-9]{6}$'
    AND PARTITION_DESCRIPTION < ${CUTOFF_EPOCH};
" || true)

if [[ -n "${TO_DROP:-}" && "${TO_DROP}" != "NULL" ]]; then
  MYSQL "ALTER TABLE ${DB}.trends DROP PARTITION ${TO_DROP};"
fi

TO_DROP2=$(MYSQL "
  SELECT GROUP_CONCAT(PARTITION_NAME)
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA='${DB}' AND TABLE_NAME='trends_uint'
    AND PARTITION_NAME REGEXP '^p[0-9]{6}$'
    AND PARTITION_DESCRIPTION < ${CUTOFF_EPOCH};
" || true)

if [[ -n "${TO_DROP2:-}" && "${TO_DROP2}" != "NULL" ]]; then
  MYSQL "ALTER TABLE ${DB}.trends_uint DROP PARTITION ${TO_DROP2};"
fi

Grant execution permission:

# chmod +x /usr/local/bin/zbx-part-trends.sh

4) Cron Registration

Create the cron file:

# cat >/etc/cron.d/zbx-partition <<'CRON'
CRON_TZ=UTC
5 1 * * * root /usr/local/bin/zbx-part-history.sh >/var/log/zbx-part-history.log 2>&1
10 1 1 * * root /usr/local/bin/zbx-part-trends.sh  >/var/log/zbx-part-trends.log  2>&1
CRON

# chmod 644 /etc/cron.d/zbx-partition
# chown root:root /etc/cron.d/zbx-partition

5) Execution Summary

Log files:

• /var/log/zbx-part-history.log
• /var/log/zbx-part-trends.log

6) Verification Checklist

• Confirm that /data/history and /data/trends contain files named like pYYYYMMDD.ibd and pYYYYMM.ibd.
• Run SHOW CREATE TABLE zabbix.history\G and verify that new partitions appear.
• Check the cron execution status with: grep CRON /var/log/syslog

7) Next Part Preview

In Part 5, we’ll disable the Zabbix Housekeeper process and tune server parameters such as
CacheSize, HistoryCacheSize, TrendCacheSize, and StartDBSyncers to balance Zabbix’s internal cache with MySQL I/O load for maximum performance.

🛠 마지막 수정일: 2025.10.30