ZABBIX monitoring historical data cleaning operation scheme

Time:2020-1-6

After ZABBIX monitoring runs for a period of time, it will leave a lot of historical monitoring data, and the ZABBIX database has been growing; it may cause system performance degradation, and the query speed of viewing historical data room is slow.

The biggest tables in ZABBIX are history and history uint, and the time in ZABBIX is recorded by using the time stamp method, so historical data can be deleted according to the time stamp

Close ZABBIX and HTTP services

pkill -9 zabbix
service httpd stop

Clean up ZABBIX historical data

View database catalog file

[[email protected] zabbix]# cd /var/lib/mysql/zabbix/
[[email protected] zabbix]# ls -lh | grep G
total 177G
-rw-r----- 1 mysql mysql 1.7G Dec 24 13:49 events.ibd
-rw-r----- 1 mysql mysql  60G Dec 24 13:49 history.ibd
-rw-r----- 1 mysql mysql 2.4G Dec 24 13:49 history_str.ibd
-rw-r----- 1 mysql mysql  99G Dec 24 13:49 history_uint.ibd
-rw-r----- 1 mysql mysql 4.6G Dec 24 13:02 trends.ibd
-rw-r----- 1 mysql mysql 9.5G Dec 24 13:49 trends_uint.ibd
[[email protected] zabbix]# 
Generate UNIX timestamps. The time is set as February 1, 2018 (tentative is to save the monitoring data after February 18)
[root @ ZABBIX server ZABBIX] ා date +% s - D "Feb 1, 2018 00:00:00" ා after executing this command, an ID will be generated
1517414400 this is the generated ID

Data backup

[root @ ZABBIX server ZABBIX] ාmysql-uroot-p ZABBIX > / root / mysqlback / zabbix.sql ාmysqlback directory needs to be created

Log in to the database

[[email protected] zabbix]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

Use ZABBIX; select ZABBIX database

#Execute SQL to view the data size before the specified date:
SELECT table_schema as `Database`,table_name AS `Table`,round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `Size in MB`FROM information_schema.TABLES where CREATE_TIME < '2018-02-01 00:00:00' and table_name='history.ibd';
#Modify the date and the table name of the query as needed (if the result of the query is 0.0, delete one of the three 1024 in the SQL and display it in G)

Execute the following command to clean up the data before the specified time, and execute the following SQL on the ZABBIX database

delete from history where clock < 1517414400;
optimize table history;

delete from history_uint where clock < 1517414400;
optimize table history_uint;

delete from trends where clock < 1517414400;
optimize table trends;

delete from trends_uint where clock < 1517414400;
optimize table trends_uint;

Note: the ID in SQL is the ID number of UNIX timestamp generation, which needs to be changed to the ID number generated by itself

Startup service

/usr/sbin/zabbix_server -c /etc/zabbix/zabbix_server.conf    #zabbix server
/usr/sbin/zabbix_agentd -c /etc/zabbix/zabbix_agentd.conf    #zabbix agent
service httpd start

Use truncate command to clear all monitoring data of ZABBIX

-------------------------------------------------------
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;
-------------------------------------------------------

Note: these commands will clear all monitoring data of ZABBIX, and pay attention to backup the database before operation

Truncate is to delete the table and then re create it according to the table structure. Delete is to delete the record data without modifying the table

Truncate is faster to delete, but it is not as safe as delete in transaction processing. If the table we executed truncate is processing a transaction, this command exits and generates an error message