首页  :: 新随笔  :: 管理

ZABBIX之MySQL数据库历史表优化

Posted on 2022-07-31 08:47  高&玉  阅读(371)  评论(0)  编辑  收藏  举报

前言

  手上有一套ZABBIX监控上线后，没特意关注过数据库，没想到两年不到的时间数据量增长到了500G，造成磁盘空间。

方案1：DELETE删除旧数据，周期太长了，而且空间释放不及时，optimize操作也费时间。

方案2：将体积大的表改造成分区表，将部分历史数据导入新表中，后期分区表删除分区释放空间更有优势。

1. 体积大的表

TABLE_NAME TABLE_SIZE
trends 4757MB
trends_uint 12885MB
history_str 26045MB
history_text 117125MB
history 151793MB
history_uint 296685MB

注释：

  这几个表存储的是历史数据，根据情况决定是否保留。

2.关闭zabbix-server

[root]# systemctl stop zabbix-server

3. 重命名历史表

alter table trends       rename to trends_20220730;
alter table trends_uint  rename to trends_uint_20220730;
alter table history_str  rename to history_str_20220730;
alter table history_text rename to history_text_20220730;
alter table history      rename to history_20220730;
alter table history_uint rename to history_uint_20220730;

4. 新建历史表  

创建trends：

CREATE TABLE `trends` (
  `itemid` bigint unsigned NOT NULL,
  `clock` int NOT NULL DEFAULT '0',
  `num` int 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=utf8mb3 COLLATE=utf8_bin
PARTITION BY RANGE (`clock`)
(PARTITION p202107 VALUES LESS THAN (1627747200) ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN (1630425600) ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN (1633017600) ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN (1635696000) ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN (1638288000) ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN (1640966400) ENGINE = InnoDB,
 PARTITION p202201 VALUES LESS THAN (1643644800) ENGINE = InnoDB,
 PARTITION p202202 VALUES LESS THAN (1646064000) ENGINE = InnoDB,
 PARTITION p202203 VALUES LESS THAN (1648742400) ENGINE = InnoDB,
 PARTITION p202204 VALUES LESS THAN (1651334400) ENGINE = InnoDB,
 PARTITION p202205 VALUES LESS THAN (1654012800) ENGINE = InnoDB,
 PARTITION p202206 VALUES LESS THAN (1656604800) ENGINE = InnoDB,
 PARTITION p202207 VALUES LESS THAN (1659283200) ENGINE = InnoDB,
 PARTITION p202208 VALUES LESS THAN (1661961600) ENGINE = InnoDB,
 PARTITION p202209 VALUES LESS THAN (1664553600) ENGINE = InnoDB,
 PARTITION p202210 VALUES LESS THAN (1667232000) ENGINE = InnoDB);

 创建trends_uint：

CREATE TABLE `trends_uint` (
  `itemid` bigint unsigned NOT NULL,
  `clock` int NOT NULL DEFAULT '0',
  `num` int NOT NULL DEFAULT '0',
  `value_min` bigint unsigned NOT NULL DEFAULT '0',
  `value_avg` bigint unsigned NOT NULL DEFAULT '0',
  `value_max` bigint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin
PARTITION BY RANGE (`clock`)
(PARTITION p202107 VALUES LESS THAN (1627747200) ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN (1630425600) ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN (1633017600) ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN (1635696000) ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN (1638288000) ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN (1640966400) ENGINE = InnoDB,
 PARTITION p202201 VALUES LESS THAN (1643644800) ENGINE = InnoDB,
 PARTITION p202202 VALUES LESS THAN (1646064000) ENGINE = InnoDB,
 PARTITION p202203 VALUES LESS THAN (1648742400) ENGINE = InnoDB,
 PARTITION p202204 VALUES LESS THAN (1651334400) ENGINE = InnoDB,
 PARTITION p202205 VALUES LESS THAN (1654012800) ENGINE = InnoDB,
 PARTITION p202206 VALUES LESS THAN (1656604800) ENGINE = InnoDB,
 PARTITION p202207 VALUES LESS THAN (1659283200) ENGINE = InnoDB,
 PARTITION p202208 VALUES LESS THAN (1661961600) ENGINE = InnoDB,
 PARTITION p202209 VALUES LESS THAN (1664553600) ENGINE = InnoDB,
 PARTITION p202210 VALUES LESS THAN (1667232000) ENGINE = InnoDB);

创建history_str：

CREATE TABLE `history_str` (
  `itemid` bigint unsigned NOT NULL,
  `clock` int NOT NULL DEFAULT '0',
  `value` varchar(255) COLLATE utf8_bin NOT NULL,
  `ns` int NOT NULL DEFAULT '0',
  KEY `history_str_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin
PARTITION BY RANGE (`clock`)
(PARTITION p20220630 VALUES LESS THAN (1656604800) ENGINE = InnoDB,
 PARTITION p20220701 VALUES LESS THAN (1656691200) ENGINE = InnoDB,
 PARTITION p20220702 VALUES LESS THAN (1656777600) ENGINE = InnoDB,
 PARTITION p20220703 VALUES LESS THAN (1656864000) ENGINE = InnoDB,
 PARTITION p20220704 VALUES LESS THAN (1656950400) ENGINE = InnoDB,
 PARTITION p20220705 VALUES LESS THAN (1657036800) ENGINE = InnoDB,
 PARTITION p20220706 VALUES LESS THAN (1657123200) ENGINE = InnoDB,
 PARTITION p20220707 VALUES LESS THAN (1657209600) ENGINE = InnoDB,
 PARTITION p20220708 VALUES LESS THAN (1657296000) ENGINE = InnoDB,
 PARTITION p20220709 VALUES LESS THAN (1657382400) ENGINE = InnoDB,
 PARTITION p20220710 VALUES LESS THAN (1657468800) ENGINE = InnoDB,
 PARTITION p20220711 VALUES LESS THAN (1657555200) ENGINE = InnoDB,
 PARTITION p20220712 VALUES LESS THAN (1657641600) ENGINE = InnoDB,
 PARTITION p20220713 VALUES LESS THAN (1657728000) ENGINE = InnoDB,
 PARTITION p20220714 VALUES LESS THAN (1657814400) ENGINE = InnoDB,
 PARTITION p20220715 VALUES LESS THAN (1657900800) ENGINE = InnoDB,
 PARTITION p20220716 VALUES LESS THAN (1657987200) ENGINE = InnoDB,
 PARTITION p20220717 VALUES LESS THAN (1658073600) ENGINE = InnoDB,
 PARTITION p20220718 VALUES LESS THAN (1658160000) ENGINE = InnoDB,
 PARTITION p20220719 VALUES LESS THAN (1658246400) ENGINE = InnoDB,
 PARTITION p20220720 VALUES LESS THAN (1658332800) ENGINE = InnoDB,
 PARTITION p20220721 VALUES LESS THAN (1658419200) ENGINE = InnoDB,
 PARTITION p20220722 VALUES LESS THAN (1658505600) ENGINE = InnoDB,
 PARTITION p20220723 VALUES LESS THAN (1658592000) ENGINE = InnoDB,
 PARTITION p20220724 VALUES LESS THAN (1658678400) ENGINE = InnoDB,
 PARTITION p20220725 VALUES LESS THAN (1658764800) ENGINE = InnoDB,
 PARTITION p20220726 VALUES LESS THAN (1658851200) ENGINE = InnoDB,
 PARTITION p20220727 VALUES LESS THAN (1658937600) ENGINE = InnoDB,
 PARTITION p20220728 VALUES LESS THAN (1659024000) ENGINE = InnoDB,
 PARTITION p20220729 VALUES LESS THAN (1659110400) ENGINE = InnoDB,
 PARTITION p20220730 VALUES LESS THAN (1659196800) ENGINE = InnoDB,
 PARTITION p20220731 VALUES LESS THAN (1659283200) ENGINE = InnoDB,
 PARTITION p20220801 VALUES LESS THAN (1659369600) ENGINE = InnoDB,
 PARTITION p20220802 VALUES LESS THAN (1659456000) ENGINE = InnoDB,
 PARTITION p20220803 VALUES LESS THAN (1659542400) ENGINE = InnoDB,
 PARTITION p20220804 VALUES LESS THAN (1659628800) ENGINE = InnoDB,
 PARTITION p20220805 VALUES LESS THAN (1659715200) ENGINE = InnoDB,
 PARTITION p20220806 VALUES LESS THAN (1659801600) ENGINE = InnoDB);

创建history_text：

CREATE TABLE `history_text` (
  `itemid` bigint unsigned NOT NULL,
  `clock` int NOT NULL DEFAULT '0',
  `value` text COLLATE utf8_bin NOT NULL,
  `ns` int NOT NULL DEFAULT '0',
  KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin
PARTITION BY RANGE (`clock`)
(PARTITION p20220630 VALUES LESS THAN (1656604800) ENGINE = InnoDB,
 PARTITION p20220701 VALUES LESS THAN (1656691200) ENGINE = InnoDB,
 PARTITION p20220702 VALUES LESS THAN (1656777600) ENGINE = InnoDB,
 PARTITION p20220703 VALUES LESS THAN (1656864000) ENGINE = InnoDB,
 PARTITION p20220704 VALUES LESS THAN (1656950400) ENGINE = InnoDB,
 PARTITION p20220705 VALUES LESS THAN (1657036800) ENGINE = InnoDB,
 PARTITION p20220706 VALUES LESS THAN (1657123200) ENGINE = InnoDB,
 PARTITION p20220707 VALUES LESS THAN (1657209600) ENGINE = InnoDB,
 PARTITION p20220708 VALUES LESS THAN (1657296000) ENGINE = InnoDB,
 PARTITION p20220709 VALUES LESS THAN (1657382400) ENGINE = InnoDB,
 PARTITION p20220710 VALUES LESS THAN (1657468800) ENGINE = InnoDB,
 PARTITION p20220711 VALUES LESS THAN (1657555200) ENGINE = InnoDB,
 PARTITION p20220712 VALUES LESS THAN (1657641600) ENGINE = InnoDB,
 PARTITION p20220713 VALUES LESS THAN (1657728000) ENGINE = InnoDB,
 PARTITION p20220714 VALUES LESS THAN (1657814400) ENGINE = InnoDB,
 PARTITION p20220715 VALUES LESS THAN (1657900800) ENGINE = InnoDB,
 PARTITION p20220716 VALUES LESS THAN (1657987200) ENGINE = InnoDB,
 PARTITION p20220717 VALUES LESS THAN (1658073600) ENGINE = InnoDB,
 PARTITION p20220718 VALUES LESS THAN (1658160000) ENGINE = InnoDB,
 PARTITION p20220719 VALUES LESS THAN (1658246400) ENGINE = InnoDB,
 PARTITION p20220720 VALUES LESS THAN (1658332800) ENGINE = InnoDB,
 PARTITION p20220721 VALUES LESS THAN (1658419200) ENGINE = InnoDB,
 PARTITION p20220722 VALUES LESS THAN (1658505600) ENGINE = InnoDB,
 PARTITION p20220723 VALUES LESS THAN (1658592000) ENGINE = InnoDB,
 PARTITION p20220724 VALUES LESS THAN (1658678400) ENGINE = InnoDB,
 PARTITION p20220725 VALUES LESS THAN (1658764800) ENGINE = InnoDB,
 PARTITION p20220726 VALUES LESS THAN (1658851200) ENGINE = InnoDB,
 PARTITION p20220727 VALUES LESS THAN (1658937600) ENGINE = InnoDB,
 PARTITION p20220728 VALUES LESS THAN (1659024000) ENGINE = InnoDB,
 PARTITION p20220729 VALUES LESS THAN (1659110400) ENGINE = InnoDB,
 PARTITION p20220730 VALUES LESS THAN (1659196800) ENGINE = InnoDB,
 PARTITION p20220731 VALUES LESS THAN (1659283200) ENGINE = InnoDB,
 PARTITION p20220801 VALUES LESS THAN (1659369600) ENGINE = InnoDB,
 PARTITION p20220802 VALUES LESS THAN (1659456000) ENGINE = InnoDB,
 PARTITION p20220803 VALUES LESS THAN (1659542400) ENGINE = InnoDB,
 PARTITION p20220804 VALUES LESS THAN (1659628800) ENGINE = InnoDB,
 PARTITION p20220805 VALUES LESS THAN (1659715200) ENGINE = InnoDB,
 PARTITION p20220806 VALUES LESS THAN (1659801600) ENGINE = InnoDB);

创建history：

CREATE TABLE `history` (
  `itemid` bigint unsigned NOT NULL,
  `clock` int NOT NULL DEFAULT '0',
  `value` double(16,4) NOT NULL DEFAULT '0.0000',
  `ns` int NOT NULL DEFAULT '0',
  KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin
PARTITION BY RANGE (`clock`)
(PARTITION p20220630 VALUES LESS THAN (1656604800) ENGINE = InnoDB,
 PARTITION p20220701 VALUES LESS THAN (1656691200) ENGINE = InnoDB,
 PARTITION p20220702 VALUES LESS THAN (1656777600) ENGINE = InnoDB,
 PARTITION p20220703 VALUES LESS THAN (1656864000) ENGINE = InnoDB,
 PARTITION p20220704 VALUES LESS THAN (1656950400) ENGINE = InnoDB,
 PARTITION p20220705 VALUES LESS THAN (1657036800) ENGINE = InnoDB,
 PARTITION p20220706 VALUES LESS THAN (1657123200) ENGINE = InnoDB,
 PARTITION p20220707 VALUES LESS THAN (1657209600) ENGINE = InnoDB,
 PARTITION p20220708 VALUES LESS THAN (1657296000) ENGINE = InnoDB,
 PARTITION p20220709 VALUES LESS THAN (1657382400) ENGINE = InnoDB,
 PARTITION p20220710 VALUES LESS THAN (1657468800) ENGINE = InnoDB,
 PARTITION p20220711 VALUES LESS THAN (1657555200) ENGINE = InnoDB,
 PARTITION p20220712 VALUES LESS THAN (1657641600) ENGINE = InnoDB,
 PARTITION p20220713 VALUES LESS THAN (1657728000) ENGINE = InnoDB,
 PARTITION p20220714 VALUES LESS THAN (1657814400) ENGINE = InnoDB,
 PARTITION p20220715 VALUES LESS THAN (1657900800) ENGINE = InnoDB,
 PARTITION p20220716 VALUES LESS THAN (1657987200) ENGINE = InnoDB,
 PARTITION p20220717 VALUES LESS THAN (1658073600) ENGINE = InnoDB,
 PARTITION p20220718 VALUES LESS THAN (1658160000) ENGINE = InnoDB,
 PARTITION p20220719 VALUES LESS THAN (1658246400) ENGINE = InnoDB,
 PARTITION p20220720 VALUES LESS THAN (1658332800) ENGINE = InnoDB,
 PARTITION p20220721 VALUES LESS THAN (1658419200) ENGINE = InnoDB,
 PARTITION p20220722 VALUES LESS THAN (1658505600) ENGINE = InnoDB,
 PARTITION p20220723 VALUES LESS THAN (1658592000) ENGINE = InnoDB,
 PARTITION p20220724 VALUES LESS THAN (1658678400) ENGINE = InnoDB,
 PARTITION p20220725 VALUES LESS THAN (1658764800) ENGINE = InnoDB,
 PARTITION p20220726 VALUES LESS THAN (1658851200) ENGINE = InnoDB,
 PARTITION p20220727 VALUES LESS THAN (1658937600) ENGINE = InnoDB,
 PARTITION p20220728 VALUES LESS THAN (1659024000) ENGINE = InnoDB,
 PARTITION p20220729 VALUES LESS THAN (1659110400) ENGINE = InnoDB,
 PARTITION p20220730 VALUES LESS THAN (1659196800) ENGINE = InnoDB,
 PARTITION p20220731 VALUES LESS THAN (1659283200) ENGINE = InnoDB);

创建history_uint:

CREATE TABLE `history_uint` (
  `itemid` bigint unsigned NOT NULL,
  `clock` int NOT NULL DEFAULT '0',
  `value` bigint unsigned NOT NULL DEFAULT '0',
  `ns` int NOT NULL DEFAULT '0',
  KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin
PARTITION BY RANGE (`clock`)
(PARTITION p20220630 VALUES LESS THAN (1656604800) ENGINE = InnoDB,
 PARTITION p20220701 VALUES LESS THAN (1656691200) ENGINE = InnoDB,
 PARTITION p20220702 VALUES LESS THAN (1656777600) ENGINE = InnoDB,
 PARTITION p20220703 VALUES LESS THAN (1656864000) ENGINE = InnoDB,
 PARTITION p20220704 VALUES LESS THAN (1656950400) ENGINE = InnoDB,
 PARTITION p20220705 VALUES LESS THAN (1657036800) ENGINE = InnoDB,
 PARTITION p20220706 VALUES LESS THAN (1657123200) ENGINE = InnoDB,
 PARTITION p20220707 VALUES LESS THAN (1657209600) ENGINE = InnoDB,
 PARTITION p20220708 VALUES LESS THAN (1657296000) ENGINE = InnoDB,
 PARTITION p20220709 VALUES LESS THAN (1657382400) ENGINE = InnoDB,
 PARTITION p20220710 VALUES LESS THAN (1657468800) ENGINE = InnoDB,
 PARTITION p20220711 VALUES LESS THAN (1657555200) ENGINE = InnoDB,
 PARTITION p20220712 VALUES LESS THAN (1657641600) ENGINE = InnoDB,
 PARTITION p20220713 VALUES LESS THAN (1657728000) ENGINE = InnoDB,
 PARTITION p20220714 VALUES LESS THAN (1657814400) ENGINE = InnoDB,
 PARTITION p20220715 VALUES LESS THAN (1657900800) ENGINE = InnoDB,
 PARTITION p20220716 VALUES LESS THAN (1657987200) ENGINE = InnoDB,
 PARTITION p20220717 VALUES LESS THAN (1658073600) ENGINE = InnoDB,
 PARTITION p20220718 VALUES LESS THAN (1658160000) ENGINE = InnoDB,
 PARTITION p20220719 VALUES LESS THAN (1658246400) ENGINE = InnoDB,
 PARTITION p20220720 VALUES LESS THAN (1658332800) ENGINE = InnoDB,
 PARTITION p20220721 VALUES LESS THAN (1658419200) ENGINE = InnoDB,
 PARTITION p20220722 VALUES LESS THAN (1658505600) ENGINE = InnoDB,
 PARTITION p20220723 VALUES LESS THAN (1658592000) ENGINE = InnoDB,
 PARTITION p20220724 VALUES LESS THAN (1658678400) ENGINE = InnoDB,
 PARTITION p20220725 VALUES LESS THAN (1658764800) ENGINE = InnoDB,
 PARTITION p20220726 VALUES LESS THAN (1658851200) ENGINE = InnoDB,
 PARTITION p20220727 VALUES LESS THAN (1658937600) ENGINE = InnoDB,
 PARTITION p20220728 VALUES LESS THAN (1659024000) ENGINE = InnoDB,
 PARTITION p20220729 VALUES LESS THAN (1659110400) ENGINE = InnoDB,
 PARTITION p20220730 VALUES LESS THAN (1659196800) ENGINE = InnoDB,
 PARTITION p20220731 VALUES LESS THAN (1659283200) ENGINE = InnoDB,
 PARTITION p20220801 VALUES LESS THAN (1659369600) ENGINE = InnoDB,
 PARTITION p20220802 VALUES LESS THAN (1659456000) ENGINE = InnoDB,
 PARTITION p20220803 VALUES LESS THAN (1659542400) ENGINE = InnoDB,
 PARTITION p20220804 VALUES LESS THAN (1659628800) ENGINE = InnoDB,
 PARTITION p20220805 VALUES LESS THAN (1659715200) ENGINE = InnoDB,
 PARTITION p20220806 VALUES LESS THAN (1659801600) ENGINE = InnoDB);

5. 启动zabbix-server

[root]# systemctl start zabbix-server

6. 导入部分历史数据

trends表导入2022年7月23日之后的历史数据：

insert into trends       select * from trends_20220730       where clock > 1658505600;

 trends_uint表导入2022年7月23日之后的历史数据：

insert into trends_uint  select * from trends_uint_20220730  where clock > 1658505600;

history_str表导入2022年7月30日之后的历史数据：

[mysql]# mysqldump --login-path=root_login zabbix history_str_20220730 --where='clock > 1659110400' --single-transaction --set-gtid-purged=off --skip-extended-insert --no-create-info --skip-lock-tables --compact >> history_str_20220730.sql
[mysql]# sed -i "s#history_str_20220730#history_str#" history_str_20220730.sql
[mysql]# mysql --login-path=root_login zabbix < history_str_20220730.sql

history_text表导入2022年7月30日之后的历史数据：

[mysql]# mysqldump --login-path=root_login zabbix history_text_20220730 --where='clock > 1659110400' --single-transaction --set-gtid-purged=off --skip-extended-insert --no-create-info --skip-lock-tables --compact >> history_text_20220730.sql
[mysql]# sed -i "s#history_text_20220730#history_text#" history_text_20220730.sql
[mysql]# mysql --login-path=root_login zabbix < history_text_20220730.sql

history表导入2022年7月30日之后的历史数据：

[mysql]# mysqldump --login-path=root_login zabbix history_20220730 --where='clock > 1659110400' --single-transaction --set-gtid-purged=off --skip-extended-insert --no-create-info --skip-lock-tables --compact >> history_20220730.sql
[mysql]# sed -i "s#history_20220730#history#" history_20220730.sql
[mysql]# mysql --login-path=root_login zabbix < history_20220730.sql

history_uint表导入2022年7月30日之后的历史数据：

[mysql]# mysqldump --login-path=root_login zabbix history_uint_20220730 --where='clock > 1659110400' --single-transaction --set-gtid-purged=off --skip-extended-insert --no-create-info --skip-lock-tables --compact >> history_uint_20220730.sql
[mysql]# sed -i "s#history_uint_20220730#history_uint#" history_uint_20220730.sql
[mysql]# mysql --login-path=root_login zabbix < history_uint_20220730.sql

6. 创建维护历史的procedure

创建7个PROCEDURE：

  • create_next_monthly_partitions
  • create_next_partitions        
  • create_partition              
  • create_zabbix_partitions      
  • drop_old_monthly_partitions  
  • drop_old_partitions          
  • drop_partition                

 

create_procedure.sql

查看代码 
 DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_next_monthly_partitions`(SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
	DECLARE NEXTCLOCK timestamp;
	DECLARE PARTITIONNAME varchar(16);
	DECLARE CLOCK int;
	SET @totalmonths = 3;
	SET @i = 1;
	createloop: LOOP
		SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i MONTH);
		SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m' );
		SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 MONTH),'%Y-%m-01 00:00:00'));
		CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
		SET @i=@i+1;
		IF @i > @totalmonths THEN
			LEAVE createloop;
		END IF;
	END LOOP;
END ;;
DELIMITER ;

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_next_partitions`(SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
	DECLARE NEXTCLOCK timestamp;
	DECLARE PARTITIONNAME varchar(16);
	DECLARE CLOCK int;
	SET @totaldays = 7;
	SET @i = 1;
	createloop: LOOP
		SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY);
		SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );
		SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));
		CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
		SET @i=@i+1;
		IF @i > @totaldays THEN
			LEAVE createloop;
		END IF;
	END LOOP;
END ;;
DELIMITER ;

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_partition`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
BEGIN
	DECLARE RETROWS int;
	SELECT COUNT(1) INTO RETROWS
		FROM information_schema.partitions
		WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME;

	IF RETROWS = 0 THEN
		SELECT CONCAT( "create_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
     		SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, 
				' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
		PREPARE STMT FROM @sql;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
	END IF;
END ;;
DELIMITER ;

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_zabbix_partitions`()
BEGIN
	CALL zabbix.create_next_partitions("zabbix","history");
	CALL zabbix.drop_old_partitions("zabbix","history");

	CALL zabbix.create_next_partitions("zabbix","history_str");
	CALL zabbix.drop_old_partitions("zabbix","history_str");

	CALL zabbix.create_next_partitions("zabbix","history_text");
	CALL zabbix.drop_old_partitions("zabbix","history_text");

	CALL zabbix.create_next_partitions("zabbix","history_uint");
	CALL zabbix.drop_old_partitions("zabbix","history_uint");

	CALL zabbix.create_next_monthly_partitions("zabbix","trends");
	CALL zabbix.drop_old_monthly_partitions("zabbix","trends");
	
	CALL zabbix.create_next_monthly_partitions("zabbix","trends_uint");
	CALL zabbix.drop_old_monthly_partitions("zabbix","trends_uint");
END ;;
DELIMITER ;

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `drop_old_monthly_partitions`(SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
	DECLARE OLDCLOCK timestamp;
	DECLARE PARTITIONNAME varchar(16);
	DECLARE CLOCK int;
	SET @minmonths = 12;
	SET @maxmonths = @minmonths+24;
	SET @i = @maxmonths;
	droploop: LOOP
		SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH);
		SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m' );
		CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
		SET @i=@i-1;
		IF @i <= @minmonths THEN
			LEAVE droploop;
		END IF;
	END LOOP;
END ;;
DELIMITER ;

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `drop_old_partitions`(SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
	DECLARE OLDCLOCK timestamp;
	DECLARE PARTITIONNAME varchar(16);
	DECLARE CLOCK int;
	SET @mindays = 30;
	SET @maxdays = @mindays+4;
	SET @i = @maxdays;
	droploop: LOOP
		SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);
		SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
		CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
		SET @i=@i-1;
		IF @i <= @mindays THEN
			LEAVE droploop;
		END IF;
	END LOOP;
END ;;
DELIMITER ;

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `drop_partition`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64))
BEGIN
	DECLARE RETROWS int;
	SELECT COUNT(1) INTO RETROWS
		FROM information_schema.partitions
		WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME;

	IF RETROWS = 1 THEN
		SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
     		SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME,
				' DROP PARTITION ', PARTITIONNAME, ';' );
		PREPARE STMT FROM @sql;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
	END IF;
END ;;
DELIMITER ;

 

创建PROCEDURE

[mysql]# mysql --login-path=root_login zabbix < create_procedure.sql

 

 创建执行计划

[root]# vi /usr/local/zabbix/cron.d/housekeeping.sh
#!/bin/bash

echo "======================================================================================================================================================================================" >> /tmp/zabbix_db_partition.log 2>&1
date +%F\ %T >> /tmp/zabbix_db_partition.log 2>&1 
/usr/local/mysql/bin/mysql --skip-column-names -B --login-path=root_login zabbix -e "CALL create_zabbix_partitions();" >> /tmp/zabbix_db_partition.log 2>&1

[root]# vi /etc/cron.daily/zabbixhousekeeping
#!/bin/bash
/usr/local/zabbix/cron.d/housekeeping.sh

 7. 清理旧历史表

truncate table trends_20220730;
truncate table trends_uint_20220730;
truncate table history_str_20220730;
truncate table history_text_20220730;
truncate table history_20220730;
truncate table history_uint_20220730;

 truncate旧表后，磁盘空间会立刻得到释放。