OceanBase集群合并（Major Compaction）时耗时异常，正常的1-2小时延长至8小时以上，并且合并期间业务响应时间明显增加，磁盘I/O使用率比较高。

OceanBase 技术问题
#1

OceanBase集群合并时耗时异常，正常的1-2小时延长至8小时以上，并且合并期间业务响应时间明显增加，磁盘I/O使用率持续增高不降低。

1 个赞
#3

看看部分表或分区数据量是否有过大或写入频繁，导致合并变慢；合并期间在线业务量，是否存在争用CPU、I/O资源。其次排查底层存储磁盘性能是否存在慢盘。还有就是检查下合并并发度、内存限制等参数配置和统计信息。

解决方案：

检查合并状态和进度：

sql
– 查看当前合并状态和进度
SELECT zone, svr_ip, svr_port,
is_merge_error, merge_status,
merge_start_time, merge_version,
last_merged_version, merge_progress
FROM __all_virtual_merge_info
WHERE tenant_id = 1001 – 替换为实际租户ID
ORDER BY zone, svr_ip;

– 查看合并历史耗时
SELECT * FROM __all_merge_info_history
WHERE merge_start_time > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY merge_start_time DESC
LIMIT 10;
识别合并瓶颈节点：

sql
– 查看各节点合并进度差异
SELECT svr_ip,
max(merge_progress) as max_progress,
min(merge_progress) as min_progress,
avg(merge_progress) as avg_progress
FROM __all_virtual_merge_info
WHERE tenant_id = 1001
GROUP BY svr_ip
ORDER BY max_progress DESC;

– 查看慢盘或I/O瓶颈
SELECT svr_ip,
disk_read_io_rate,
disk_write_io_rate,
disk_io_utilization
FROM __all_virtual_disk_stat
WHERE svr_ip IN (SELECT svr_ip FROM __all_virtual_merge_info
WHERE merge_progress < 50)
ORDER BY disk_io_utilization DESC;
分析数据分布热点：

sql
– 查找数据量最大的分区
SELECT table_id, partition_id,
data_size, row_count,
svr_ip
FROM __all_virtual_table_stat
WHERE tenant_id = 1001
ORDER BY data_size DESC
LIMIT 20;

– 查看活跃分区（写入频繁）
SELECT partition_id,
sum(rows_inserted) as total_inserts,
sum(rows_updated) as total_updates,
sum(rows_deleted) as total_deletes
FROM __all_virtual_partition_stat
WHERE tenant_id = 1001
AND stat_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY partition_id
ORDER BY (total_inserts + total_updates + total_deletes) DESC
LIMIT 20;
步骤二：立即优化措施
调整合并策略：

sql
– 临时降低合并对业务的影响
ALTER SYSTEM SET _ob_enable_fast_commit = false; – 谨慎使用，可能影响DML性能

– 调整合并并发度（根据实际情况调整）
ALTER SYSTEM SET merge_thread_count = 8; – 默认可能为4
ALTER SYSTEM SET minor_merge_concurrency = 4; – 默认可能为2
资源隔离与限流：

sql
– 为合并操作设置资源组限制
ALTER RESOURCE UNIT sys_unit_config
SET min_cpu = 2, max_cpu = 4,
min_memory = ‘4G’, max_memory = ‘8G’;

– 调整I/O优先级
ALTER SYSTEM SET _ob_io_calibration_mode = ‘LOW’; – 在合并期间降低I/O优先级
拆分热点分区：

sql
– 如果发现超大分区，考虑拆分
ALTER TABLE hotspot_table
SPLIT PARTITION p_max
INTO (
PARTITION p_1 VALUES LESS THAN (1000000),
PARTITION p_2 VALUES LESS THAN (2000000),
PARTITION p_3 VALUES LESS THAN (MAXVALUE)
);

优化与预防

对频繁写入的大表，调整分区策略（按时间范围分区）

定期归档历史数据，减少单分区数据量

优化主键设计，避免写入热点

使用高性能SSD，并确保磁盘RAID配置合理

考虑使用OceanBase的读写分离架构，将合并影响隔离到只读副本

定期检查磁盘健康状态，排除慢盘

智能调度合并：

sql
– 设置合并窗口，避免业务高峰
ALTER SYSTEM SET merge_schedule_interval = ‘02:00-06:00’; – 凌晨合并

– 开启智能合并策略
ALTER SYSTEM SET enable_adaptive_compaction = true;
ALTER SYSTEM SET compaction_low_thread_score = 30; – 低负载时合并
监控与告警体系：

sql
– 创建合并监控视图
CREATE VIEW merge_monitor AS
SELECT DATE(merge_start_time) as merge_date,
TIMESTAMPDIFF(MINUTE, merge_start_time, merge_finish_time) as duration_minutes,
merge_status,
data_size_before_merge,
data_size_after_merge
FROM __all_merge_info_history
WHERE merge_status = ‘COMPLETED’;

紧急恢复预案
如果合并已卡住且影响业务：

1.暂停合并（谨慎使用）：
ALTER SYSTEM SUSPEND MERGE;

2.切换流量到备用集群或只读副本

3.重启受影响节点的observer服务

#4

我参考参考。谢谢

#5
  1. 找到未合并完成的租户id
    select* from CDB_OB_MAJOR_COMPACTION where STATUS != “IDLE”;
  2. 查询server级别进度表，看有多少分区没有合并完成
    select * from __all_virtual_server_compaction_progress where tenant_id = xxx;
  3. 看分区级别进度表，可以找到没有合并完成的分区及TRACE_ID
    select * from __all_virtual_tablet_compaction_progress where tenant_id = xxx;
  4. 通过sstable信息表可以看到SSTable的数据量和数据分布
    select * from GV$OB_SSTABLES where tenant_id = xxx and tablet_id = xxx and svr_ip = “xxx”;
#6

如果在故意刷积分 会给你们禁言