设为首页 收藏本站
查看: 4843|回复: 0

[经验分享] Zabbix优化之必杀技-表分区

[复制链接]
累计签到:1 天
连续签到:1 天
发表于 2014-5-6 10:35:57 | 显示全部楼层 |阅读模式


前言,使用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)历史数据的表
wKiom1NnzMfC4yFSAAHbz1mcSS8644.jpg
2)警告日志数据的表
wKioL1NnzMqgFW_VAAEfQOYP4V8474.jpg
History表结构
mysql> show create table history\G;
*************************** 1. row ***************************
       Table: history
Create 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
mysql> show create table history_str\G;
       Table: history_str
Create 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=utf8
mysql> show create table history_str_sync \G;
*************************** 1. row ***************************
       Table: history_str_sync
Create 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=utf8
mysql> show create table history_sync \G;
*************************** 1. row ***************************
       Table: history_sync
Create 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=utf8
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> 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> show create table history_uint \G;
*************************** 1. row ***************************
       Table: history_uint
Create 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=utf8
mysql> show create table history_uint_sync\G;
*************************** 1. row ***************************
       Table: history_uint_sync
Create 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: trends
Create 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=utf8
mysql> show create table trends_uint\G;
*************************** 1. row ***************************
       Table: trends_uint
Create 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: housekeeper
Create 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: housekeeper
Create 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
查看索引

1
mysql> show index from history\G;
如下表所示

wKioL1NnzhXhJpSzAANmptiQQDQ319.jpg
改变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`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64),PARTITIONNAME VARCHAR(64), CLOCK INT)
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
       *
DECLARE RETROWS INT;
SELECTCOUNT(1)INTO RETROWS
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME ANDTABLE_NAME= TABLENAME AND partition_name =PARTITIONNAME;
IF RETROWS =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.
               */
SELECT CONCAT("partition_create(", SCHEMANAME,",", TABLENAME,",", PARTITIONNAME,",", CLOCK,")")ASmsg;
SET @SQL= CONCAT('ALTER TABLE ', SCHEMANAME,'.', TABLENAME,' ADD PARTITION (PARTITION ',PARTITIONNAME,' VALUES LESS THAN (', CLOCK,'));');
PREPARE STMT FROM @SQL;
EXECUTE STMT;
               DEALLOCATE PREPARE STMT;
ENDIF;
END$$
DELIMITER ;
分区删除的存储过程
DELIMITER $$
CREATEPROCEDURE`partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64),DELETE_BELOW_PARTITION_DATE BIGINT)
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)
       */
DECLARE done INTDEFAULTFALSE;
DECLARE drop_part_name VARCHAR(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.
       */
DECLARE myCursor CURSOR FOR
SELECT partition_name
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME ANDTABLE_NAME= TABLENAME ANDCAST(SUBSTRING(partition_nameFROM2)ASUNSIGNED)< DELETE_BELOW_PARTITION_DATE;
DECLARE CONTINUE HANDLER FORNOT FOUND SET done =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.
       */
OPEN myCursor;
       read_loop: LOOP
               FETCH myCursor INTO drop_part_name;
IF done THEN
                       LEAVE read_loop;
ENDIF;
SET @drop_partitions =IF(@drop_partitions ="", drop_part_name, CONCAT(@drop_partitions,",", drop_part_name));
END LOOP;
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,";");
PREPARE STMT FROM @full_sql;
EXECUTE STMT;
               DEALLOCATE PREPARE STMT;
SELECT CONCAT(SCHEMANAME,".", TABLENAME)AS`table`, @drop_partitions AS`partitions_deleted`;
ELSE
               /*
                  No partitions are being deleted, so print out "N/A" (Not
applicable) to indicatethat no changes were made.
               */
SELECT CONCAT(SCHEMANAME,".", TABLENAME)AS`table`,"N/A"AS`partitions_deleted`;
ENDIF;
END$$
DELIMITER ;
分区维护的存储过程
DELIMITER $$
CREATEPROCEDURE`partition_maintenance`(SCHEMA_NAME VARCHAR(32),TABLE_NAMEVARCHAR(32),KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
BEGIN
DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);
DECLARE PARTITION_NAME VARCHAR(16);
DECLARE LESS_THAN_TIMESTAMP INT;
DECLARE CUR_TIME INT;
CALL partition_verify(SCHEMA_NAME,TABLE_NAME, HOURLY_INTERVAL);
SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00'));
IFDATE(NOW())='2014-04-01'THEN
SET CUR_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_INTERVALS THEN
                       LEAVE create_loop;
ENDIF;
SET LESS_THAN_TIMESTAMP = CUR_TIME +(HOURLY_INTERVAL * @__interval *3600);
SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL *(@__interval-1)*3600,'p%Y%m%d%H00');
CALL partition_create(SCHEMA_NAME,TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
SET @__interval=@__interval+1;
END LOOP;
SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(),INTERVAL KEEP_DATA_DAYSDAY),'%Y%m%d0000');
CALL partition_drop(SCHEMA_NAME,TABLE_NAME, OLDER_THAN_PARTITION_DATE);
END$$
DELIMITER ;
分区校验的存储过程
DELIMITER $$
CREATEPROCEDURE`partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))
BEGIN
DECLARE PARTITION_NAME VARCHAR(16);
DECLARE RETROWS INT(11);
DECLARE FUTURE_TIMESTAMP TIMESTAMP;
/** Check if any partitions exist for the given SCHEMANAME.TABLENAME.   */
SELECTCOUNT(1)INTO RETROWS
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME ANDTABLE_NAME= TABLENAME AND partition_name ISNULL;
/*  * If partitions do not exist, go ahead and partition the table*/
IF RETROWS =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").
      */
SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE()," ",'00:00:00'));
SET PARTITION_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
PREPARE STMT FROM @__PARTITION_SQL;
EXECUTE STMT;
               DEALLOCATE PREPARE STMT;
ENDIF;
END$$
DELIMITER ;

使用存储过程
mysql>CALL partition_maintenance('<zabbix_db_name>','<table_name>',<days_to_keep_data>,<hourly_interval>,<num_future_intervals_to_create>)

wKiom1Nnzsajen5QAADorNPzfrQ647.jpg
例如,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: history
Create 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')"
参考文档



运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-18745-1-1.html 上篇帖子: zabbix安装配置 下篇帖子: rhel 6.5 64bit 构筑zabbix2.2.2监控服务
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表