zabbix数据库日常处理

zabbix所有的数据库都存到数据库里。这样，有几个表的数据量非常大，做好数据库的备份，定期对history,trends,history_uint,events 这四个表做truncate处理。可以必免因为数据量太大，对系统造成过高的负载。

另外也可以利用mysql的分区功能：每个月一个表分区，当数据库大时，直接drop 表分区。
| history | CREATE TABLE `history` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` double(16,4) NOT NULL DEFAULT '0.0000',
  `ns` int(11) NOT NULL DEFAULT '0',
  KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( clock)
(PARTITION p1 VALUES LESS THAN (1356969600) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1359648000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (1362067200) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (1364745600) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (1367337600) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (1370016000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (1372608000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (1375286400) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (1377964800) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (1380556800) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (1383235200) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (1385827200) ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN (1388505600) ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN (1391184000) ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN (1393603200) ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN (1396281600) ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN (1398873600) ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN (1401552000) ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN (1404144000) ENGINE = InnoDB,
 PARTITION p20 VALUES LESS THAN (1406822400) ENGINE = InnoDB,
 PARTITION p21 VALUES LESS THAN (1409500800) ENGINE = InnoDB,
 PARTITION p22 VALUES LESS THAN (1412092800) ENGINE = InnoDB,
 PARTITION p23 VALUES LESS THAN (1414771200) ENGINE = InnoDB,
 PARTITION p24 VALUES LESS THAN (1417363200) ENGINE = InnoDB,
 PARTITION p25 VALUES LESS THAN (1420041600) ENGINE = InnoDB,
 PARTITION p26 VALUES LESS THAN (1422720000) ENGINE = InnoDB,
 PARTITION p27 VALUES LESS THAN (1425139200) ENGINE = InnoDB,
 PARTITION p28 VALUES LESS THAN (1427817600) ENGINE = InnoDB,
 PARTITION p29 VALUES LESS THAN (1430409600) ENGINE = InnoDB,
 PARTITION p30 VALUES LESS THAN (1433088000) ENGINE = InnoDB,
 PARTITION p31 VALUES LESS THAN (1435680000) ENGINE = InnoDB,
 PARTITION p32 VALUES LESS THAN (1438358400) ENGINE = InnoDB,
 PARTITION p33 VALUES LESS THAN (1441036800) ENGINE = InnoDB,
 PARTITION p34 VALUES LESS THAN (1443628800) ENGINE = InnoDB,
 PARTITION p35 VALUES LESS THAN (1446307200) ENGINE = InnoDB,
 PARTITION p36 VALUES LESS THAN (1448899200) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |