Zabbix stores metric history using Unix timestamps in large InnoDB tables (notab history and history_uint). Over time these grow substantially, slowing queries and cnosuming disk. Old rows can be removed by timestamp to reclaim space.

Stop Zabbix and web services

# Prefer systemd when available
systemctl stop zabbix-server zabbix-agent httpd 2>/dev/null || true

# Fallback for non-systemd systems
service zabbix-server stop 2>/dev/null || true
service zabbix-agent stop 2>/dev/null || true
service httpd stop 2>/dev/null || true

# Ensure no lingering processes
pkill -9 -f zabbix 2>/dev/null || true

Inspect database table files

cd /var/lib/mysql/zabbix
# Quick glance at large .ibd files
ls -lh *.ibd | egrep 'G|M'
# Or aggregate sizes
du -sh *.ibd | sort -h | tail -n 10

Compute the cutoff timestamp

# Example: keep data from Feb 1, 2018 onward
CUTOFF_HUMAN="2018-02-01 00:00:00"
CUTOFF_TS=$(date -d "$CUTOFF_HUMAN" +%s)
echo "$CUTOFF_HUMAN -> $CUTOFF_TS"

Create a backup

mkdir -p /root/mysqlback
mysqldump -uroot -p \
  --single-transaction --quick --routines --triggers \
  zabbix > /root/mysqlback/zabbix-$(date +%F).sql

Connect to MySQL and review table sizes

mysql -uroot -p

USE zabbix;

-- Table size overview (GiB)
SELECT
  table_name,
  ROUND((data_length + index_length)/1024/1024/1024, 2) AS size_gb
FROM information_schema.tables
WHERE table_schema = 'zabbix'
  AND table_name IN ('history','history_str','history_uint','trends','trends_uint','events')
ORDER BY size_gb DESC;

Purge rows older than the cutoff and reclaim space

-- Set cutoff based on computed Unix timestamp
SET @cutoff := 1517414400; -- replace with your $CUTOFF_TS

-- Remove old high-cardinality history first
DELETE FROM history       WHERE clock < @cutoff;
OPTIMIZE TABLE history;

DELETE FROM history_uint  WHERE clock < @cutoff;
OPTIMIZE TABLE history_uint;

DELETE FROM history_str   WHERE clock < @cutoff;
OPTIMIZE TABLE history_str;

-- Summarized trend data
DELETE FROM trends        WHERE clock < @cutoff;
OPTIMIZE TABLE trends;

DELETE FROM trends_uint   WHERE clock < @cutoff;
OPTIMIZE TABLE trends_uint;

Start services

systemctl start zabbix-server zabbix-agent httpd 2>/dev/null || true
service zabbix-server start 2>/dev/null || true
service zabbix-agent start 2>/dev/null || true
service httpd start 2>/dev/null || true

Clear all monitoring data (destructive)

TRUNCATE TABLE history;      OPTIMIZE TABLE history;
TRUNCATE TABLE history_str;  OPTIMIZE TABLE history_str;
TRUNCATE TABLE history_uint; OPTIMIZE TABLE history_uint;
TRUNCATE TABLE trends;       OPTIMIZE TABLE trends;
TRUNCATE TABLE trends_uint;  OPTIMIZE TABLE trends_uint;
TRUNCATE TABLE events;       OPTIMIZE TABLE events;

Notes

• Back up the database before executing DELETE or TRUNCATE operasions.
• TRUNCATE drops and recreates the table, which is faster but not transactional and cannot be rolled back.
• DELETE preserves the table structure and participates in transactions but is slower for very large volumes.
• Run space-reclaiming operaitons during maintenance windows to minimize impact.