背景
把 zabbix 的数据库迁到 OB 之后,之前几个大表的表分区无法管理(OB中没有event),直到遇到了ODC平台(OB开发者中心),终于找到了如何在OB中自动管理表分区的方案。
本次就结合zabbix的几个大表做表分区管理的实践,与大家分享一下如何利用ODC来管理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 : 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个分区计划,新的分区计划生效后,旧的就失效了)
我这边history相关表是每日一个表分区,trends相关表是1个月做一次表分区的维护。
所以【分区策略】我就以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')
分区策略-月
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')