时间2014-05-06
作者itnihao
邮箱itnihao@qq.com
博客http://www.itnihao.com
如需引用，请注明以上信息，谢谢合作
前言，使用zabbix最大的瓶颈在于数据库,维护好zabbix的数据存储，告警，即能够很好的应用zabbix去构建监控系统。本文所讲的正是数据存储部分。本文所针对的用户，需要对zabbix有一定概念，对mysql熟悉，掌握存储过程的书写，对zabbix数据库字段熟悉
本部分内容来自本人的新书，作为对新书分表章节的部分补充，书名叫《zabbix监控系统》，将于2014-06与读者面市。书的章节目录已经放在github上面
https://github.com/itnihao/zabbix-book/blob/master/readme.mdzabbix中历史数据的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: history_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
尽管将housekeeper功能已经关闭，但zabbix-server和web前端仍然会记录数据到housekeeper表，这里为了防止写入数据，将其表的引擎设置为blackhole，使其不可写。
mysql>alter table housekeeper engine = blackhole;
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=blackhole default charset=utf8
查看索引mysql> show index from history/g;
如下表所示
改变history_text表结构mysql> show create table history_text/g;
*************************** 1. row ***************************
table: history_text
create 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=utf8
mysql> alter table history_text drop primary key, add index (id), drop index history_text_2, add index history_text_2 (itemid, id);
mysql> show create table history_text/g;
*************************** 1. row ***************************
table: history_text
create 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',
key `history_text_1` (`itemid`,`clock`),
key `id` (`id`), #原来的primary key
key `history_text_2` (`itemid`,`id`) #原来的unique key
) engine=innodb default charset=utf8
改变history_log表结构mysql> show create table history_log/g;
*************************** 1. row ***************************
table: history_log
create 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=utf8
mysql> alter table history_log drop primary key, add index (id), drop index history_log_2, add index history_log_2 (itemid, id);
mysql> show create table history_log/g;
*************************** 1. row ***************************
table: history_log
create 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',
key `history_log_1` (`itemid`,`clock`),
key `id` (`id`), #原来的primary key
key `history_log_2` (`itemid`,`id`) #原来的unique key
) engine=innodb default charset=utf8
表分区的过程防盗链，来自博客http://www.itnihao.com
创建存储过程 分区创建的存储过程
delimiter $$
createprocedure`partition_create`(schemanamevarchar(64),tablenamevarchar(64),partitionnamevarchar(64),clockint)
begin
/*
schemaname = the db schema in which to make changes
tablename = the table with partitions to potentially delete
partitionname = the name of the partition to create
*/
/*
verify that the partition does not already exist
*
declareretrowsint;
selectcount(1)intoretrows
frominformation_schema.partitions
wheretable_schema=schemanameandtable_name=tablenameandpartition_name=partitionname;
ifretrows=0then
/*
1. print a message indicating that a partition was created
2. create the sql to create the partition
3. execute the sql from #2.
*/
selectconcat(partition_create(,schemaname,,,tablename,,,partitionname,,,clock,))asmsg;
set@sql=concat('alter table ',schemaname,'.',tablename,' add partition (partition ',partitionname,' values less than (',clock,'));');
preparestmtfrom@sql;
executestmt;
deallocatepreparestmt;
endif;
end$$
delimiter ;
分区删除的存储过程
delimiter $$
createprocedure`partition_drop`(schemanamevarchar(64),tablenamevarchar(64),delete_below_partition_datebigint)
begin
/*
schemaname = the db schema in which to make changes
tablename = the table with partitions to potentially delete
delete_below_partition_date = delete any partitions with names that are dates older than this one (yyyy-mm-dd)
*/
declaredoneintdefaultfalse;
declaredrop_part_namevarchar(16);
/*
get a list of all the partitions that are older than the date
in delete_below_partition_date. all partitions are prefixed with
a p, so use substring to get rid of that character.
*/
declaremycursor cursorfor
selectpartition_name
frominformation_schema.partitions
wheretable_schema=schemanameandtable_name=tablenameandcast(substring(partition_namefrom2)asunsigned)declarecontinue handlerfornotfoundsetdone=true;
/*
create the basics for when we need to drop the partition. also, create
@drop_partitions to hold a comma-delimited list of all partitions that
should be deleted.
*/
set@alter_header=concat(alter table ,schemaname,.,tablename, drop partition );
set@drop_partitions=;
/*
start looping through all the partitions that are too old.
*/
openmycursor;
read_loop: loop
fetch mycursorintodrop_part_name;
ifdonethen
leave read_loop;
endif;
set@drop_partitions=if(@drop_partitions=,drop_part_name,concat(@drop_partitions,,,drop_part_name));
endloop;
if@drop_partitions !=then
/*
1. build the sql to drop all the necessary partitions.
2. run the sql to drop the partitions.
3. print out the table partitions that were deleted.
*/
set@full_sql=concat(@alter_header,@drop_partitions,;);
preparestmtfrom@full_sql;
executestmt;
deallocatepreparestmt;
selectconcat(schemaname,.,tablename)as`table`,@drop_partitionsas`partitions_deleted`;
else
/*
no partitions are being deleted, so print out n/a (not
applicable) to indicatethat no changes were made.
*/
selectconcat(schemaname,.,tablename)as`table`,n/aas`partitions_deleted`;
endif;
end$$
delimiter ;
分区维护的存储过程
delimiter $$
createprocedure`partition_maintenance`(schema_namevarchar(32),table_namevarchar(32),keep_data_daysint,hourly_intervalint,create_next_intervalsint)
begin
declareolder_than_partition_datevarchar(16);
declarepartition_namevarchar(16);
declareless_than_timestampint;
declarecur_timeint;
callpartition_verify(schema_name,table_name,hourly_interval);
setcur_time=unix_timestamp(date_format(now(),'%y-%m-%d 00:00:00'));
ifdate(now())='2014-04-01'then
setcur_time=unix_timestamp(date_format(date_add(now(),interval1day),'%y-%m-%d 00:00:00'));
endif;
set@__interval=1;
create_loop: loop
if@__interval>create_next_intervalsthen
leave create_loop;
endif;
setless_than_timestamp=cur_time+(hourly_interval*@__interval*3600);
setpartition_name=from_unixtime(cur_time+hourly_interval*(@__interval-1)*3600,'p%y%m%d%h00');
callpartition_create(schema_name,table_name,partition_name,less_than_timestamp);
set@__interval=@__interval+1;
endloop;
setolder_than_partition_date=date_format(date_sub(now(),intervalkeep_data_daysday),'%y%m%d0000');
callpartition_drop(schema_name,table_name,older_than_partition_date);
end$$
delimiter ;
分区校验的存储过程
delimiter $$
createprocedure`partition_verify`(schemanamevarchar(64),tablenamevarchar(64),hourlyintervalint(11))
begin
declarepartition_namevarchar(16);
declareretrowsint(11);
declarefuture_timestamptimestamp;
/** check if any partitions exist for the given schemaname.tablename. */
selectcount(1)intoretrows
frominformation_schema.partitions
wheretable_schema=schemanameandtable_name=tablenameandpartition_nameisnull;
/* * if partitions do not exist, go ahead and partition the table*/
ifretrows=1then
/*
* take the current date at 00:00:00 and add hourlyinterval to it. this is the timestamp below which we will store values.
* we begin partitioning based on the beginning of a day. this is because we don't want to generate a random partition
* that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could
* end up creating a partition now named p201403270600 when all other partitions will be like p201403280000).
*/
setfuture_timestamp=timestampadd(hour,hourlyinterval,concat(curdate(), ,'00:00:00'));
setpartition_name=date_format(curdate(),'p%y%m%d%h00');
-- create the partitioning query
set@__partition_sql=concat(alter table ,schemaname,.,tablename, partition by range(`clock`));
set@__partition_sql=concat(@__partition_sql,(partition ,partition_name, values less than (,unix_timestamp(future_timestamp),)););
-- run the partitioning query
preparestmtfrom@__partition_sql;
executestmt;
deallocatepreparestmt;
endif;
end$$
delimiter ;
使用存储过程mysql>callpartition_maintenance('','',,,)
例如，zabbix.history保存28天，表区间的时间为24小时，预留14天的区间。
mysql> call partition_maintenance('zabbix', 'history', 28, 24, 14);+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405070000,1399478400) |+-----------------------------------------------------------+1 row in set (18.75 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405080000,1399564800) |+-----------------------------------------------------------+1 row in set (19.08 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405090000,1399651200) |+-----------------------------------------------------------+1 row in set (19.16 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405100000,1399737600) |+-----------------------------------------------------------+1 row in set (19.27 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405110000,1399824000) |+-----------------------------------------------------------+1 row in set (19.42 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405120000,1399910400) |+-----------------------------------------------------------+1 row in set (19.52 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405130000,1399996800) |+-----------------------------------------------------------+1 row in set (19.63 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405140000,1400083200) |+-----------------------------------------------------------+1 row in set (19.89 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405150000,1400169600) |+-----------------------------------------------------------+1 row in set (20.00 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405160000,1400256000) |+-----------------------------------------------------------+1 row in set (20.07 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405170000,1400342400) |+-----------------------------------------------------------+1 row in set (20.13 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405180000,1400428800) |+-----------------------------------------------------------+1 row in set (20.20 sec)+-----------------------------------------------------------+| msg |+-----------------------------------------------------------+| partition_create(zabbix,history,p201405190000,1400515200) |+-----------------------------------------------------------+1 row in set (20.31 sec)+----------------+--------------------+| table| partitions_deleted |+----------------+--------------------+| zabbix.history | n/a|+----------------+--------------------+1 row in set (20.42 sec)query ok, 0 rows affected (20.42 sec)
创建存储过程delimiter $$create procedure `partition_maintenance_all`(schema_name varchar(32))begin call partition_maintenance(schema_name, 'history', 28, 24, 14); call partition_maintenance(schema_name, 'history_log', 28, 24, 14); call partition_maintenance(schema_name, 'history_str', 28, 24, 14); call partition_maintenance(schema_name, 'history_text', 28, 24, 14); call partition_maintenance(schema_name, 'history_uint', 28, 24, 14); call partition_maintenance(schema_name, 'trends', 730, 24, 14); call partition_maintenance(schema_name, 'trends_uint', 730, 24, 14);end$$delimiter ;
调用存储过程mysql> call partition_maintenance_all('zabbix');+----------------+--------------------+| table| partitions_deleted |+----------------+--------------------+| zabbix.history | n/a|+----------------+--------------------+1 row in set (0.01 sec)............+--------------------+--------------------+| table| partitions_deleted |+--------------------+--------------------+| zabbix.trends_uint | n/a|+--------------------+--------------------+1 row in set (22.41 sec)query ok, 0 rows affected, 1 warning (22.41 sec)mysql>
查看表结构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=utf8/*!50100 partition by range (`clock`)(partition p201405060000 values less than (1399392000) engine = innodb, partition p201405070000 values less than (1399478400) engine = innodb, partition p201405080000 values less than (1399564800) engine = innodb, partition p201405090000 values less than (1399651200) engine = innodb, partition p201405100000 values less than (1399737600) engine = innodb, partition p201405110000 values less than (1399824000) engine = innodb, partition p201405120000 values less than (1399910400) engine = innodb, partition p201405130000 values less than (1399996800) engine = innodb, partition p201405140000 values less than (1400083200) engine = innodb, partition p201405150000 values less than (1400169600) engine = innodb, partition p201405160000 values less than (1400256000) engine = innodb, partition p201405170000 values less than (1400342400) engine = innodb, partition p201405180000 values less than (1400428800) engine = innodb, partition p201405190000 values less than (1400515200) engine = innodb) */
添加定时任务
1 1 * * * mysql-uzabbix -pzabbix zabbix -e call partition_maintenance_all('zabbix')
参考文档
本文参考https://www.zabbix.org/wiki/docs/howto/mysql_partition写成。