Linux · 2015-01-28

zabbix历史数据清理


mysql> SELECT TABLE_NAME AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) AS Size_in_MB FROM information_schema.TABLES  WHERE table_schema = 'zabbix' ORDER BY Size_in_MB DESC LIMIT 10;          
+-----------------------+------------+
| Table                 | Size_in_MB |
+-----------------------+------------+
| history_uint          |    7195.09 |
| history               |    3494.61 |
| trends_uint           |     341.44 |
| trends                |     252.44 |
| alerts                |      24.70 |
| events                |      14.06 |
| history_str           |       3.52 |
| items                 |       2.08 |
| images                |       1.53 |
| auditlog              |       0.58 |

可以看出history_uint跟history这两个表堆积了大量的历史数据,两种方法删除zabbix的历史数据

第一种是直接清空表(历史记录全删)
truncate table history_uint;
optimize table history_uint;
truncate table history;
optimize table history;

第二种是删除指定时间段内的数据,timestamp可以用date命令来取, date "+%s" -d "年月日",比如
[root@localhost ~]# date "+%s" -d "20150127"
1422334800

删除2015年1月27日前zabbix的历史记录
DELETE FROM `history_uint` WHERE `clock` < 1422334800;
optimize table history_uint;
DELETE FROM `history` WHERE `clock` < 1422334800;
optimize table history;