zabbix 大表在OB中实现表分区管理

2024年 7月 4日 63.4k 0

背景

把 zabbix 的数据库迁到 OB 之后,之前几个大表的表分区无法管理(OB中没有event),直到遇到了ODC平台(OB开发者中心),终于找到了如何在OB中自动管理表分区的方案。

本次就结合zabbix的几个大表做表分区管理的实践,与大家分享一下如何利用ODC来管理OB中的表分区。

因为我们原始表里已经有大量的数据,如果贸然对原始表去创建表分区的话,可能会造成长时间的缩影,所以,当前的整体思路是:

    1. 为每个要操作的表创建一个 [tb_name]---->临时表[tb_name_tmp],表结构语原始表保持一致;
    2. 通过代码为每个临时表 [tb_name_tmp]生成表分区创建语句;
    3. 为每个临时表 [tb_name_tmp]创建表分区;
    4. 将操作日前一天的数据通过 insert into select 写入到临时表 [tb_name_tmp]中,并校验
    5. 停止应用,并将剩余数据写入到临时表 [tb_name_tmp]中
    6. 将原始表[tb_name]重名为[tb_name_bak],并将临时表 [tb_name_tmp]重命名为[tb_name]
    7. 启动服务,并检查服务是否正常,如果服务异常,检查数据是否正确;
    8. 如果服务正常,在odc中为相关表创建表分区维护计划;
    9. 一段时间后,将原始表[tb_name_bak]进行归档或者移除;

操作对象

首先,明确需要操作的表对象是哪些:

表名 作用 保留时长 清理间隔 数据类型
history 存储原始的历史数据 90天 每天 数字(浮点数)
history_uint 存储原始的历史数据 90天 每天 数字(无符号)
history_str 存储原始的短字符串数据 90天 每天 字符型
history_text 存储原始的长字符串数据 90天 每天 文本
history_log 存储原始的日志字符串数据 90天 每天 日志
trends 存储每小时统计数据(趋势) 12个月 每月 数字(浮点数)
trends_uint 保持每小时统计数据(趋势) 12个月 每月 数字(无符号)
auditlog 审计日志表

准备操作

查找各个表中的最早记录

如果记录最早时间早于保留时间,那就从保留日开始导入,如果晚于保留开始日期,则用最早记录的日期开始建立分区

SELECT FROM_UNIXTIME(MIN(clock)) FROM history;

SELECT FROM_UNIXTIME(MIN(clock)) FROM history_uint;

SELECT FROM_UNIXTIME(MIN(clock)) FROM history_str;

SELECT FROM_UNIXTIME(MIN(clock)) FROM history_text;

SELECT FROM_UNIXTIME(MIN(clock)) FROM history_log;

SELECT FROM_UNIXTIME(MIN(clock)) FROM trends;

SELECT FROM_UNIXTIME(MIN(clock)) FROM trends_uint;

创建分区表结构

SHOW CREATE TABLE history;
SHOW CREATE TABLE history_uint;
SHOW CREATE TABLE history_str;
SHOW CREATE TABLE history_text;
SHOW CREATE TABLE history_log;
SHOW CREATE TABLE trends;
SHOW CREATE TABLE trends_uint;

使用以下脚本来生成表分区

使用python生成创建表分区的SQL语句

by day

# -*- coding: utf-8 -*-            
# @Time     : 2023/6/8 10:17
# @Author   : YoKing Ma
# @FileName : create_tp_day.py
# @Software : PyCharm


import datetime

tb_name = input("Please input data table name:")

print(f"""
Please get the first day, use like this command:
SELECT FROM_UNIXTIME(MIN(clock)) FROM {tb_name}
""")
start_day = input("Please input the first day (2023-03-03): ")

# tb_name="history"
# start_day="2023-03-03"

try:
    first_date = datetime.datetime.strptime(start_day, "%Y-%m-%d")
    print(f"Your input date is [{first_date}]")
except ValueError:
    print("date format is error!")

pt_create=[]

period = 0
while period <= 90:
    curr_date = first_date + datetime.timedelta(days=period)
    curr_date_title = curr_date.strftime("%Y_%m_%d")
    tommow_date = curr_date + datetime.timedelta(days=1)
    period += 1
    sql_str=f'PARTITION p{curr_date_title} VALUES LESS THAN (UNIX_TIMESTAMP("{tommow_date}"))'
    pt_create.append(sql_str)

pt_sql_str = f"""ALTER TABLE {tb_name} PARTITION BY RANGE (clock)
("""
for sql in pt_create:
    pt_sql_str += '\n'
    pt_sql_str += sql
    pt_sql_str += ','


pt_sql_str += "\b\n);"

print(pt_sql_str)

by mouth

# -*- coding: utf-8 -*-            
# @Time     : 2023/6/8 11:27
# @Author   : YoKing Ma
# @FileName : create_tp_month.py
# @Software : PyCharm

## pip install python-dateutil

import datetime
from dateutil.relativedelta import relativedelta

tb_name = input("Please input data table name:")

print(f"""
Please get the first day, use like this command:
SELECT FROM_UNIXTIME(MIN(clock)) FROM {tb_name}
""")
start_month = input("Please input the first day (2023-03): ")

# tb_name="history"
# start_month="2022-02"

try:
    tmp_date = start_month.split('-')
    fyear = int(tmp_date[0])
    fmonth = int(tmp_date[1])
    fdate = datetime.datetime(fyear, fmonth, 1)
except ValueError:
    print("date format is error!")

pt_create=[]

period = 12
cdate = fdate
for i in range(0,13):
    title = f"{cdate.year}_{cdate.month}"
    ndate = cdate + relativedelta(months=+1)

    sql = f'PARTITION p{title} VALUES LESS THAN (UNIX_TIMESTAMP("{ndate}"))'
    pt_create.append(sql)
    cdate = ndate

pt_sql_str = f"""ALTER TABLE {tb_name} PARTITION BY RANGE (clock)
("""
for sql in pt_create:
    pt_sql_str += '\n'
    pt_sql_str += sql
    pt_sql_str += ','


pt_sql_str += "\b\n);"

print(pt_sql_str)

案例演示

如生成了 `history` 表的临时表 `history_tmp`

CREATE TABLE `history_tmp` (
  `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',
  PRIMARY KEY (`itemid`, `clock`, `ns`)
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin;

用上面的脚本,为`history_tmp`表创建表分区语句

ALTER TABLE history_tmp PARTITION BY RANGE ( clock)
(
PARTITION p2024_04_04 VALUES LESS THAN (UNIX_TIMESTAMP("2024-04-05 00:00:00")) ENGINE = InnoDB,
PARTITION p2024_04_05 VALUES LESS THAN (UNIX_TIMESTAMP("2024-04-06 00:00:00")) ENGINE = InnoDB,
……
PARTITION p2024_07_01 VALUES LESS THAN (UNIX_TIMESTAMP("2024-07-02 00:00:00")) ENGINE = InnoDB,
PARTITION p2024_07_02 VALUES LESS THAN (UNIX_TIMESTAMP("2024-07-03 00:00:00")) ENGINE = InnoDB,
PARTITION p2024_07_03 VALUES LESS THAN (UNIX_TIMESTAMP("2024-07-04 00:00:00")) ENGINE = InnoDB
);

迁移数据

分2次迁移数据,这样可以减少业务停机的时长

数据导入

第一次迁移可以把变更日前的数据全部导入,比如变更日为2024年的7月3日,那将2024年7月3日 00:00:00之前90天的数据全部导入到分区表中

INSERT /*+ append enable_parallel_dml parallel(8) */ INTO `history_tmp`(`itemid`, `clock`, `value`, `ns`)
SELECT `itemid`, `clock`, `value`, `ns`
FROM `history`
WHERE `clock` >= 1712160000 and `clock` < 1719936000;

数据校验

对比临时表中的最早和最晚的记录是否和原表一致,并检查行数是否一致。

因为zabbix有housekeeper服务,这个服务会不定时的删除表中过期的数据,所以,在导入数据的过程中会造成数据不一致的情况。可以先把zabbix的管家服务停止;

SELECT FROM_UNIXTIME(MIN(clock)) FROM history_tmp;
SELECT FROM_UNIXTIME(MAX(clock)) FROM history_tmp;
SELECT COUNT(1) FROM history WHERE `clock` >= 1712160000 and `clock` < 1719936000;      -- 402789803
SELECT COUNT(1) FROM history_tmp;   -- 402789803

校验通过后,可以停止zabbix服务,并进行剩余数据的导入。操作与上文类似就不再赘述。

修改表名

rename table history to history_bak;
rename table history_tmp to history;

修改完表名后,就启动zabbix服务,看服务日志是否有报错,报错的话需要解决报错,直至服务正常。

在ODC中创建表分区管理计划

为已经分区表创建分区计划(在odc中每个数据库只允许有1个分区计划,新的分区计划生效后,旧的就失效了)

zabbix 大表在OB中实现表分区管理-1

我这边history相关表是每日一个表分区,trends相关表是1个月做一次表分区的维护。

所以【分区策略】我就以2中类型展示:【日】和【月】

分区策略--日

zabbix 大表在OB中实现表分区管理-2

这里产生的SQL语句类似于:

ALTER TABLE `zabbix`.`history` DROP PARTITION (`p2024_04_04`);

ALTER TABLE `zabbix`.`history` ADD PARTITION (
	PARTITION `p2024_07_04` VALUES LESS THAN (1720108800));

策略中用到的表达式

分区创建表达式:unix_timestamp(DATE(NOW() - INTERVAL DAY(NOW())
表分区命名表达式:DATE_FORMAT(DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH), 'p%Y_%m')

分区策略-月

zabbix 大表在OB中实现表分区管理-3

ALTER TABLE `zabbix`.`trends` DROP PARTITION (`p2023_7`);

ALTER TABLE `zabbix`.`trends` ADD PARTITION (
	PARTITION `p2024_08` VALUES LESS THAN (1725120000));

策略中用到的表达式

分区创建表达式:unix_timestamp(DATE(NOW() - INTERVAL DAY(NOW())
表分区命名表达式:DATE_FORMAT(DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH), 'p%Y_%m')

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论