Zabbix monitoring--Clean up historical data

Zabbix after running for a period of time, will leave a lot of historical data, will find that the Zabbix database has been increasing. After 3 months of operation, the author's database reached 5.7G, which may result in degraded system performance and slow query when viewing historical data.
Zabbix inside the largest table is the history of the table, many people on the web is to write all the data to empty these tables, in fact, we can delete the history of the record in time.

The largest table inside is "history" and "History_uint" two tables;
650) this.width=650; "src=" Http://blog.chinaunix.net/attachment/201402/28/29179844_1393550864IFNG.jpg "height=" 213 "width=" 295 "style=" border:0px; "/>

The time in the Zabbix is recorded using the timestamp method, we can convert it and then delete it according to the timestamp;

For example, to delete data from 2014 years before January 1

1. Convert standard Time to timestamp first

# date +%s-d "2014-01-01 00:00:01"
1388505601

2. mysql Cleanup data

Click ( here ) to collapse or open

1. MySQL> DELETE from ' history_uint ' WHERE ' clock ' < 1388505601;
   

2. 
   

3. MySQL> optimize table history_uint;

Note: It may take a long time to execute a second line of command, without interruption in the middle, otherwise it is easy to lose data.

This is more practical to delete historical data according to the time period, there is also a way to completely erase historical monitoring data
Zabbix empty history MySQL database operation:

Click ( here ) to collapse or open

2. Mysql-uroot-p Enter the MySQL password
   

4. Use Zabbix;
   

6. TRUNCATE TABLE history;
   

8. Optimize table history;
   

10. TRUNCATE TABLE History_str;
    

12. Optimize table history_str;
    

14. TRUNCATE TABLE History_uint;
    

16. Optimize table History_uint;
    

18. TRUNCATE TABLE trends;
    

20. Optimize table trends;
    

22. TRUNCATE TABLE Trends_uint;
    

24. Optimize table Trends_uint;
    

26. TRUNCATE TABLE events;
    

28. Optimize table events;

Note: This operation clears all historical monitoring data for Zabbix, please back up the database before operation!

This article is from the "Dream to Reality" blog, please be sure to keep this source http://lookingdream.blog.51cto.com/5177800/1846389

Zabbix monitoring--Clean up historical data