Zabbix历史数据清理
Zabbix历史数据清理
添加索引(效率高)
ALTER TABLE `history_str` ADD INDEX index_history_str_clock ( `clock` );
ALTER TABLE `trends` ADD INDEX index_trends_clock ( `clock` );
ALTER TABLE `trends_uint` ADD INDEX index_trends_uint_clock ( `clock` );
ALTER TABLE `events` ADD INDEX index_events_clock ( `clock` );
ALTER TABLE `history` ADD INDEX index_history_clock ( `clock` );
ALTER TABLE `history_text` ADD INDEX index_history_text_clock ( `clock` );
ALTER TABLE `history_uint` ADD INDEX index_history_uint_clock ( `clock` );
#查看索引
show indexes from history_uint;
1、统计数据库中每个表所占的空间:
2、清理zabbix一周之前的历史数据:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
! /bin/bash User= "zabbixuser" Passwd= "zabbixpass" Date=` date -d $( date -d "-7 day" +%Y%m%d) +%s` #取7天之前的时间戳 $( which mysql) -u${User} -p${Passwd} -e " use zabbixdb; DELETE FROM history WHERE clock < $Date; optimize table history ; DELETE FROM history_str WHERE clock < $Date; optimize table history_str; DELETE FROM history_uint WHERE clock < $Date; optimize table history_uint; DELETE FROM history_text WHERE clock < $Date; optimize table history_text; DELETE FROM trends WHERE clock < $Date; optimize table trends; DELETE FROM trends_uint WHERE clock < $Date; optimize table trends_uint; DELETE FROM events WHERE clock < $Date; optimize table events; " |
3、添加到系统计划任务:
1
2
|
#remove the zabbix mysql data before 7 day's ago 0 3 * * 0 /usr/local/script/clearzabbix .sh > /usr/local/script/clearzabbix .log |
另:可以使用truncate命令直接清空数据库:
1
2
3
4
5
6
7
|
truncate table history ; truncate table history_uint; truncate table history_str; truncate table history_text; truncate table trends; truncate table trends_uint; truncate table events; |
如果想要删除表的所有数据,truncate语句要比 delete 语句快。
因为 truncate 删除了表,然后根据表结构重新建立它,而 delete 删除的是记录,并没有尝试去修改表。
不过truncate命令虽然快,却不像delete命令那样对事务处理是安全的。
因此,如果我们想要执行truncate删除的表正在进行事务处理,这个命令就会产生退出并产生错误信息。