大表数据如何在OceanBase中进行表分区管理的实践

背景

将Zabbix的数据库迁移至OceanBase后，以其中的几个大表作为案例，本文将分享如何利用ODC（OceanBase 开发者工具），来进行自动管理OB数据库中的表分区的方案。

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

为每个要操作的表创建一个 [tb_name]---->临时表[tb_name_tmp]，表结构语原始表保持一致；
通过代码为每个临时表 [tb_name_tmp]生成表分区创建语句；
为每个临时表 [tb_name_tmp]创建表分区；
将操作日前一天的数据通过 insert into select 写入到临时表 [tb_name_tmp]中，并校验
停止应用，并将剩余数据写入到临时表 [tb_name_tmp]中
将原始表[tb_name]重名为[tb_name_bak]，并将临时表 [tb_name_tmp]重命名为[tb_name]
启动服务，并检查服务是否正常，如果服务异常，检查数据是否正确；
如果服务正常，在odc中为相关表创建表分区维护计划；
一段时间后，将原始表[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 : PyCharmimport datetimetb_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 += 1sql_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 += sqlpt_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-dateutilimport datetime
from dateutil.relativedelta import relativedeltatb_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 = ndatept_sql_str = f"""ALTER TABLE {tb_name} PARTITION BY RANGE (clock)
("""
for sql in pt_create:pt_sql_str += '\n'pt_sql_str += sqlpt_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个分区计划，新的分区计划生效后，旧的就失效了）

1720061551

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

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

分区策略--日

1720061781

这里产生的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')

分区策略-月

1720061955

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')