Zabbix DB가 느릴 때: MySQL 파티션으로 해결하는 실무 구축법 (4편) — 자동 파티션 스크립트와 크론 관리로 완전 자동화하기

개요

이 글은 3편에서 생성한 파티션 구조를 자동으로 유지·관리하는 단계를 다룬다.
Zabbix는 수집량이 많기 때문에, 매일·매월 수동으로 파티션을 추가하거나 오래된 데이터를 삭제하는 것은 비효율적이다.

이번 편에서는 다음 두 가지 스크립트를 통해 완전 자동화한다.

• /usr/local/bin/zbx-part-history.sh — 일 단위(history) 파티션 자동 생성 및 90일 이전 데이터 삭제
• /usr/local/bin/zbx-part-trends.sh — 월 단위(trends) 파티션 자동 생성 및 12개월 이전 데이터 삭제

1) 전제 조건

• 1~3편의 절차 완료 (history/trends 파티션 구조 적용)
• MySQL 접속 계정: zabbix
• /data/history, /data/trends 디렉토리 존재
• UTC 기준으로 동작 (크론 타임존은 UTC로 고정)

2) History 자동화 스크립트

파일 경로: /usr/local/bin/zbx-part-history.sh

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

# ===== 기본 설정 =====
DB="zabbix"
DB_USER="zabbix"
DB_PASS="여기에 DB 비밀번호"
DB_HOST="127.0.0.1"
TARGET_DIR="/data/history"
DAYS_KEEP=90   # 보관 일수

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

# ===== 오늘 날짜 기준 계산 =====
TODAY_UTC=$(date -u +%Y-%m-%d)
CURRENT_EPOCH=$(date -u -d "${TODAY_UTC} 00:00:00" +%s)
NEXT_BOUNDARY_EPOCH=$((CURRENT_EPOCH + 86400))   # 내일 00:00
NEXT_TAG=$(date -u -d "@${CURRENT_EPOCH}" +%Y%m%d)

# ===== 파티션 존재 여부 확인 =====
EXISTS=$(MYSQL "
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA='${DB}' AND TABLE_NAME='history'
    AND PARTITION_NAME='p${NEXT_TAG}';
")

# ===== 새로운 파티션 생성 =====
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

# ===== 오래된 파티션 자동 삭제 =====
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

저장 후 실행 권한 부여:

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

3) Trends 자동화 스크립트

파일 경로: /usr/local/bin/zbx-part-trends.sh

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

# ===== 기본 설정 =====
DB="zabbix"
DB_USER="zabbix"
DB_PASS="여기에 DB 비밀번호"
DB_HOST="127.0.0.1"
TARGET_DIR="/data/trends"
MONTHS_KEEP=12   # 보관 개월 수

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

# ===== 오늘 기준 월 계산 =====
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)

# ===== 파티션 존재 확인 및 생성 =====
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

# ===== 오래된 월 파티션 삭제 =====
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

저장 후 권한 부여:

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

4) 크론 등록

크론 파일 생성:

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) 동작 결과 요약

로그 파일 위치:

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

6) 점검 포인트

• /data/history 및 /data/trends 경로에 pYYYYMMDD.ibd, pYYYYMM.ibd 형식으로 파일이 자동 생성되는지 확인
• mysql> SHOW CREATE TABLE zabbix.history\G 실행 시, 새 파티션 정보가 반영되어야 함
• 크론 로그(grep CRON /var/log/syslog)로 스크립트 실행 여부 확인

7) 다음 편 예고

5편에서는 Zabbix Housekeeper를 비활성화하고,
CacheSize, HistoryCacheSize, TrendCacheSize, StartDBSyncers 등의 값을 조정해
Zabbix 서버 자체 캐시와 MySQL I/O 밸런스를 맞추는 최종 튜닝 단계를 다룬다.

🛠 마지막 수정일: 2025.10.28