Zabbix优化之必杀技-表分区_MySQL

时间2014-05-06

作者itnihao

邮箱[email protected]

博客http://www.itnihao.com

如需引用，请注明以上信息，谢谢合作

前言，使用zabbix最大的瓶颈在于数据库,维护好zabbix的数据存储，告警，即能够很好的应用zabbix去构建监控系统。本文所讲的正是数据存储部分。本文所针对的用户，需要对zabbix有一定概念，对MySQL熟悉，掌握存储过程的书写，对zabbix数据库字段熟悉

本部分内容来自本人的新书，作为对新书分表章节的部分补充，书名叫《zabbix监控系统》，将于2014-06与读者面市。书的章节目录已经放在github上面

https://github.com/itnihao/zabbix-book/blob/master/README.md

Zabbix中历史数据的

zabbix对数据将数据存于数据库，其主要将历史数据存于history和trends的2个表中，如下

1）历史数据的表

2）警告日志数据的表

History表结构

mysql> show create table history/G;*************************** 1. row *************************** Table: historyCreate Table: 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=utf8mysql> show create table history_str/G; Table: his<div>本文来源gaodai^.ma#com搞#代!码网</div>tory_strCreate Table: CREATE TABLE `history_str` (`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`value` varchar(255) NOT NULL DEFAULT '',`ns` int(11) NOT NULL DEFAULT '0',KEY `history_str_1` (`itemid`,`clock`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql> show create table history_str_sync /G;*************************** 1. row *************************** Table: history_str_syncCreate Table: CREATE TABLE `history_str_sync` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`nodeid` int(11) NOT NULL,`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`value` varchar(255) NOT NULL DEFAULT '',`ns` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`id`),KEY `history_str_sync_1` (`nodeid`,`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql> show create table history_sync /G;*************************** 1. row *************************** Table: history_syncCreate Table: CREATE TABLE `history_sync` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`nodeid` int(11) NOT NULL,`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',PRIMARY KEY (`id`),KEY `history_sync_1` (`nodeid`,`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql> show create table history_text /G;*************************** 1. row *************************** Table: history_textCreate Table: CREATE TABLE `history_text` (`id` bigint(20) unsigned NOT NULL,`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`value` text NOT NULL,`ns` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`id`),UNIQUE KEY `history_text_2` (`itemid`,`id`),KEY `history_text_1` (`itemid`,`clock`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql> show create table history_log/G;*************************** 1. row *************************** Table: history_logCreate Table: CREATE TABLE `history_log` (`id` bigint(20) unsigned NOT NULL,`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`timestamp` int(11) NOT NULL DEFAULT '0',`source` varchar(64) NOT NULL DEFAULT '',`severity` int(11) NOT NULL DEFAULT '0',`value` text NOT NULL,`logeventid` int(11) NOT NULL DEFAULT '0',`ns` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`id`),UNIQUE KEY `history_log_2` (`itemid`,`id`),KEY `history_log_1` (`itemid`,`clock`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql> show create table history_uint /G;*************************** 1. row *************************** Table: history_uintCreate Table: CREATE TABLE `history_uint` (`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`value` bigint(20) unsigned NOT NULL DEFAULT '0',`ns` int(11) NOT NULL DEFAULT '0',KEY `history_uint_1` (`itemid`,`clock`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql> show create table history_uint_sync/G;*************************** 1. row *************************** Table: history_uint_syncCreate Table: CREATE TABLE `history_uint_sync` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`nodeid` int(11) NOT NULL,`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`value` bigint(20) unsigned NOT NULL DEFAULT '0',`ns` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`id`),KEY `history_uint_sync_1` (`nodeid`,`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

trends表结构

mysql> show create table trends/G;*************************** 1. row *************************** Table: trendsCreate Table: CREATE TABLE `trends` (`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`num` int(11) NOT NULL DEFAULT '0',`value_min` double(16,4) NOT NULL DEFAULT '0.0000',`value_avg` double(16,4) NOT NULL DEFAULT '0.0000',`value_max` double(16,4) NOT NULL DEFAULT '0.0000',PRIMARY KEY (`itemid`,`clock`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql> show create table trends_uint/G;*************************** 1. row *************************** Table: trends_uintCreate Table: CREATE TABLE `trends_uint` (`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`num` int(11) NOT NULL DEFAULT '0',`value_min` bigint(20) unsigned NOT NULL DEFAULT '0',`value_avg` bigint(20) unsigned NOT NULL DEFAULT '0',`value_max` bigint(20) unsigned NOT NULL DEFAULT '0',PRIMARY KEY (`itemid`,`clock`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

housekeeper表结构

mysql> show create table housekeeper/G;*************************** 1. row *************************** Table: housekeeperCreate Table: CREATE TABLE `housekeeper` (`housekeeperid` bigint(20) unsigned NOT NULL,`tablename` varchar(64) NOT NULL DEFAULT '',`field` varchar(64) NOT NULL DEFAULT '',`value` bigint(20) unsigned NOT NULL,PRIMARY KEY (`housekeeperid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

喜欢 (0)赏

[搞代码]

分享 (0)