Cleaning up the Zabbix database

Your issues are going to come from this table history_uint, which holds the items history data.

Since this is not an official procedure, use it at your own risk.

Environment:
Zabbix v3.0
MySql 5.1 – InnoDB with innodb_file_per_table=ON

Step 1 – Stop the Zabbix server

Step 2 – Open your favourite MySQL client and create a new table

CREATE TABLE history_uint_new LIKE history_uint

Step 3 – Insert the latest records from the history_uint table to the history_uint_new table

First you need to decide how many records you need to keep. Please note, that the history data in graphs will still be available since trends data is stored in a different table.

I decided to keep the data for the last 3 weeks. The event time is in UNIX-time (aka POSIX-time or Epoch time) format, so you need to calculate the date and time of the event you will start with.

There is a tool available to do this calculation for you – Epoch Converter.

For example, if you decide to keep all the events from the Wed, 15 Jun 2016 09:13:14 GMT on, the Epoch time would be 1465981994000.

Now that we have our Epoch timestamp, we can copy the records to the new table:

INSERT INTO history_uint_new SELECT * FROM history_uint WHERE clock > '1465981994000'

If this crashes due to a MySQL Connection error try truncating the history_unit table first.

# mysqlq -p

(login with root password)

mysql> TRUNCATE TABLE history_uint;

Step 4 – Rename the history_uint and history_uint_new tables

ALTER TABLE history_uint RENAME history_uint_old
ALTER TABLE history_uint_new RENAME history_uint

Step 5 – Start the Zabbix server

Start the Zabbix server and check if everything is ok and if you’re happy…

Step 6 – Drop the old table and save some disk space

DROP TABLE history_uint_old

Additionally you can update the items table and set the item history table record to a fewer days, so Zabbix will do the automatic clean up before the table size gets too big.

UPDATE items SET history = '15' WHERE history > '30'