AHMED ZBYR

AHMED ZBYR

Biker, EV Enthusiast

In large Zabbix deployments, the raw history tables (history, history_uint, history_str, history_log, and history_text) frequently grow to tens or hundreds of gigabytes. Running a DELETE FROM history_uint WHERE clock < ... statement against a massive InnoDB table is both slow (holding row locks and generating huge undo logs) and does not reclaim disk space on the filesystem without a subsequent OPTIMIZE TABLE.

A much faster way to prune an oversized Zabbix history table and immediately reclaim disk space (when MySQL is configured with innodb_file_per_table=ON) is the shadow-table swap pattern:

  1. Stop zabbix-server.
  2. Back up the target table (mysqldump) as a safety precaution.
  3. Create a new empty table (CREATE TABLE ... LIKE history_uint).
  4. Copy only the recent rows you want to keep (for example, the last 3 months based on Unix clock epoch) into the new table (INSERT INTO ... SELECT).
  5. Atomically rename history_uint to history_uint_old and rename the new table into place (history_uint).
  6. Drop history_uint_old to immediately release the old .ibd file back to the OS filesystem.
  7. Start zabbix-server.

Prerequisites & Warnings:

  • Verify innodb_file_per_table=ON (SHOW VARIABLES LIKE 'innodb_file_per_table';) before starting. If innodb_file_per_table is OFF, dropping history_uint_old frees pages inside the global ibdata1 file rather than shrinking ibdata1 on disk.
  • Ensure you have enough free disk space to hold the recent 3-month subset of rows during Step 4 before Step 7 drops the old table.
  • For long-term high-volume Zabbix installations, consider MySQL/MariaDB table partitioning (or TimescaleDB on PostgreSQL) and disable Zabbix’s internal Housekeeper for history* and trends*.

Step-by-Step Procedure

Step 1: Stop the Zabbix Server

Stop zabbix-server so no new metrics are written while the table swap is in progress:

sudo service zabbix-server stop

Step 2: Back Up the Target Table (Optional Safety Dump)

If you have sufficient disk space on a backup volume, dump the existing history_uint table first:

mysqldump -u zabbix -p zabbix history_uint > /tmp/history_uint.sql

Step 3: Create the New Empty Table (CREATE TABLE ... LIKE)

CREATE TABLE ... LIKE clones the exact column definitions and indexes of history_uint:

CREATE TABLE history_uint_new_20161007 LIKE history_uint;

Step 4: Copy Only Recent Records Using a Unix Epoch Cutoff

Zabbix stores timestamps in the clock column as Unix epoch seconds. You can calculate the epoch timestamp for 3 months ago using GNU date:

# Example: specific date to Unix epoch
date --date "20160707" +%s
# 1467829800

# Dynamic: 3 months ago from right now
date -d "now -3 months" +%s

Insert only rows newer than your cutoff timestamp into the new table:

INSERT INTO history_uint_new_20161007
SELECT * FROM history_uint
WHERE clock > 1467829800;

Step 5: Rename the Old Table and Swap in the New Table

Rename the original table to history_uint_old and rename history_uint_new_20161007 to history_uint:

ALTER TABLE history_uint RENAME history_uint_old;
ALTER TABLE history_uint_new_20161007 RENAME history_uint;

(Tip: You can also perform both renames in a single atomic statement with RENAME TABLE history_uint TO history_uint_old, history_uint_new_20161007 TO history_uint;.)

Step 6: Drop the Old Table to Reclaim Disk Space

Once you confirm history_uint contains the expected row count, drop the old table:

DROP TABLE history_uint_old;

Step 7: Restart the Zabbix Server

sudo service zabbix-server start

Step 8: Reduce Item History Retention to Prevent Future Bloat (Optional)

To prevent history* tables from growing excessively again, review item history retention intervals. In Zabbix 2.x/3.0, items.history is stored as an integer number of days:

-- Zabbix 2.x / 3.0 (integer days): cap item history retention at 15 days where > 30 days
UPDATE items SET history = 15 WHERE history > 30;

(Note: In Zabbix 3.4+, the history column uses time-unit suffixes such as '15d' or '90d' and can be overridden globally under Administration → General → Housekeeping.)

Complete Automation Script

Below is the complete automation script (history_table_clean_up_db_script.sh):

#!/bin/bash
set -euo pipefail

THREE_MONTH_BACK_DATE=$(date -d "now -3 months" +%Y-%m-%d)
CURRENT_DATE=$(date -d "now" +%Y-%m-%d)

EPOCH_3MONTHS_BACK=$(date -d "$THREE_MONTH_BACK_DATE" +%s)
EPOCH_NOW=$(date -d "$CURRENT_DATE" +%s)

ZABBIX_DATABASE="zabbix"
ZABBIX_USER="zabbix"
ZABBIX_PASSWD="REPLACE_WITH_DB_PASSWORD"

ZABBIX_TABLE_NAME="history_uint"

BACKUP_DIR_PATH="/tmp/zabbix/zabbix_table_backup_${ZABBIX_TABLE_NAME}"
BACKUP_FILE_PATH="${BACKUP_DIR_PATH}/${ZABBIX_TABLE_NAME}_${CURRENT_DATE}_${EPOCH_NOW}.sql"

echo "------------------------------------------"
echo "Date to Keep From      : $THREE_MONTH_BACK_DATE"
echo "Epoch to Keep From     : $EPOCH_3MONTHS_BACK"
echo "Today's Date           : $CURRENT_DATE"
echo "Epoch For Today's Date : $EPOCH_NOW"
echo "------------------------------------------"

echo "------------------------------------------"
echo "  1. Stopping Zabbix Server"
echo "------------------------------------------"
sudo service zabbix-server stop
sleep 1

echo "------------------------------------------"
echo "  Display Tables"
echo "------------------------------------------"
echo "SHOW TABLES;" | mysql -u"$ZABBIX_USER" -p"$ZABBIX_PASSWD" "$ZABBIX_DATABASE"
sleep 1

echo "------------------------------------------"
echo "  2. Backing up ${ZABBIX_TABLE_NAME} Table"
echo "     Location : ${BACKUP_FILE_PATH}"
echo "------------------------------------------"
mkdir -p "${BACKUP_DIR_PATH}"
mysqldump --single-transaction -u"$ZABBIX_USER" -p"$ZABBIX_PASSWD" "$ZABBIX_DATABASE" "${ZABBIX_TABLE_NAME}" > "${BACKUP_FILE_PATH}"
sleep 1

echo "------------------------------------------------------------------"
echo "  3. Create Temp (${ZABBIX_TABLE_NAME}_${EPOCH_NOW}) Table"
echo "------------------------------------------------------------------"
echo "CREATE TABLE ${ZABBIX_TABLE_NAME}_${EPOCH_NOW} LIKE ${ZABBIX_TABLE_NAME};" | mysql -u"$ZABBIX_USER" -p"$ZABBIX_PASSWD" "$ZABBIX_DATABASE"
sleep 1

echo "------------------------------------------------------------------"
echo "  4. Copy Recent Rows from ${ZABBIX_TABLE_NAME} to Temp Table"
echo "------------------------------------------------------------------"
echo "INSERT INTO ${ZABBIX_TABLE_NAME}_${EPOCH_NOW} SELECT * FROM ${ZABBIX_TABLE_NAME} WHERE clock > ${EPOCH_3MONTHS_BACK};" | mysql -u"$ZABBIX_USER" -p"$ZABBIX_PASSWD" "$ZABBIX_DATABASE"
sleep 1

echo "------------------------------------------------------------------"
echo "  5. Rename ${ZABBIX_TABLE_NAME} to ${ZABBIX_TABLE_NAME}_${EPOCH_NOW}_old"
echo "------------------------------------------------------------------"
echo "ALTER TABLE ${ZABBIX_TABLE_NAME} RENAME ${ZABBIX_TABLE_NAME}_${EPOCH_NOW}_old;" | mysql -u"$ZABBIX_USER" -p"$ZABBIX_PASSWD" "$ZABBIX_DATABASE"
sleep 1

echo "------------------------------------------"
echo "  6. Rename Temp Table (${ZABBIX_TABLE_NAME}_${EPOCH_NOW}) to ${ZABBIX_TABLE_NAME}"
echo "------------------------------------------"
echo "ALTER TABLE ${ZABBIX_TABLE_NAME}_${EPOCH_NOW} RENAME ${ZABBIX_TABLE_NAME};" | mysql -u"$ZABBIX_USER" -p"$ZABBIX_PASSWD" "$ZABBIX_DATABASE"
sleep 1

echo "------------------------------------------"
echo "  7. Drop Old Table (${ZABBIX_TABLE_NAME}_${EPOCH_NOW}_old)"
echo "------------------------------------------"
echo "DROP TABLE ${ZABBIX_TABLE_NAME}_${EPOCH_NOW}_old;" | mysql -u"$ZABBIX_USER" -p"$ZABBIX_PASSWD" "$ZABBIX_DATABASE"
sleep 1

echo "------------------------------------------"
echo "  8. Starting Zabbix Server"
echo "------------------------------------------"
sudo service zabbix-server start

References

You May Also Enjoy