Measure storage use in the Zabbix schema

Identify which tables consume the most space before pruning.

SELECT
  t.table_name AS tbl,
  ROUND((t.data_length + t.index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables AS t
WHERE t.table_schema = 'zabbix'
ORDER BY size_mb DESC;

Large footprints usually come from history and history_uint, which hold item value timelines.

Inspect the history tables

DESCRIBE history_uint;
DESCRIBE history;

Typical structures:

• history_uint

  • itemid BIGINT UNSIGNED
  • clock INT
  • value BIGINT UNSIGNED
  • ns INT

• history

  • itemid BIGINT UNSIGNED
  • clock INT
  • value DOUBLE(16,4)
  • ns INT

The clock column stores a UNIX epoch (seconds). Deleting by clock removes old data.

Stop Zabbix server before bulk deletes

Avoid writing conflicts by pausing the server component that inserts historical rows.

# Systemd-based hosts
sudo systemctl stop zabbix-server

Compute a cutoff timestamp

Example for a specific point in time:

date -u -d '2019-06-04 00:00:00' +%s

Example for data older then 30 days:

# Local timezone
date -d '30 days ago' +%s
# UTC
date -u -d '30 days ago' +%s

Purge old rows and reclaim space

Delete using the cutoff epoch, then run OPTIMIZE to release disk space to the storage engine.

-- Replace 1559577600 with your computed cutoff
DELETE FROM history      WHERE clock < 1559577600;
DELETE FROM history_uint WHERE clock < 1559577600;

OPTIMIZE TABLE history;
OPTIMIZE TABLE history_uint;

For very large datasets, execute chunked deletes to reduce long locks and transaction size:

-- Repeat until affected_rows() = 0
DELETE FROM history      WHERE clock < 1559577600 LIMIT 50000;
DELETE FROM history_uint WHERE clock < 1559577600 LIMIT 50000;

Automated cleanup script (30‑day retention)

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

DB_HOST="127.0.0.1"
DB_NAME="zabbix"
DB_USER="zabbix"
DB_PASS="zabbix"
RETENTION_DAYS=30

# Compute cutoff epoch
CUTOFF_EPOCH=$(date -d "${RETENTION_DAYS} days ago" +%s)

mysql -h"${DB_HOST}" -u"${DB_USER}" -p"${DB_PASS}" "${DB_NAME}" <<SQL
-- Avoid binlog bloat if using replication and this host is a replica-only cleaner
SET SESSION sql_log_bin = 0;

DELETE FROM history      WHERE clock < ${CUTOFF_EPOCH};
DELETE FROM history_uint WHERE clock < ${CUTOFF_EPOCH};

OPTIMIZE TABLE history, history_uint;
SQL

Chunked variant to limit each transaction size:

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

DB_HOST="127.0.0.1"
DB_NAME="zabbix"
DB_USER="zabbix"
DB_PASS="zabbix"
RETENTION_DAYS=30
BATCH=50000

CUTOFF_EPOCH=$(date -d "${RETENTION_DAYS} days ago" +%s)

while :; do
  affected=$(mysql -N -B -h"${DB_HOST}" -u"${DB_USER}" -p"${DB_PASS}" "${DB_NAME}" \
    -e "DELETE FROM history WHERE clock < ${CUTOFF_EPOCH} LIMIT ${BATCH}; SELECT ROW_COUNT();")
  [ "${affected}" -eq 0 ] && break
  sleep 0.2
done

while :; do
  affected=$(mysql -N -B -h"${DB_HOST}" -u"${DB_USER}" -p"${DB_PASS}" "${DB_NAME}" \
    -e "DELETE FROM history_uint WHERE clock < ${CUTOFF_EPOCH} LIMIT ${BATCH}; SELECT ROW_COUNT();")
  [ "${affected}" -eq 0 ] && break
  sleep 0.2
done

mysql -h"${DB_HOST}" -u"${DB_USER}" -p"${DB_PASS}" "${DB_NAME}" -e "OPTIMIZE TABLE history, history_uint;"

After maintenance, start the Zabbix server again:

sudo systemctl start zabbix-server