--- title: OceanBase 数据库 V4.x 转储合并信息查询手册及问题排查-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于OceanBase 数据库 V4.x 转储合并信息查询手册及问题排查相关的常见问题和使用技巧，帮助您快速解决OceanBase 数据库 V4.x 转储合并信息查询手册及问题排查的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # OceanBase 数据库 V4.x 转储合并信息查询手册及问题排查 更新时间：2026-06-12 08:51 适用版本： V4.2.x 内容类型：TechNote 本文介绍 OceanBase 数据库 V4.x 转储合并信息查询及问题排查。 ## 详细说明 ### 合并相关视图 | **视图名** | 视图介绍 | | --- | --- | | [CDB_OB_FREEZE_INFO](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219605)/DBA_OB_FREEZE_INFO | 合并版本信息 | | [CDB_OB_MAJOR_COMPACTION](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219476)/DBA_OB_MAJOR_COMPACTION | 租户的合并全局信息 | | [CDB_OB_ZONE_MAJOR_COMPACTION](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219496)/DBA_OB_ZONE_MAJOR_COMPACTION | 租户各个 Zone 的合并信息 | | [CDB_OB_TABLET_CHECKSUM_ERROR_INFO](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219646) | TABLET 副本之间出现的数据不一致的信息 | | [CDB_OB_COLUMN_CHECKSUM_ERROR_INFO](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219629) | 主表与索引表之间的 COLUMN_CHECKSUM 校验 | | [GV$OB_COMPACTION_PROGRESS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219772)/V$OB_COMPACTION_PROGRESS | OBServer 节点级合并进度信息 | | [GV$OB_TABLET_COMPACTION_PROGRESS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219775)/V$OB_TABLET_COMPACTION_PROGRESS | TABLET 级合并进度信息 | | [GV$OB_TABLET_COMPACTION_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219775)/V$OB_TABLET_COMPACTION_HISTORY | TABLET 级合并的历史信息 | | [GV$OB_COMPACTION_DIAGNOSE_INFO](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219669)/V$OB_COMPACTION_DIAGNOSE_INFO | 合并诊断信息 | | [GV$OB_COMPACTION_SUGGESTIONS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219703)/V$OB_COMPACTION_SUGGESTIONS | 合并建议信息 | 注 1：除特别说明外，本文档提供的视图**默认查询 sys 租户** OceanBase 数据库，查询各租户使用数据库连接字符串如下。 查询 sys 租户 OceanBase 数据库。 ```shell mysql -h10.xx.xx.20 -P2883 -uroot@sys#obcluster -D oceanbase -A -pxxx ``` 查询 MySQL 租户 OceanBase 数据库。 ```shell mysql -h10.xx.xx.20 -P2883 -uroot@obmysql#obcluster -D oceanbase -A -pxxx ``` 查询 Oracle 租户 (非 SYS 用户登陆也可以)。 ```shell obclient -h10.xx.xx.20 -P2883 -uSYS@oboracle#obcluster -A -pxxx ``` 注 2：除特别说明外，本文档提供的视图**默认不需要从 sys 租户切换至其他租户**。以查询租户 tablet 信息为例，可以在 sys 租户下切换为业务租户后查询 `__all_tablet_meta_table`，但在 sys 租户下可以**直接查询其对应的 virtual 视图 `__all_virtual_tablet_meta_table`** 或其对应的标准视图 CDB_OB_TABLET_REPLICAS，不需要切换为业务租户，且在 sys 租户下可以直接查询所有租户信息。 注 3：在不能直接登陆业务租户时可以在 sys 租户下**切换到业务租户** 以方便查询关于切换租户，但需要注意通过 2881 直连 OBServer。 ```shell mysql -h10.xx.xx.20 -P2881 -uroot@sys -pxxx ``` 通过如下方式切换租户。 ```shell -- 通过租户 ID 切换租户 ALTER SYSTEM CHANGE TENANT tenant_id=1002; -- 通过租户名字切换租户 ALTER SYSTEM CHANGE TENANT obmysql; -- 如下是通过租户名字切换租户，其中 META$1002 是 1002 租户的 META 租户名字，其租户 ID 为 1001。 ALTER SYSTEM CHANGE TENANT META$1002; ``` 注意，通过 sys 租户切换到其他租户时，不能通过 OBProxy 进行连接，否则报错如下。 ```shell obclient [oceanbase]> ALTER SYSTEM CHANGE TENANT META$1002; ERROR 1235 (0A000): operation from proxy not supported ``` sys 租户切换到其他租户时，可以通过如下 SQL 查看当前切换后的租户，以避免误操作。 ```shell -- sys 租户/MySQL 租户 SHOW TENANT; SELECT effective_tenant_id(); -- MySQL 租户/Oracle 租户 SELECT tenant_id, tenant_name, tenant_type FROM dba_ob_tenants ORDER BY tenant_id; ``` ### 合并相关参数 **业务租户**下通过如下 SQL 查询合并相关参数。 ```shell SHOW PARAMETERS LIKE 'compaction%score'; ``` 如下 3 个参数都是**租户级别**且动态生效，默认值均为 0，表示 6 个线程，可以根据实际需要进行调整： ```shell ALTER SYSTEM SET compaction_high_thread_score=16; -- MINI MERGE 的线程数 ALTER SYSTEM SET compaction_mid_thread_score=16; -- MINOR MERGE 的线程数 ALTER SYSTEM SET compaction_low_thread_score=16; -- MAJOR MERGE 的线程数 ``` 注 ：上述为**租户级别**参数，**不要在 sys 租户下查询**以避免查询有误 (OBCloud 等在不能直接登陆业务租户时，可以在 sys 租户下切换到业务租户以方便查询)。 ### 查询租户信息 sys 租户/ MySQL 租户 / Oracle 租户通过如下 SQL 查询租户信息，如 tenant_id，以备后续查询需要。 ```shell SELECT tenant_id, tenant_name, tenant_type, primary_zone, locality, compatibility_mode, status, create_time FROM dba_ob_tenants ORDER BY tenant_id; ``` ### 查看租户每天合并时间 sys 租户/ MySQL 租户 / Oracle 租户通过如下 SQL 查询。 ```shell SELECT tenant_id, zone, svr_ip, svr_port, scope, section, name, value FROM gv$ob_parameters WHERE name = 'major_freeze_duty_time' ORDER BY tenant_id, zone, svr_ip, svr_port; ``` ### 查询 Root Service leader 由于合并服务注册在每个租户 1 号日志流的 leader 上，相关数据库日志也在租户 1 号日志流 leader 所在的机器上。 因此，需要先找到租户 1 号日志流 leader 所在的机器以方便后续查看相关数据库日志以排查合并相关问题。 sys 租户下通过如下 SQL 查询 Root Service leader， ```shell SET @tenant_id=1002; SELECT tenant_id, zone, svr_ip, ls_id, role, COUNT(1) cnt FROM cdb_ob_table_locations WHERE tenant_id = @tenant_id and ls_id = 1 GROUP BY tenant_id, zone, svr_ip, ls_id, role ORDER BY tenant_id, zone, svr_ip, ls_id, role DESC; ``` 也可以通过 sys 租户特有的 DBA 视图根据选举信息查询，或查询选举信息以评估对合并的影响。 ```shell SET @tenant_id=1002; SELECT timestamp, value1, svr_ip, svr_port, event, name3, value3 FROM dba_ob_server_event_history WHERE module = "ELECTION" AND value1 = @tenant_id ORDER BY timestamp DESC LIMIT 10; ``` MySQL 租户 / Oracle 租户下通过如下 SQL 查询 Root Service leader。 ```shell SELECT zone, svr_ip, ls_id, role, COUNT(1) cnt FROM dba_ob_table_locations WHERE ls_id = 1 GROUP BY zone, svr_ip, ls_id, role ORDER BY zone, svr_ip, ls_id, role DESC; ``` ### 查询合并版本信息 sys 租户下通过如下 SQL 查询。 ```shell SET @tenant_id=1002; SELECT * FROM cdb_ob_freeze_info WHERE tenant_id = @tenant_id ORDER BY gmt_create DESC LIMIT 10; ``` MySQL 租户下通过如下 SQL 查询。 ```shell SELECT * FROM dba_ob_freeze_info ORDER BY gmt_create DESC LIMIT 10; ``` Oracle 租户下通过如下 SQL 查询。 ```shell ALTER SESSION SET nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF'; SELECT * FROM dba_ob_freeze_info ORDER BY gmt_create DESC FETCH FIRST 10 ROWS ONLY; ``` ### 查询合并状态 #### 系统租户下查询所有租户的合并状态 如下 SQL 中 `last_scn` 表示 "上一轮已完成合并的版本号"，`global_broadcast_scn` 表示"当前这一轮合并的版本号"，只有当 `last_scn` == `global_broadcast_scn` 时，表示当前轮次的合并结束。 如果 `status` 列一直出于 `COMPACTING` 状态，则说明该租户的合并可能卡住了。 另外，也可以关注下 `is_error` 列，如果该列的值为 `CHECKSUM_ERROR`，则说明该租户下出现了 CHECKSUM 校验不一致的问题。 ```shell SELECT tenant_id, global_broadcast_scn, is_error AS error, status, frozen_scn, last_scn, is_suspended AS suspend, info, start_time, last_finish_time, round(timestampdiff(second, start_time, case when start_time>last_finish_time then now() else last_finish_time end)/60,2) merge_time_min FROM cdb_ob_major_compaction ORDER BY start_time DESC LIMIT 50; ``` 在系统租户下展示所有租户的各个 Zone 的合并信息。 ```shell SELECT * FROM cdb_ob_zone_major_compaction ORDER BY start_time DESC; ``` #### MySQL 普通租户下查询当前租户的合并状态 ```shell SELECT global_broadcast_scn, is_error AS error, status, frozen_scn, last_scn, is_suspended AS suspend, info, start_time, last_finish_time, round(timestampdiff(second, start_time, last_finish_time)/60,2) merge_time_min FROM dba_ob_major_compaction ORDER BY start_time DESC LIMIT 50; ``` 在普通租户下展示当前租户的各个 Zone 的合并信息。 ```shell SELECT * FROM dba_ob_zone_major_compaction; ``` #### Oracle 普通租户下查询当前租户的合并状态 ```shell SELECT global_broadcast_scn, is_error AS error, status, frozen_scn, last_scn, is_suspended AS suspend, info, to_char( ( from_tz( CAST( TO_TIMESTAMP_TZ( '1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR' ) + numtodsinterval(start_time / 1000000, 'SECOND') AS TIMESTAMP ), 'UTC' ) AT LOCAL ), 'YYYY-MM-DD HH24:MI:SS' ) start_time, to_char( ( from_tz( CAST( TO_TIMESTAMP_TZ( '1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR' ) + numtodsinterval(last_finish_time / 1000000, 'SECOND') AS TIMESTAMP ), 'UTC' ) AT LOCAL ), 'YYYY-MM-DD HH24:MI:SS' ) last_finish_time, round( (last_finish_time - start_time) / 1000000 / 60, 2 ) merge_time_min FROM dba_ob_major_compaction ORDER BY start_time DESC FETCH FIRST 10 ROWS ONLY; ``` 在 Oracle 普通租户下展示当前租户的各个 Zone 的合并信息。 ```shell SELECT zone, broadcast_scn, status, last_scn, to_char( ( from_tz( CAST( TO_TIMESTAMP_TZ( '1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR' ) + numtodsinterval(start_time / 1000000, 'SECOND') AS TIMESTAMP ), 'UTC' ) AT LOCAL ), 'YYYY-MM-DD HH24:MI:SS' ) start_time, to_char( ( from_tz( CAST( TO_TIMESTAMP_TZ( '1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR' ) + numtodsinterval(last_finish_time / 1000000, 'SECOND') AS TIMESTAMP ), 'UTC' ) AT LOCAL ), 'YYYY-MM-DD HH24:MI:SS' ) last_finish_time, round( (last_finish_time - start_time) / 1000000 / 60, 2 ) merge_time_min FROM dba_ob_zone_major_compaction; ``` ### 查询合并耗时 通过 sys 租户特有的 DBA 视图查看每日合并/手动合并耗时。 ```shell SET @tenant_id=1002; SELECT t.tenant_id, t.global_boradcast_scn, t.merge_begin_time, u.merge_end_time, round(timestampdiff(second, t.merge_begin_time, u.merge_end_time)/60,2) merge_time_min FROM ( SELECT value1 tenant_id, value2 global_boradcast_scn, timestamp merge_begin_time FROM dba_ob_rootservice_event_history WHERE module = 'daily_merge' AND event = 'merging' ) t, ( SELECT value1 tenant_id, value2 global_boradcast_scn, timestamp merge_end_time FROM dba_ob_rootservice_event_history WHERE module = 'daily_merge' AND event = 'global_merged' ) u WHERE t.tenant_id = u.tenant_id AND t.tenant_id = @tenant_id AND t.global_boradcast_scn = u.global_boradcast_scn ORDER BY t.merge_begin_time DESC LIMIT 10; ``` ### 获取租户的合并进度 **sys 租户/MySQL 租户** 按租户查询合并进度。 ```shell SELECT compaction_scn, tenant_id, SUM(total_tablet_count - unfinished_tablet_count) finished_tablet_count, SUM(unfinished_tablet_count) unfinished_tablet_count, SUM(total_tablet_count) total_tablet_count, round( 100 *( 1 - SUM(unfinished_tablet_count) / SUM(total_tablet_count) ), 2 ) progress_pct FROM gv$ob_compaction_progress GROUP BY tenant_id, compaction_scn ORDER BY compaction_scn DESC, tenant_id LIMIT 10; ``` 按 OBServer 查询合并进度。 ```shell SELECT svr_ip, svr_port, tenant_id, type, zone, compaction_scn, status, total_tablet_count, unfinished_tablet_count, round(data_size / 1024 / 1024, 2) data_size_mb, unfinished_data_size / 1024 / 1024 unfinished_data_size_mb, start_time, estimated_finish_time, round( 100 *(1 - unfinished_tablet_count / total_tablet_count), 2 ) progress_pct FROM gv$ob_compaction_progress ORDER BY compaction_scn DESC LIMIT 10; ``` **Oracle 租户** ```shell ALTER SESSION SET nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF'; SELECT compaction_scn, tenant_id, SUM(total_tablet_count - unfinished_tablet_count) finished_tablet_count, SUM(unfinished_tablet_count) unfinished_tablet_count, SUM(total_tablet_count) total_tablet_count, round( 100 *( 1 - SUM(unfinished_tablet_count) / SUM(total_tablet_count) ), 2 ) progress_pct FROM gv$ob_compaction_progress GROUP BY tenant_id, compaction_scn ORDER BY compaction_scn DESC, tenant_id FETCH FIRST 10 ROWS ONLY; ``` ```shell ALTER SESSION SET nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF'; SELECT svr_ip, svr_port, tenant_id, type, zone, compaction_scn, status, total_tablet_count, unfinished_tablet_count, round(data_size / 1024 / 1024, 2) data_size_mb, round(unfinished_data_size / 1024 / 1024, 2) unfinished_data_size_mb, start_time, estimated_finish_time, round( 100 *(1 - unfinished_tablet_count / total_tablet_count), 2 ) progress_pct FROM gv$ob_compaction_progress ORDER BY compaction_scn DESC FETCH FIRST 10 ROWS ONLY; ``` ### 获取 TABLET 合并进度 **sys 租户/MySQL 租户**/**Oracle 租户**下查询。 查询 TABLET 合并明细信息。 ```shell SELECT svr_ip, svr_port, tenant_id, type, compaction_scn, status, tablet_id, round(data_size / 1024 / 1024, 2) data_size_mb, round(unfinished_data_size / 1024 / 1024, 2) unfinished_data_size_mb, start_time, estimated_finish_time FROM gv$ob_tablet_compaction_progress ORDER BY start_time DESC; ``` 查询 TABLET 合并汇总信息。 ```shell SELECT svr_ip, svr_port, tenant_id, type, compaction_scn, status, COUNT(1) FROM gv$ob_tablet_compaction_progress GROUP BY svr_ip, svr_port, tenant_id, type, compaction_scn, status; ``` ### 查询 TABLET 是否合并到指定版本 从 `cdb_ob_major_compaction`/`dba_ob_major_compaction` 获取 `global_broadcast_scn` 后。 查询明细。 ```shell SET @tenant_id=1002; SET @compaction_scn=1725442148340415871; SELECT tenant_id, svr_ip, svr_port, tablet_id, compaction_scn, round(data_size / 1024 / 1024, 2) data_size_mb, round(required_size / 1024 / 1024, 2) required_size_mb FROM cdb_ob_tablet_replicas WHERE tenant_id = @tenant_id AND compaction_scn < @compaction_scn LIMIT 10; ``` 按 `compaction_scn` 统计租户下的 tablet : ```shell SET @tenant_id=1002; SELECT tenant_id, svr_ip, svr_port, compaction_scn, round(SUM(data_size) / 1024 / 1024, 2) data_size_mb, round(SUM(required_size) / 1024 / 1024, 2) required_size_mb, COUNT(1) FROM cdb_ob_tablet_replicas WHERE tenant_id = @tenant_id GROUP BY tenant_id, svr_ip, svr_port, compaction_scn ORDER BY tenant_id, svr_ip, svr_port, compaction_scn DESC; ``` 查询明细。 ```shell SET @compaction_scn=1725442148340415871; SELECT svr_ip, svr_port, tablet_id, compaction_scn, round(data_size / 1024 / 1024, 2) data_size_mb, round(required_size / 1024 / 1024, 2) required_size_mb FROM dba_ob_tablet_replicas WHERE compaction_scn < @compaction_scn LIMIT 10; ``` ```shell SELECT svr_ip, svr_port, compaction_scn, round(SUM(data_size) / 1024 / 1024, 2) data_size_mb, round(SUM(required_size) / 1024 / 1024, 2) required_size_mb, COUNT(1) FROM dba_ob_tablet_replicas GROUP BY svr_ip, svr_port, compaction_scn ORDER BY svr_ip, svr_port, compaction_scn DESC; ``` 查询明细。 ```shell SET @compaction_scn=1725442148340415871; SELECT svr_ip, svr_port, tablet_id, compaction_scn, round(data_size / 1024 / 1024, 2) data_size_mb, round(required_size / 1024 / 1024, 2) required_size_mb FROM dba_ob_tablet_replicas WHERE compaction_scn < @compaction_scn FETCH FIRST 10 ROWS ONLY; ``` ``` ### 统计 TABLET 转储历史 注：基于内存虚拟表，内容可能被轮转覆盖，**不一定能存储一次合并的所有 TABLET 转储历史**，仅推荐查询最近的 TABLET 转储历史信息。 ```shell SELECT tenant_id, MIN(start_time) AS min_start_time, MAX(finish_time) AS max_finish_time, round(SUM(occupy_size)/1024/1024,2) AS occupy_size_mb, SUM(total_row_count) AS total_row_count, COUNT(1) AS tablet_count FROM gv$ob_tablet_compaction_history WHERE type = 'MINI_MERGE' AND finish_time >= date_sub(now(), INTERVAL 600 SECOND) -- AND finish_time >= date_sub(now(), INTERVAL 3 HOUR) -- AND finish_time >= date_sub(now(), INTERVAL 1 DAY) GROUP BY tenant_id LIMIT 50; ``` ```shell SELECT MIN(start_time) AS min_start_time, MAX(finish_time) AS max_finish_time, round(SUM(occupy_size)/1024/1024,2) AS occupy_size_mb, SUM(total_row_count) AS total_row_count, COUNT(1) AS tablet_count FROM gv$ob_tablet_compaction_history WHERE type = 'MINI_MERGE' AND finish_time >= date_sub(now(), INTERVAL 600 SECOND) -- AND finish_time >= date_sub(now(), INTERVAL 3 HOUR) -- AND finish_time >= date_sub(now(), INTERVAL 1 DAY) LIMIT 50; ``` ```shell SELECT MIN(start_time) AS min_start_time, MAX(finish_time) AS max_finish_time, round(SUM(occupy_size)/1024/1024,2) AS occupy_size_mb, SUM(total_row_count) AS total_row_count, COUNT(1) AS tablet_count FROM gv$ob_tablet_compaction_history WHERE type = 'MINI_MERGE' AND finish_time >= SYSTIMESTAMP - INTERVAL '1' HOUR; ``` ### 查看 TABLET 转储历史明细 sys 租户下通过如下 SQL 查询。 ```shell SET @tenant_id=1002; SELECT svr_ip, svr_port, tenant_id, ls_id, tablet_id, type, compaction_scn, start_time, finish_time, timestampdiff(microsecond, start_time, finish_time) / 1000000 seconds, round(occupy_size / 1024 / 1024, 2) AS occupy_size_mb, total_row_count FROM gv$ob_tablet_compaction_history WHERE type = 'MINI_MERGE' AND finish_time >= date_sub(now(), INTERVAL 24 HOUR) AND tenant_id = @tenant_id ORDER BY start_time DESC LIMIT 10; ``` ```shell SELECT svr_ip, svr_port, tenant_id, ls_id, tablet_id, type, compaction_scn, start_time, finish_time, timestampdiff(microsecond, start_time, finish_time) / 1000000 seconds, round(occupy_size / 1024 / 1024, 2) AS occupy_size_mb, total_row_count FROM gv$ob_tablet_compaction_history WHERE type = 'MINI_MERGE' AND finish_time >= date_sub(now(), INTERVAL 24 HOUR) ORDER BY start_time DESC LIMIT 10; ``` ```shell SELECT svr_ip, svr_port, tenant_id, ls_id, tablet_id, type, compaction_scn, start_time, finish_time, round(occupy_size / 1024 / 1024, 2) AS occupy_size_mb, total_row_count FROM gv$ob_tablet_compaction_history WHERE type = 'MINI_MERGE' AND finish_time >= SYSTIMESTAMP - INTERVAL '1' HOUR ORDER BY start_time DESC FETCH FIRST 10 ROWS ONLY; ``` ### OCP 租户转储历史 在 OCP 租户合并管理中，显示对应的转储历史。OCP 会每五分钟拉取 TABLET 转储信息并统计保存，并在合并结束后，根据 OCP 内收集的转储历史进行展示。 可以通过如下方式查询 OCP `ocp_monitor` 数据库，查询 OCP 内收集的转储历史。 可以通过如下命令获取 ocp_meta 租户登陆信息。 ```shell docker exec -it ocp bash -c 'echo mysql -h${OCP_METADB_HOST} -P${OCP_METADB_PORT} -u${OCP_METADB_USER} -p${OCP_METADB_PASSWORD} -Docp -A' ``` 将如下命令返回的结果中的信息替换为 `ocp_monitor` 相关信息，密码为创建 OCP 时设置的密码。 ```shell ocp_meta => ocp_monitor -Docp => -Docp_monitor ``` 如 ```shell OCP_MONITOR_HOST=10.xx.xx.20 OCP_MONITOR_PASSWORD=xxx mysql -h"${OCP_MONITOR_HOST}" -P3306 -uroot@ocp_monitor#metadbcluster -p"${OCP_MONITOR_PASSWORD}" -Docp_monitor -A ``` 可以通过如下 SQL 查询 OCP 内收集的转储历史。 ```shell SET @ob_cluster_name='obcluster'; SELECT usec_to_time(min_start_time), usec_to_time(max_finish_time), * FROM ob_tenant_mini_compaction_info WHERE cluster_name = @ob_cluster_name ORDER BY min_start_time DESC LIMIT 10; ``` ### 查询合并诊断信息视图 sys 租户/MySQL 租户下通过如下 SQL 查询。 ```shell SELECT * FROM gv$ob_compaction_diagnose_info ORDER BY create_time DESC LIMIT 10; ``` ```shell SELECT * FROM gv$ob_compaction_diagnose_info ORDER BY create_time DESC FETCH FIRST 10 ROWS ONLY; ``` **示例一 存储层 tablet 合并调度异常** 定位到未完成合并的 tablet 后，需要先确认这个 tablet 的合并调度是否正常。 合并未调度的原因有很多：由于合并前需要做一次转储，保证 tablet snapshot version 推过合并冻结点，所以**如果转储有问题的话，会导致 tablet 的合并无法被调度。** ![image-20240903151312578](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/transfer-compaction/compaction/20250306ob4x-dump-merge-information01.png) 从上图可以看出 tablet 549755813987 的 MAJOR MERGE 处于 NOT_SCHEDULE 状态，原因是 **tablet snapshot version未推过合并冻结点**；tablet 549755813987 的 MINI MERGE 处于 FAILED 状态，说明该 tablet 合并卡住的原因是转储失败了。 tablet snapshot version 未推过合并冻结点的原因也有多个： - 备机读时间戳未推过合并冻结点，此时无法发起转储，合并也就无法调度。 - 转储失败。 - 当前转储队列压力较大，转储 DAG 一直未调度，导致合并超时。 - MemTable 未达到冻结条件（未打印 "flush memtable" 日志）。 **示例二 tablet 版本尚未推高至当前合并版本号** 如下 "status = RS_UNCOMPACTED" 的诊断信息，则表明还存在 tablet 版本尚未推高至当前合并版本号。也就是说，RS 端合并卡在检查 tablet 版本号是否皆已推高至当前合并版本号阶段，需要进一步排查存储层为何尚未将这些 tablets 的版本推高至当前合并版本号。 ```shell obclient [oceanbase]> SELECT * FROM __all_virtual_compaction_diagnose_info LIMIT 20; +---------------+----------+-----------+-------------+-------+-----------+----------------+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | svr_ip | svr_port | tenant_id | type | ls_id | tablet_id | status | create_time | diagnose_info | +---------------+----------+-----------+-------------+-------+-----------+----------------+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | 1 | 1 | RS_UNCOMPACTED | 2024-08-21 15:39:58.071357 | server="10.xx.xx.20:2882",status="compaction_scn_not_update",frozen_scn=1724207164616098840,compaction_scn=1724176804358745685,report_scn=1724176804358745685 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | 1 | 121 | RS_UNCOMPACTED | 2024-08-21 15:39:58.071362 | server="10.xx.xx.20:2882",status="compaction_scn_not_update",frozen_scn=1724207164616098840,compaction_scn=1724176804358745685,report_scn=1724176804358745685 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | 1 | 331 | RS_UNCOMPACTED | 2024-08-21 15:39:58.071364 | server="10.xx.xx.20:2882",status="compaction_scn_not_update",frozen_scn=1724207164616098840,compaction_scn=1724176804358745685,report_scn=1724176804358745685 | +---------------+----------+-----------+-------------+-------+-----------+----------------+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (3.424 sec) ``` 进一步查到这三个 tablet 为系统视图，等待即可。如果是非系统的 tablet 慢的话则根据需要进一步定位原因。 sys 租户通过如下 SQL 查询。 ```shell obclient [oceanbase]> SELECT tenant_id,database_name,table_name,table_id,table_type,tablet_id,ls_id FROM cdb_ob_table_locations WHERE tenant_id = 1002 AND tablet_id IN (1,121,331); +-----------+---------------+------------------------+----------+--------------+-----------+-------+ | tenant_id | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | TABLET_ID | LS_ID | +-----------+---------------+------------------------+----------+--------------+-----------+-------+ | 1002 | oceanbase | __all_core_table | 1 | SYSTEM TABLE | 1 | 1 | | 1002 | oceanbase | __all_sys_stat | 121 | SYSTEM TABLE | 121 | 1 | | 1002 | oceanbase | __all_monitor_modified | 331 | SYSTEM TABLE | 331 | 1 | +-----------+---------------+------------------------+----------+--------------+-----------+-------+ 3 rows in set (1.355 sec) ``` MySQL 租户 / Oracle 租户通过如下 SQL 查询。 ```shell obclient [ALVIN]> SELECT database_name,table_name,table_id,table_type,tablet_id,ls_id FROM dba_ob_table_locations a WHERE a.tablet_id IN (1,121,331); +---------------+------------------------+----------+--------------+-----------+-------+ | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | TABLET_ID | LS_ID | +---------------+------------------------+----------+--------------+-----------+-------+ | oceanbase | __all_core_table | 1 | SYSTEM TABLE | 1 | 1 | | oceanbase | __all_sys_stat | 121 | SYSTEM TABLE | 121 | 1 | | oceanbase | __all_monitor_modified | 331 | SYSTEM TABLE | 331 | 1 | +---------------+------------------------+----------+--------------+-----------+-------+ 3 rows in set (0.958 sec) ``` ### 查询合并建议信息视图 用于记录根据历史的 COMPACTION 记录，提出的一些建议信息。 包括但不限于： 数据表单分区数据量过大导致执行时间过长/执行的数据量和时间不符合，怀疑硬件故障/大表随机插入，导致的宏块重用率低/并行合并划分 range 不合理等。 ```shell SELECT * FROM gv$ob_compaction_suggestions ORDER BY start_time DESC LIMIT 10; ``` ```shell SELECT * FROM gv$ob_compaction_suggestions ORDER BY start_time DESC FETCH FIRST 10 ROWS ONLY; ``` ### 查询 DAG #### 查询 `__all_virtual_dag_scheduler` sys 租户下通过如下 SQL 查询 DAG 参数。 ```shell SET @tenant_id=1002; SELECT * FROM __all_virtual_dag_scheduler WHERE tenant_id = @tenant_id AND `key` like '%COMPACTION%' ORDER BY svr_ip, svr_port, tenant_id, `key`, value_type; ``` 以下为默认参数，可以关注 `value_type` 为 `RUNNING_TASK_CNT` 对应的 `value`。 ```shell MySQL [oceanbase]> SELECT * FROM __all_virtual_dag_scheduler WHERE tenant_id = @tenant_id AND `key` like '%COMPACTION%' ORDER BY svr_ip, svr_port, tenant_id, `key`, value_type; +----------------+----------+-----------+------------------+----------------------+-------+ | svr_ip | svr_port | tenant_id | value_type | key | value | +----------------+----------+-----------+------------------+----------------------+-------+ | 10.xx.xx.27 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_HIGH | 6 | | 10.xx.xx.27 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_HIGH | 0 | | 10.xx.xx.27 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_HIGH | 6 | | 10.xx.xx.27 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_LOW | 6 | | 10.xx.xx.27 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_LOW | 0 | | 10.xx.xx.27 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_LOW | 6 | | 10.xx.xx.27 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_MID | 6 | | 10.xx.xx.27 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_MID | 0 | | 10.xx.xx.27 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_MID | 6 | | 10.xx.xx.28 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_HIGH | 6 | | 10.xx.xx.28 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_HIGH | 0 | | 10.xx.xx.28 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_HIGH | 6 | | 10.xx.xx.28 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_LOW | 6 | | 10.xx.xx.28 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_LOW | 0 | | 10.xx.xx.28 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_LOW | 6 | | 10.xx.xx.28 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_MID | 6 | | 10.xx.xx.28 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_MID | 0 | | 10.xx.xx.28 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_MID | 6 | | 10.xx.xx.29 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_HIGH | 6 | | 10.xx.xx.29 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_HIGH | 0 | | 10.xx.xx.29 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_HIGH | 6 | | 10.xx.xx.29 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_LOW | 6 | | 10.xx.xx.29 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_LOW | 0 | | 10.xx.xx.29 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_LOW | 6 | | 10.xx.xx.29 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_MID | 6 | | 10.xx.xx.29 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_MID | 0 | | 10.xx.xx.29 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_MID | 6 | +----------------+----------+-----------+------------------+----------------------+-------+ 27 rows in set (0.003 sec) ``` 在单机测试环境设置如下两个参数后查询。 ```shell compaction_high_thread_score 22 compaction_low_thread_score 21 ``` 可以看到 `PRIO_COMPACTION_HIGH` `RUNNING_TASK_CNT` 为 22，`PRIO_COMPACTION_LOW` `RUNNING_TASK_CNT` 为 0。 ```shell obclient [oceanbase]> SELECT * FROM __all_virtual_dag_scheduler WHERE tenant_id = @tenant_id AND `key` like '%COMPACTION%' ORDER BY svr_ip, svr_port, tenant_id, `key`, value_type; +---------------+----------+-----------+------------------+----------------------+-------+ | svr_ip | svr_port | tenant_id | value_type | key | value | +---------------+----------+-----------+------------------+----------------------+-------+ | 10.xx.xx.20 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_HIGH | 22 | | 10.xx.xx.20 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_HIGH | 22 | | 10.xx.xx.20 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_HIGH | 22 | | 10.xx.xx.20 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_LOW | 21 | | 10.xx.xx.20 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_LOW | 0 | | 10.xx.xx.20 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_LOW | 21 | | 10.xx.xx.20 | 2882 | 1002 | LOW_LIMIT | PRIO_COMPACTION_MID | 6 | | 10.xx.xx.20 | 2882 | 1002 | RUNNING_TASK_CNT | PRIO_COMPACTION_MID | 0 | | 10.xx.xx.20 | 2882 | 1002 | UP_LIMIT | PRIO_COMPACTION_MID | 6 | +---------------+----------+-----------+------------------+----------------------+-------+ 9 rows in set (0.088 sec) ``` #### 查询 `__all_virtual_dag` sys 租户下通过如下 SQL 查询 DAG 汇总。 ```shell SELECT svr_ip, svr_port, tenant_id, dag_type, status, COUNT(1), SUM(running_task_cnt) running_task_cnt FROM __all_virtual_dag GROUP BY svr_ip, svr_port, tenant_id, dag_type, status; ``` sys 租户下通过如下 SQL 查询 DAG 明细。 ```shell SELECT svr_ip, svr_port, tenant_id, dag_type, dag_key, start_time FROM __all_virtual_dag WHERE status = 'NODE_RUNNING' ORDER BY svr_ip, svr_port, tenant_id, dag_type, dag_key; ``` ``` 可以看到 `MINI_MERGE` 和 `MAJOR_MERGE` 分别为 22 个和 21 个。 ```shell obclient [oceanbase]> SELECT svr_ip, svr_port, tenant_id, dag_type, status, COUNT(1), SUM(running_task_cnt) running_task_cnt FROM __all_virtual_dag GROUP BY svr_ip, svr_port, tenant_id, dag_type, status; +---------------+----------+-----------+-------------+--------------+----------+-----------------------+ | svr_ip | svr_port | tenant_id | dag_type | status | count(1) | sum(running_task_cnt) | +---------------+----------+-----------+-------------+--------------+----------+-----------------------+ | 10.xx.xx.20 | 2882 | 1002 | MINI_MERGE | NODE_RUNNING | 22 | 22 | | 10.xx.xx.20 | 2882 | 1002 | MINI_MERGE | READY | 78 | 0 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | NODE_RUNNING | 21 | 21 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | READY | 79 | 0 | +---------------+----------+-----------+-------------+--------------+----------+-----------------------+ 4 rows in set (0.002 sec) ``` 查询 DAG 明细，可以看到 `MAJOR_MERGE` 为 21 个。 ```shell obclient [oceanbase]> SELECT svr_ip, svr_port, tenant_id, dag_type, dag_key, start_time FROM __all_virtual_dag WHERE status = 'NODE_RUNNING' ORDER BY svr_ip, svr_port, tenant_id, dag_type, dag_key; +---------------+----------+-----------+-------------+------------------------------------------+----------------------------+ | svr_ip | svr_port | tenant_id | dag_type | dag_key | start_time | +---------------+----------+-----------+-------------+------------------------------------------+----------------------------+ | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=1152921504606896292 | 2024-08-23 17:52:49.332138 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=1152921504606979218 | 2024-08-23 17:52:49.337463 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=1152921504606987614 | 2024-08-23 17:52:49.361007 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=1152921504607034443 | 2024-08-23 17:52:49.417520 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=1152921504607050720 | 2024-08-23 17:52:49.429122 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=1152921504607074379 | 2024-08-23 17:52:49.257475 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=1152921504607074933 | 2024-08-23 17:52:49.315961 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=209066 | 2024-08-23 17:52:49.382214 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=216944 | 2024-08-23 17:52:49.257346 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=238892 | 2024-08-23 17:52:49.298824 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=248936 | 2024-08-23 17:52:49.233521 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=251565 | 2024-08-23 17:52:49.394768 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=278021 | 2024-08-23 17:52:49.469170 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=281085 | 2024-08-23 17:52:49.294511 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=286831 | 2024-08-23 17:52:49.337603 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=317318 | 2024-08-23 17:52:49.337747 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=343187 | 2024-08-23 17:52:49.227017 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=373728 | 2024-08-23 17:52:49.469134 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=411271 | 2024-08-23 17:52:49.257325 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=424117 | 2024-08-23 17:52:49.394123 | | 10.xx.xx.20 | 2882 | 1002 | MAJOR_MERGE | ls_id=1001 tablet_id=431541 | 2024-08-23 17:52:49.354247 | +---------------+----------+-----------+-------------+------------------------------------------+----------------------------+ 21 rows in set (0.002 sec) ``` #### 查询失败 DAG 记录 一般来说，如果是转储/合并失败，失败原因及 trace ID 都会记录到 `__all_virtual_dag_warning_history` 虚拟表中。 ```shell SELECT * FROM __all_virtual_dag_warning_history ORDER BY gmt_create DESC LIMIT 10; ``` ![image-20240903151951205](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/transfer-compaction/compaction/20250306ob4x-dump-merge-information02.png) 成功拿到 trace ID，接下来就可以参考 `gmt_modified`，到对应时间范围内的 observer log 中查看具体的失败原因了。 ### 查询 SERVER 级别合并事件历史 sys 租户/MySQL 租户下通过如下 SQL 查询。 ```shell SET @tenant_id=1002; SELECT * FROM __all_virtual_server_compaction_event_history where tenant_id = @tenant_id ORDER BY event_timestamp DESC LIMIT 20; ``` 注：`event_timestamp` 是存储的结束时间，晚于 Root Service 合并结束的时间。 ### 查询 CHECKSUM 相关视图 RS 端的 major freeze 实际包括两个过程：判定租户下所有 tablet 的 major merge 已完成和 CHECKSUM 校验。 当 RS 端判定 tablet 的 major merge 完成后，开始执行 CHECKSUM 校验工作，主要包括 tablet 三副本间的 data_checksum 校验，主表与索引表之间的 column_checksum 校验。因此，如果 `cdb_ob_major_compaction.is_error` 列值为 `CHECKSUM_ERROR` (参考本文档中**查询合并状态**部分)，则说明合并实际已完成，CHECKSUM 校验失败。如果其 CHECKSUM 校验失败，则不允许其发起下一轮的 major freeze。 在解决了数据一致性问题后，通过 `ALTER SYSTEM CLEAR MERGE ERROR;` 来清除 checksum_error 状态。 sys 租户下查询。 查询 CHECKSUM 不一致的 tablet_id。 ```shell SET @tenant_id=1002; SELECT tenant_id, compaction_scn, tablet_id FROM __all_virtual_tablet_replica_checksum where tenant_id = @tenant_id GROUP BY tenant_id, compaction_scn, tablet_id HAVING MIN(data_checksum) != MAX(data_checksum) OR MIN(column_checksums) != MAX(column_checksums); ``` 查询存在 checksum error 的 tablet 或 table： ```shell SELECT * FROM cdb_ob_tablet_checksum_error_info LIMIT 10; SELECT * FROM cdb_ob_column_checksum_error_info LIMIT 10; ``` ### 查询 IO 相关视图 #### 查询 `gv$ob_io_benchmark` sys 租户下查询，以 16K READ IOPS 为例。 ```shell SELECT * FROM gv$ob_io_benchmark WHERE mode = 'READ' AND size = '16384'; ``` 示例如下。 ```shell MySQL [oceanbase]> SELECT * FROM gv$ob_io_benchmark WHERE mode = 'READ' AND size = '16384'; +----------------+----------+--------------+------+-------+--------+------+---------+ | SVR_IP | SVR_PORT | STORAGE_NAME | MODE | SIZE | IOPS | MBPS | LATENCY | +----------------+----------+--------------+------+-------+--------+------+---------+ | 10.xx.xx.27 | 2882 | DATA | READ | 16384 | 91670 | 1432 | 173 | | 10.xx.xx.29 | 2882 | DATA | READ | 16384 | 106219 | 1659 | 150 | | 10.xx.xx.28 | 2882 | DATA | READ | 16384 | 104286 | 1629 | 152 | +----------------+----------+--------------+------+-------+--------+------+---------+ 3 rows in set (0.006 sec) ``` #### 查询 `__all_virtual_io_quota` sys 租户下查询。 ```shell SET @tenant_id=1004; SELECT svr_ip, svr_port, tenant_id, group_id, mode, size, real_iops, real_mbps FROM __all_virtual_io_quota WHERE tenant_id = @tenant_id ORDER BY svr_ip, svr_port, tenant_id, group_id, mode, size; ``` 示例如下。 ```shell MySQL [oceanbase]> SET @tenant_id=1004; Query OK, 0 rows affected (0.000 sec) MySQL [oceanbase]> SELECT svr_ip, svr_port, tenant_id, group_id, mode, size, real_iops, real_mbps FROM __all_virtual_io_quota WHERE tenant_id = @tenant_id ORDER BY svr_ip, svr_port, tenant_id, group_id, mode, size; +----------------+----------+-----------+----------+-------+------+-----------+-----------+ | svr_ip | svr_port | tenant_id | group_id | mode | size | real_iops | real_mbps | +----------------+----------+-----------+----------+-------+------+-----------+-----------+ | 10.xx.xx.27 | 2882 | 1004 | 20000 | WRITE | 4311 | 490 | 2 | | 10.xx.xx.27 | 2882 | 1004 | 20004 | READ | 4096 | 9920 | 39 | | 10.xx.xx.27 | 2882 | 1004 | 20004 | WRITE | 4096 | 1446 | 6 | | 10.xx.xx.28 | 2882 | 1004 | 20000 | WRITE | 4210 | 658 | 3 | | 10.xx.xx.28 | 2882 | 1004 | 20004 | READ | 4096 | 4612 | 18 | | 10.xx.xx.28 | 2882 | 1004 | 20004 | WRITE | 4096 | 1973 | 8 | | 10.xx.xx.29 | 2882 | 1004 | 20000 | WRITE | 4220 | 461 | 2 | | 10.xx.xx.29 | 2882 | 1004 | 20004 | READ | 4096 | 9666 | 38 | | 10.xx.xx.29 | 2882 | 1004 | 20004 | WRITE | 4096 | 1398 | 5 | +----------------+----------+-----------+----------+-------+------+-----------+-----------+ 9 rows in set (0.003 sec) ``` ### 合并慢问题 #### 排查 CPU 内存 IO 等是否存在瓶颈 当分区较多 (如几十万甚至几百万) 或服务器硬件较差 (参考本文档**服务器硬件基本检查**部分) 时可能导致合并较慢。 从如下示例中，如因磁盘存在瓶颈，建议采用 NVMe SSD，**临时**缓解方式可以通过设置集群参数 `syslog_level` 为 `WARN` 以减少 IO。 **排查方式一 通过 tsar 命令** 命令如下。 ```shell tsar -l -i 1 ``` 相关参数解释如下： ```shell --live/-l running print live mode, which module will print --interval/-i specify intervals numbers, in minutes if with --live, it is in seconds ``` 可以看出 device mapper dm-2 和 dm-3 磁盘使用率较高，存在瓶颈。 ![image-20240905120028291](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/transfer-compaction/compaction/20250306ob4x-dump-merge-information03.png) **排查方式二 通过 iostat 命令** 命令如下。 ```shell iostat -x 1 -k ``` 相关参数解释如下： ```shell -x Display extended statistics. -k Display statistics in kilobytes per second. -m Display statistics in megabytes per second. ``` ![image-20240905120144528](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/transfer-compaction/compaction/20250306ob4x-dump-merge-information04.png) **通过 `lsblk` 查看 device mapper 对应的挂载点** 可以看出 device mapper dm-2 和 dm-3 对应的挂载点分别为 `/data/1` 和 `/data/log1`。 ```shell #lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT sda 8:0 0 1T 0 disk ├─sda1 8:1 0 1023G 0 part │ ├─klas-root 253:0 0 100G 0 lvm / │ ├─klas-swap 253:1 0 4G 0 lvm │ ├─klas-data_1 253:2 0 400G 0 lvm /data/1 │ ├─klas-data_log1 253:3 0 400G 0 lvm /data/log1 │ ├─klas-home 253:4 0 100G 0 lvm /home │ └─klas-tmp 253:5 0 19G 0 lvm /tmp └─sda2 8:2 0 1G 0 part /boot sr0 11:0 1 1024M 0 rom ``` #### 合理调整合并相关参数 合并默认使用 6 个线程，可以根据实际需要进行调整，详见上述**合并相关参数**部分。 ### 合并卡住问题 RS (Root Service ) 端判定合并结束的主要流程 1. 检查每个 zone 中的 tablet 版本号是否皆已推高至当前合并版本号，若是，则更新 `CDB_OB_ZONE_MAJOR_COMPACTION` 内部表中对应 Zone 的 LAST_SCN，并将 STATUS 置为 IDLE。 2. 对所有 tablet 进行副本间 CHECKSUM 校验、主表和索引表间 CHECKSUM 校验、主备租户 CHECKSUM 校验。 3. 成功完成上述两件事情之后，更新 `CDB_OB_MAJOR_COMPACTION` 内部表中的 LAST_SCN，并将 STATUS 置为 IDLE。 #### 判断是否卡住 **方式一 查询数据字典** 如果 `cdb_ob_major_compaction.status` 列一直处于 `COMPACTING` 状态 (远远超过**查询合并耗时**中的每日合并时间) ，或 `cdb_ob_major_compaction.is_error` 列值为 `CHECKSUM_ERROR`，则说明该租户的合并可能卡住了。 参考本文档以下部分： **查询合并状态** **查询 CHECKSUM 相关视图** **查询合并耗时** **方式二 查询 RS 日志** RS 日志中一直提示某个 tablet 'replica not merged'。 ```shell grep 'replica not merged' rootservice.log*|grep 'T1004_MergeSche' | vim - ``` 注：上述命令中的 1004 需要换成对应租户 ID。 #### 检查 TABLET 版本号是否都已推高 **方式一 查询数据字典** **查询 TABLET 是否合并到指定版本** 和 **获取租户的合并进度** **查询合并诊断信息视图** **查询 `__all_virtual_dag`** 可以在 Root Service leader (参考本文档**查询 Root Service leader** 部分) 所在的 OBServer 上查看日志，基于 'replica not merged' 关键字可以查看卡在哪一个 tablet 上了： ``` 可以发现，1004 租户下，tablet_id=50002 的 tablet，长时间 (一般超过10分钟) 处于 **A=1655899935260047146** 版本，而没有合并到指定的**B=1655900220014778135** 版本。 ![image-20240903145858295](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/transfer-compaction/compaction/20250306ob4x-dump-merge-information05.png) **如果采用查日志的方式**，则我们需要确认该 tablet 在内部表中的 `compaction_scn` 信息是否真的未合并到指定版本 B。 以 sys 租户下查询为例。 ```shell SET @tenant_id=1002; SET @tablet_id=1002; SELECT tenant_id, svr_ip, svr_port, tablet_id, compaction_scn, round(data_size / 1024 / 1024, 2) data_size_mb, round(required_size / 1024 / 1024, 2) required_size_mb FROM cdb_ob_tablet_replicas WHERE tenant_id = @tenant_id AND tablet_id = @tablet_id; ``` 此外，还可以在 RS 日志中查看是否存在 "unmerged_count=..." 相关日志。若存在，则表明依然存在 tablet 版本尚未推高至当前合并版本号。如图所示，11:06:52，1002 租户，z1 中还有 163434 个 tablet 副本版本尚未推高至当前合并版本号，z3 中还有 155841 个 tablet 副本版本尚未推高至当前合并版本号。 ```shell # 将 yyy 替换为对应的时间，将 1004 替换为对应租户的 ID grep "check updating merge status" rootservice.log.xxx | grep T1004 ``` ![image-202409031458582322](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/transfer-compaction/compaction/20250306ob4x-dump-merge-information06.png) #### 检查是否卡在 TABLET 校验 **方式一 查询数据字典** 如果 `cdb_ob_major_compaction.is_error` 列值为 `CHECKSUM_ERROR`，则说明该租户的合并可能卡在校验了。 可以在 Root Service leader (参考本文档**查询 Root Service leader** 部分) 所在的 OBServer 上查看日志，基于 'exists unverified tables' 关键字查看是否存在相关日志，若存在，则表明依然存在 table 尚未完成 checksum 校验。 如图所示，1004 租户存在 506674 号 table 和 736983 号 table 尚未完成 CHECKSUM 校验。 通过如下命令查看 RS 日志。 ```shell # 将 yyy 替换为对应的时间，将 1004 替换为对应租户的 ID grep "exists unverified tables" rootservice.log.yyy | grep T1004 ``` ![image-20240903145858223395](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/transfer-compaction/compaction/20250306ob4x-dump-merge-information07.png) ### 合并未发起问题 排查方向： 确认集群参数 `enable_major_freeze` 是否打开。 检查每日合并是否与 DDL 冲突，导致未发起每日合并。 如果其 checksum 校验失败，则不允许其发起下一轮的 major freeze，参考**查询 CHECKSUM 相关视图**部分。 #### 检查 CHECKSUM 是否失败 如果其 CHECKSUM 校验失败，则不允许其发起下一轮的 major freeze。 在解决了数据一致性问题后，通过 `alter system clear merge error` 来清除 checksum_error 状态 **检查是否卡在 TABLET 校验** #### 检查是否发起租户合并失败 可以在 observer.log 中搜索 `refresh merge info`： ```shell grep 'refresh merge info' observer.log.2022*|vim - ``` 如下说明 RS 对该租户调度合并失败： ![image-20240903145858222295](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/transfer-compaction/compaction/20250306ob4x-dump-merge-information08.png) ### 服务器硬件基本检查 #### 检查磁盘是否为 SSD ```shell df -hT lsblk # 检查磁盘是否为 SSD，rota (rotational) 为 0 是固态硬盘 SSD, 为 1 是机械硬盘 HDD lsblk -d -o name,rota,model ``` #### 检查网卡是否为万兆网卡 ```shell # 查看 IP 对应网卡 ip a s up # 查看 IP 对应网卡是否为万兆网卡 Speed: 1000Mb/s or Speed: 10000Mb/s ethtool eth0 ``` #### 简单测试磁盘性能 简单测试磁盘性能方法如下。 ```shell # 步骤一，检查磁盘是否为 SSD，rota (rotational) 为 0 是固态硬盘SSD, 为 1 是机械硬盘HDD lsblk -o name,rota,model # 步骤二，检查 /data/1/ 磁盘性能，Linux 命令为 DISK=/data/1/ DEVICE=`df -Th "${DISK}" | tail -n 1 | awk '{print $1}'` ;echo "${DEVICE}" time for i in `seq 1 1000`; do dd bs=4k if="${DEVICE}" count=1 skip=$(( $RANDOM * 128 )) >/dev/null 2>&1 ;done # 步骤三，检查 /data/log1/ 磁盘性能，Linux 命令为 DISK=/data/log1/ DEVICE=`df -Th "${DISK}" | tail -n 1 | awk '{print $1}'` ;echo "${DEVICE}" time for i in `seq 1 1000`; do dd bs=4k if="${DEVICE}" count=1 skip=$(( $RANDOM * 128 )) >/dev/null 2>&1 ;done ``` 以上简单检查磁盘性能方法，SSD 盘预期耗时 1s 左右，HDD 盘预期耗时 6s-10s 甚至更长时间。 ## 影响租户 影响 OceanBase 数据库中的 sys 租户和 Oracle 租户以及 MySQL 租户。 ## 适用版本 OceanBase 数据库 V4.2.x 版本。 Previous [Minor/Mini SSTable 的 UPPER_TRANS_VERSION 长时间算不出来](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002762604) Next [OBServer major merge 超时，日志 Lob writer 不断报错](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002075780) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线