zabbix5.0 创建分区表
备注：zabbix已经运行一段时间，history、history_uint等表已经达到100G左右，需要将这几张表删除（原来没有创建带有分区功能的表），这些数据是历史监控数据（删除需要谨慎），我们是把这几张表删除了，不做保留，数据可以不要。
mysql分区表概述
分区表分为四种：
range分区：基于属于一个给定连续区间的列值，把多行分配给分区
list分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
hash分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算
key分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列
常用的是range和list分区
无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。
注1：
如果想在已经建好的表上进行分区，如果使用alter添加分区的话，mysql会提示错误：
ERROR 1505 <HY000> Partition management on a not partitioned table is not possible
正确的方法是新建一个具有分区的表，结构一致，然后用insert into 分区表 select * from 原始表;
记录的大致过程
- 停止zabbix-server
- 记录下要修改的表的表结构
- 删除表
- 创建和之前表结构一致但带有分区表的数据表
- 创建procedure，修改crontab
操作记录
如果只做历史表的每日分割，删除如下表
history
history_uint
history_log
history_str
history_text
history_uint：该表存储的是监控项的无符号整型的数据。该数据的保存时长，取决于在监控项设置的 历史数据保留时长
history_str：等保存的是 字符型数据。这些都是我们在设置监控项的对应的信息类型决定的。该数据的保存时长，取决于在监控项设置的 历史数据保留时长。
trends：是保存了趋势数据用的，和 history 不同的是，trends 表仅仅保存了小时平均的值。所以 trends 表也有很多的类型，对应history
记录原表的创建sql
MariaDB [zabbix]> show create table zabbix.history\G show create table zabbix.history_uint\G show create table zabbix.history_log\G show create table zabbix.history_str\G show create table zabbix.history_text\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 NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
*************************** 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 COLLATE=utf8_bin
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: history_log
Create Table: CREATE TABLE `history_log` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`timestamp` int(11) NOT NULL DEFAULT '0',
`source` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
`value` text COLLATE utf8_bin NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
*************************** 1. row ***************************
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) COLLATE utf8_bin NOT NULL DEFAULT '',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_str_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: history_text
Create Table: CREATE TABLE `history_text` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` text COLLATE utf8_bin NOT NULL,
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
删除之前的表和创建带有分区的表
# ---------history-----------
truncate table history;
drop table history;
CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
partition by range(clock) (
PARTITION p20220424 VALUES LESS THAN (UNIX_TIMESTAMP("2022-04-24 00:00:00"))
);
show create table history;
# -----------history_uint--------------
truncate table history_uint;
drop table history_uint;
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 COLLATE=utf8_bin
partition by range(clock) (
PARTITION p20220424 VALUES LESS THAN (UNIX_TIMESTAMP("2022-04-24 00:00:00"))
);
show create table history_uint;
# -----------history_log----------
truncate table history_log;
drop table history_log;
CREATE TABLE `history_log` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`timestamp` int(11) NOT NULL DEFAULT '0',
`source` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
`value` text COLLATE utf8_bin NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
partition by range(clock) (
PARTITION p20220424 VALUES LESS THAN (UNIX_TIMESTAMP("2022-04-24 00:00:00"))
);
show create table history_log;
# ------------history_str--------------
truncate table history_str;
drop table history_str;
CREATE TABLE `history_str` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_str_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
partition by range(clock) (
PARTITION p20220424 VALUES LESS THAN (UNIX_TIMESTAMP("2022-04-24 00:00:00"))
);
show create table history_str;
# ------------history_text----------------
truncate table history_text;
drop table history_text;
CREATE TABLE `history_text` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` text COLLATE utf8_bin NOT NULL,
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
partition by range(clock) (
PARTITION p20220424 VALUES LESS THAN (UNIX_TIMESTAMP("2022-04-24 00:00:00"))
);
show create table history_text;
创建分区函数
DELIMITER //
DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_partitions` //
CREATE PROCEDURE `zabbix`.`create_zabbix_partitions` ()
BEGIN
CALL zabbix.create_next_partitions("zabbix","history");
CALL zabbix.create_next_partitions("zabbix","history_log");
CALL zabbix.create_next_partitions("zabbix","history_str");
CALL zabbix.create_next_partitions("zabbix","history_text");
CALL zabbix.create_next_partitions("zabbix","history_uint");
CALL zabbix.drop_old_partitions("zabbix","history");
CALL zabbix.drop_old_partitions("zabbix","history_log");
CALL zabbix.drop_old_partitions("zabbix","history_str");
CALL zabbix.drop_old_partitions("zabbix","history_text");
CALL zabbix.drop_old_partitions("zabbix","history_uint");
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` //
CREATE PROCEDURE `zabbix`.`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 //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //
CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @mindays = 3;
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 //
DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` //
CREATE PROCEDURE `zabbix`.`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 //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_partition` //
CREATE PROCEDURE `zabbix`.`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 ;
添加计划任务
0 0 * * * /usr/bin/mysql --skip-column-names -h xxx -uroot '-pxxx' -B zabbix -e "CALL create_zabbix_partitions();
最终的形式
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16273
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show create table zabbix.history\G show create table zabbix.history_uint\G show create table zabbix.history_log\G show create table zabbix.history_str\G show create table zabbix.history_text\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 NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20220424 VALUES LESS THAN (1650729600) ENGINE = InnoDB,
PARTITION p20220425 VALUES LESS THAN (1650902400) ENGINE = InnoDB,
PARTITION p20220426 VALUES LESS THAN (1650988800) ENGINE = InnoDB,
PARTITION p20220427 VALUES LESS THAN (1651075200) ENGINE = InnoDB,
PARTITION p20220428 VALUES LESS THAN (1651161600) ENGINE = InnoDB,
PARTITION p20220429 VALUES LESS THAN (1651248000) ENGINE = InnoDB,
PARTITION p20220430 VALUES LESS THAN (1651334400) ENGINE = InnoDB,
PARTITION p20220501 VALUES LESS THAN (1651420800) ENGINE = InnoDB) */
1 row in set (0.01 sec)
*************************** 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 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20220424 VALUES LESS THAN (1650729600) ENGINE = InnoDB,
PARTITION p20220425 VALUES LESS THAN (1650902400) ENGINE = InnoDB,
PARTITION p20220426 VALUES LESS THAN (1650988800) ENGINE = InnoDB,
PARTITION p20220427 VALUES LESS THAN (1651075200) ENGINE = InnoDB,
PARTITION p20220428 VALUES LESS THAN (1651161600) ENGINE = InnoDB,
PARTITION p20220429 VALUES LESS THAN (1651248000) ENGINE = InnoDB,
PARTITION p20220430 VALUES LESS THAN (1651334400) ENGINE = InnoDB,
PARTITION p20220501 VALUES LESS THAN (1651420800) ENGINE = InnoDB) */
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: history_log
Create Table: CREATE TABLE `history_log` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`timestamp` int(11) NOT NULL DEFAULT '0',
`source` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
`value` text COLLATE utf8_bin NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20220424 VALUES LESS THAN (1650729600) ENGINE = InnoDB,
PARTITION p20220425 VALUES LESS THAN (1650902400) ENGINE = InnoDB,
PARTITION p20220426 VALUES LESS THAN (1650988800) ENGINE = InnoDB,
PARTITION p20220427 VALUES LESS THAN (1651075200) ENGINE = InnoDB,
PARTITION p20220428 VALUES LESS THAN (1651161600) ENGINE = InnoDB,
PARTITION p20220429 VALUES LESS THAN (1651248000) ENGINE = InnoDB,
PARTITION p20220430 VALUES LESS THAN (1651334400) ENGINE = InnoDB,
PARTITION p20220501 VALUES LESS THAN (1651420800) ENGINE = InnoDB) */
1 row in set (0.00 sec)
*************************** 1. row ***************************
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) COLLATE utf8_bin NOT NULL DEFAULT '',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_str_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20220424 VALUES LESS THAN (1650729600) ENGINE = InnoDB,
PARTITION p20220425 VALUES LESS THAN (1650902400) ENGINE = InnoDB,
PARTITION p20220426 VALUES LESS THAN (1650988800) ENGINE = InnoDB,
PARTITION p20220427 VALUES LESS THAN (1651075200) ENGINE = InnoDB,
PARTITION p20220428 VALUES LESS THAN (1651161600) ENGINE = InnoDB,
PARTITION p20220429 VALUES LESS THAN (1651248000) ENGINE = InnoDB,
PARTITION p20220430 VALUES LESS THAN (1651334400) ENGINE = InnoDB,
PARTITION p20220501 VALUES LESS THAN (1651420800) ENGINE = InnoDB) */
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: history_text
Create Table: CREATE TABLE `history_text` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` text COLLATE utf8_bin NOT NULL,
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20220424 VALUES LESS THAN (1650729600) ENGINE = InnoDB,
PARTITION p20220425 VALUES LESS THAN (1650902400) ENGINE = InnoDB,
PARTITION p20220426 VALUES LESS THAN (1650988800) ENGINE = InnoDB,
PARTITION p20220427 VALUES LESS THAN (1651075200) ENGINE = InnoDB,
PARTITION p20220428 VALUES LESS THAN (1651161600) ENGINE = InnoDB,
PARTITION p20220429 VALUES LESS THAN (1651248000) ENGINE = InnoDB,
PARTITION p20220430 VALUES LESS THAN (1651334400) ENGINE = InnoDB,
PARTITION p20220501 VALUES LESS THAN (1651420800) ENGINE = InnoDB) */
1 row in set (0.00 sec)
3483
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
