Zabbix Clean up historical data

Project on-line less than one months, Zabbix data incredibly fast 100G, too many monitoring items, too large data volume, Zabbix load, temporarily do not want to upgrade hardware, had to dig inside, clear the historical data; generally retain 1 months of data, Zabbix this project is only to do monitoring alarm, Data retention too much not much reference significance;

Generally these tables are too big, history, History_uint,history_log, and some directly when the table is clear, concise and convenient, but if someone needs reference data, it is quite awkward; some do a separate table, do the stored procedure, pretty efficient, But the project is not the same only simple monitoring does not have to be so troublesome;

The routines are all the same: Stop Zabbix,mysql add skip new to compress storage space after data is deleted, capturing the point in time that needs to be purged:

root@Testl]$ date +%s -d"20160929"

1475078400

Mysql> Delete from the history where clock<1475078400;

Query OK, 36093 rows Affected (43.64 sec)

mysql> Delete from History_uint Whereclock < 1475078400;

Query OK, 21501392 rows Affected (min51.48 sec)

Mysql> optimize table history;

Query OK, 12175692 rows affected (5 min9.16 sec)

records:12175692 duplicates:0 warnings:0

mysql> optimize table history_uint;

Query OK, 145195609 rows Affected (min29.07 sec)

records:145195609 duplicates:0 warnings:0

Wait a long time to see the effect:

Original data:

-RW-RW----1 mysql mysql 1.4G Oct 13:23history.ibd

-RW-RW----1 mysql mysql 12M Oct 13:23 items.ibd

-RW-RW----1 mysql mysql 96M Oct 13:23 events.ibd

-RW-RW----1 mysql mysql 17G Oct 13:23 history_uint.ibd

-RW-RW----1 mysql mysql 56G Oct 13:23 history_log.ibd

-RW-RW----1 mysql mysql 252M Oct 13:23 trends_uint.ibd

-RW-RW----1 mysql mysql 72M Oct 13:23 trends.ibd

-RW-RW----1 mysql mysql 112K Oct 13:59sessions.ibd

In compression:

indentation process to generate /#sql -5f11_3.ibd file, the job is finished history_uint.ibd file;

Find-size +50m-exec ls-lh {} \;

-RW-RW----1 mysql mysql 960M Oct 14:42./history.ibd

-RW-RW----1 mysql mysql 9.2G Oct 15:31./#sql -5f11_3.ibd

-RW-RW----1 mysql mysql 56G Oct 13:23./history_log.ibd

-RW-RW----1 mysql mysql 96M Oct 13:23./events.ibd

-RW-RW----1 mysql mysql 252M Oct 13:23./trends_uint.ibd

-RW-RW----1 mysql mysql 72M Oct 13:23./trends.ibd

-RW-RW----1 mysql mysql 17G Oct 14:59./history_uint.ibd

Final deletion of two tables after data:

zabbix]$ find-size +50m-execls-lh {} \;

-RW-RW----1 mysql mysql 960M Oct 15:42./history.ibd

-RW-RW----1 mysql mysql 56G Oct 15:42./history_log.ibd

-RW-RW----1 mysql mysql 96M Oct 15:42./events.ibd

-RW-RW----1 mysql mysql 252M Oct 15:41./trends_uint.ibd

-RW-RW----1 mysql mysql 72M Oct 15:41./trends.ibd

-RW-RW----1 mysql mysql 11G Oct 15:42./history_uint.ibd

after that, restart MySQL,Zabbix,nginx try;

looking at history_log so good grief, ready to truncate off. Later, you can make a script, the monthly execution of deletion records;

This article is from the "Silent Years" blog, please be sure to keep this source http://xpu2001.blog.51cto.com/890980/1866927

Zabbix Clean up historical data