--- title: 负载均衡任务查询-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于负载均衡任务查询相关的常见问题和使用技巧，帮助您快速解决负载均衡任务查询的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 负载均衡任务查询 更新时间：2026-07-01 08:46 适用版本： V4.1.x、V4.2.x 内容类型：TechNote OceanBase 数据库 V4.2 版本是首个完整实现单机分布式一体化架构的版本，具有里程碑意义。OceanBase 采用分区转移技术实现了日志流的分裂和合并，支持了以分区为单位进行跨节点的数据搬迁，完善了[负载均衡](https://open.oceanbase.com/blog/7727361312)策略，补齐了可扩展性能力，真正实现了单机和分布式形态的动态变换。 本文将介绍在租户水平扩缩容 (如 1-1-1 集群扩展为 2-2-2 集群或[修改 Primary Zone](https://www.oceanbase.com/docs/common-ocp-1000000001740029)) 和分区均衡 (如设置 [Table Group](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220199)) 等情况时如何查询负载均衡任务。 ## 详细说明 本文以 1-1-1 集群 MySQL 租户[修改 Primary Zone](https://www.oceanbase.com/docs/common-ocp-1000000001740029) 为例，展示如何查询负载均衡任务。 除特别说明外，如下 SQL 语法适用于 MySQL 模式和 Oracle 模式。 ### 负载均衡任务相关视图 从如下图中可以直观地了解负载均衡任务之间的关系： ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/cluster-management/20250220load-balancing-task-query.png) 相关 DBA 视图如下： | **视图名称** | **说明** | | --- | --- | | DBA_OB_TABLE_LOCATIONS | 分区副本分布，包含分区详细信息，如 `table_name`、`partition_name`、`subpartition_name` 等 | | DBA_OB_LS_LOCATIONS | 日志流副本分布 | | DBA_OB_BALANCE_JOBS | 正在执行的负载均衡任务，如扩容、缩容、分区均衡等 | | DBA_OB_BALANCE_JOB_HISTORY | 负载均衡任务历史 | | DBA_OB_BALANCE_TASKS | 正在执行的 LS 均衡任务，如 LS 分裂、LS 合并等 | | DBA_OB_BALANCE_TASK_HISTORY | LS 均衡任务历史 | | DBA_OB_TRANSFER_TASKS | 正在执行的 transfer 任务，tablet 级别 | | DBA_OB_TRANSFER_TASK_HISTORY | transfer 任务历史 | 其中负载均衡任务 DBA 视图对应关联关系为： `dba_ob_balance_jobs.job_id` - `dba_ob_balance_tasks.job_id` `dba_ob_balance_tasks.task_id` - `dba_ob_transfer_tasks.balance_task_id` ### 创建测试用例 为方便演示，在用户租户下创建测试表如下。 MySQL 租户下语法如下： ```shell CREATE TABLE sales ( sale_id INT NOT NULL, region INT, product INT, quantity INT, sale_date DATE, PRIMARY KEY (sale_id, region,product) ) PARTITION BY LIST (region) SUBPARTITION BY HASH (product) SUBPARTITIONS 3 ( PARTITION p0 VALUES IN (0), PARTITION p1 VALUES IN (1) ); ``` Oracle 租户下语法如下： ```shell CREATE TABLE sales ( sale_id NUMBER NOT NULL, region NUMBER, product NUMBER, quantity NUMBER, sale_date DATE, PRIMARY KEY (sale_id, region, product) ) PARTITION BY LIST (region) SUBPARTITION BY HASH (product) SUBPARTITIONS 3 ( PARTITION p0 VALUES (0), PARTITION p1 VALUES (1) ); ``` 创建测试表后查询各分区位置信息。 ```shell SELECT table_name, table_id, object_id, partition_name, subpartition_name, svr_ip, role, tablet_id, ls_id FROM dba_ob_table_locations WHERE role = 'LEADER' -- AND database_name = 'test' AND lower(table_name) = 'sales' ORDER BY table_name, tablet_id; ``` 查询结果如下： ```shell +------------+----------+-----------+----------------+-------------------+---------------+--------+-----------+-------+ | table_name | table_id | object_id | partition_name | subpartition_name | svr_ip | role | tablet_id | ls_id | +------------+----------+-----------+----------------+-------------------+---------------+--------+-----------+-------+ | sales | 500002 | 500005 | p0 | p0sp0 | 10.10.20.20 | LEADER | 200001 | 1001 | | sales | 500002 | 500006 | p0 | p0sp1 | 10.10.20.20 | LEADER | 200002 | 1001 | | sales | 500002 | 500007 | p0 | p0sp2 | 10.10.20.20 | LEADER | 200003 | 1001 | | sales | 500002 | 500008 | p1 | p1sp0 | 10.10.20.20 | LEADER | 200004 | 1001 | | sales | 500002 | 500009 | p1 | p1sp1 | 10.10.20.20 | LEADER | 200005 | 1001 | | sales | 500002 | 500010 | p1 | p1sp2 | 10.10.20.20 | LEADER | 200006 | 1001 | +------------+----------+-----------+----------------+-------------------+---------------+--------+-----------+-------+ 6 rows in set (0.07 sec) ``` ### 查询负载均衡配置参数 首先，在用户租户下查询，当如下参数均为 `True` 时才会进行正常的分区均衡操作。 ```shell SHOW PARAMETERS LIKE 'enable_rebalance'; SHOW PARAMETERS LIKE 'enable_transfer'; ``` ### 查询 Primary Zone 信息 通过如下 SQL 查询 Primary Zone 信息。 ```shell SELECT tenant_id,tenant_name,tenant_type,primary_zone FROM dba_ob_tenants ORDER BY tenant_id DESC; ``` 可以看到当前 Primary Zone 为 `zone1`。 ```shell +-----------+-------------+-------------+-------------------+ | tenant_id | tenant_name | tenant_type | primary_zone | +-----------+-------------+-------------+-------------------+ | 1074 | obmysql | USER | zone1;zone2;zone3 | +-----------+-------------+-------------+-------------------+ 1 row in set (0.04 sec) ``` ### 查询日志流信息 通过如下 SQL 查询用户租户日志流信息。 ```shell SELECT ls_id, svr_ip, svr_port, zone, role, member_list FROM dba_ob_ls_locations WHERE role = 'LEADER' AND ls_id > 1 ORDER BY ls_id; ``` 可以看到 1-1-1 集群下当 Primary Zone 为 `zone1` 时，仅有一条用户租户的日志流。 ```shell +-------+---------------+----------+-------+--------+----------------------------------------------------------------+ | ls_id | svr_ip | svr_port | zone | role | member_list | +-------+---------------+----------+-------+--------+----------------------------------------------------------------+ | 1001 | 10.10.20.20 | 2882 | zone1 | LEADER | 10.10.20.20:2882:1,10.10.20.21:2882:1,10.10.20.22:2882:1 | +-------+---------------+----------+-------+--------+----------------------------------------------------------------+ 1 row in set (0.05 sec) ``` ### 查询各分区 LEADER 分布 通过如下 SQL 查询各分区 LEADER 分布。 ```shell SELECT ls_id, zone, svr_ip, svr_port, role, count(1) FROM dba_ob_table_locations WHERE role = 'LEADER' AND ls_id > 1 GROUP BY ls_id, zone, svr_ip, svr_port, role ORDER BY ls_id, zone, svr_ip, svr_port, role; ``` 从如下查询结果可以看出，当前所有 LEADER 都在 `zone1`。 ```shell +-------+-------+---------------+----------+--------+----------+ | ls_id | zone | svr_ip | svr_port | role | count(1) | +-------+-------+---------------+----------+--------+----------+ | 1001 | zone1 | 10.10.20.20 | 2882 | LEADER | 6 | +-------+-------+---------------+----------+--------+----------+ 1 row in set (0.05 sec) ``` ### 修改 Primary Zone 接下来[修改 Primary Zone](https://www.oceanbase.com/docs/common-ocp-1000000001740029)，将 Primary Zone 从 `zone1;zone2;zone3` 修改为 `zone1,zone2,zone3`，即打散所有分区。 ### 查看负载均衡任务 通过如下 SQL 查询正在执行的负载均衡任务。 ```shell SELECT job_id, create_time, balance_strategy, job_type, target_unit_num, target_primary_zone_num, status FROM dba_ob_balance_jobs ORDER BY create_time DESC; ``` 从如下查询结果中的 `balance_strategy` 值 `LS balance by expand` 可以看出此为扩容任务。 ```shell +--------+----------------------------+----------------------+------------+-----------------+-------------------------+--------+ | job_id | create_time | balance_strategy | job_type | target_unit_num | target_primary_zone_num | status | +--------+----------------------------+----------------------+------------+-----------------+-------------------------+--------+ | 13308 | 2025-01-10 15:42:18.584286 | LS balance by expand | LS_BALANCE | 1 | 3 | DOING | +--------+----------------------------+----------------------+------------+-----------------+-------------------------+--------+ 1 row in set (0.04 sec) ``` 根据 `dba_ob_balance_jobs.job_id` 查询正在执行的 LS (Log Stream，日志流) 均衡任务。 ```shell SELECT * FROM dba_ob_balance_tasks WHERE job_id = 13308 ORDER BY job_id DESC, task_id DESC\G ``` 从如下查询结果中的 `SRC_LS` 和 `DEST_LS` 可以看出正在从 LS 1001 中分裂出 LS 1002 和 LS 1003，并可以根据 `PART_COUNT` 和 `FINISHED_PART_COUNT` 查看执行的进度。 ```shell *************************** 1. row *************************** TASK_ID: 13312 CREATE_TIME: 2025-01-10 15:42:18.589251 MODIFY_TIME: 2025-01-10 15:42:18.589251 TASK_TYPE: LS_SPLIT SRC_LS: 1001 DEST_LS: 1003 PART_LIST: 500002:500006,500002:500009 FINISHED_PART_LIST: NULL PART_COUNT: 2 FINISHED_PART_COUNT: 0 LS_GROUP_ID: 1001 STATUS: INIT PARENT_LIST: 13310 CHILD_LIST: NULL CURRENT_TRANSFER_TASK_ID: -1 JOB_ID: 13308 COMMENT: NULL BALANCE_STRATEGY: LS balance by expand *************************** 2. row *************************** TASK_ID: 13310 CREATE_TIME: 2025-01-10 15:42:18.588026 MODIFY_TIME: 2025-01-10 15:42:21.597227 TASK_TYPE: LS_SPLIT SRC_LS: 1001 DEST_LS: 1002 PART_LIST: 500002:500007,500002:500010 FINISHED_PART_LIST: NULL PART_COUNT: 2 FINISHED_PART_COUNT: 0 LS_GROUP_ID: 1001 STATUS: CREATE_LS PARENT_LIST: NULL CHILD_LIST: 13312 CURRENT_TRANSFER_TASK_ID: -1 JOB_ID: 13308 COMMENT: Wait for status of LS 1002 to change from CREATED to NORMAL BALANCE_STRATEGY: LS balance by expand 2 rows in set (0.03 sec) ``` 然后根据 `dba_ob_balance_tasks.task_id` 查询。 ```shell SELECT * FROM dba_ob_transfer_tasks WHERE balance_task_id = 13310 ORDER BY balance_task_id desc,task_id DESC\G ``` 从如下查询结果可以看到正在执行从 LS 1001 到 LS 1002 的 transfer 任务，并从 `PART_LIST` (格式为 `dba_ob_table_locations.table_id`:`dba_ob_table_locations.object_id`) 可以看到正在迁移的对象。 ```shell *************************** 1. row *************************** TASK_ID: 1 CREATE_TIME: 2025-01-10 15:42:23.613131 MODIFY_TIME: 2025-01-10 15:42:23.613131 SRC_LS: 1001 DEST_LS: 1002 PART_LIST: 500002:500010,500002:500007 PART_COUNT: 2 NOT_EXIST_PART_LIST: NULL LOCK_CONFLICT_PART_LIST: NULL TABLE_LOCK_TABLET_LIST: NULL TABLET_LIST: NULL TABLET_COUNT: 0 START_SCN: 0 FINISH_SCN: 0 STATUS: INIT TRACE_ID: YB420BA6506F-00062B440C68B60E-0-0 RESULT: -1 BALANCE_TASK_ID: 13310 TABLE_LOCK_OWNER_ID: -1 COMMENT: 1 row in set (0.04 sec) ``` 执行完后继续执行从 LS 1001 到 LS 1003 的 transfer 任务。 ```shell mysql> SELECT * FROM dba_ob_transfer_tasks WHERE balance_task_id = 13310 ORDER BY balance_task_id desc,task_id DESC\G *************************** 1. row *************************** TASK_ID: 2 CREATE_TIME: 2025-01-10 15:42:30.831545 MODIFY_TIME: 2025-01-10 15:42:30.831545 SRC_LS: 1001 DEST_LS: 1003 PART_LIST: 500002:500009,500002:500006 PART_COUNT: 2 NOT_EXIST_PART_LIST: NULL LOCK_CONFLICT_PART_LIST: NULL TABLE_LOCK_TABLET_LIST: NULL TABLET_LIST: NULL TABLET_COUNT: 0 START_SCN: 0 FINISH_SCN: 0 STATUS: INIT TRACE_ID: YB420BA6506F-00062B440C68B616-0-0 RESULT: -1 BALANCE_TASK_ID: 13312 TABLE_LOCK_OWNER_ID: -1 COMMENT: 1 row in set (0.04 sec) ``` 执行完后可以从 history 表中查到负载均衡历史任务。 SELECT * FROM dba_ob_balance_tasks ORDER BY job_id DESC, task_id DESC\G SELECT * FROM dba_ob_transfer_tasks ORDER BY balance_task_id desc,task_id DESC\G SELECT job_id, create_time, finish_time, balance_strategy, job_type, target_unit_num, target_primary_zone_num, status FROM dba_ob_balance_job_history ORDER BY create_time DESC; SELECT * FROM dba_ob_balance_task_history ORDER BY job_id DESC, task_id DESC\G SELECT * FROM dba_ob_transfer_task_history ORDER BY balance_task_id desc,task_id DESC\G ``` 查询结果如下： ```shell mysql> SELECT job_id, create_time, balance_strategy, job_type, target_unit_num, target_primary_zone_num, status FROM dba_ob_balance_jobs ORDER BY create_time DESC; Empty set (0.04 sec) mysql> SELECT * FROM dba_ob_balance_tasks ORDER BY job_id DESC, task_id DESC\G Empty set (0.04 sec) mysql> SELECT * FROM dba_ob_transfer_tasks ORDER BY balance_task_id desc,task_id DESC\G Empty set (0.04 sec) SELECT * FROM dba_ob_transfer_task_history ORDER BY balance_task_id desc,task_id DESC\G *************************** 1. row *************************** job_id: 13308 create_time: 2025-01-10 15:42:18.584286 finish_time: 2025-01-10 15:42:38.663886 balance_strategy: LS balance by expand job_type: LS_BALANCE target_unit_num: 1 target_primary_zone_num: 3 status: COMPLETED 2 rows in set (0.04 sec) mysql> SELECT * FROM dba_ob_balance_task_history ORDER BY job_id DESC, task_id DESC\G *************************** 1. row *************************** TASK_ID: 13312 CREATE_TIME: 2025-01-10 15:42:18.589251 FINISH_TIME: 2025-01-10 15:42:35.961833 TASK_TYPE: LS_SPLIT SRC_LS: 1001 DEST_LS: 1003 PART_LIST: NULL FINISHED_PART_LIST: 500002:500006,500002:500009 PART_COUNT: 0 FINISHED_PART_COUNT: 2 LS_GROUP_ID: 1001 STATUS: COMPLETED PARENT_LIST: NULL CHILD_LIST: NULL CURRENT_TRANSFER_TASK_ID: -1 JOB_ID: 13308 COMMENT: NULL BALANCE_STRATEGY: LS balance by expand *************************** 2. row *************************** TASK_ID: 13310 CREATE_TIME: 2025-01-10 15:42:18.588026 FINISH_TIME: 2025-01-10 15:42:26.804661 TASK_TYPE: LS_SPLIT SRC_LS: 1001 DEST_LS: 1002 PART_LIST: NULL FINISHED_PART_LIST: 500002:500007,500002:500010 PART_COUNT: 0 FINISHED_PART_COUNT: 2 LS_GROUP_ID: 1001 STATUS: COMPLETED PARENT_LIST: NULL CHILD_LIST: 13312 CURRENT_TRANSFER_TASK_ID: -1 JOB_ID: 13308 COMMENT: NULL BALANCE_STRATEGY: LS balance by expand 2 rows in set (0.03 sec) mysql> SELECT * FROM dba_ob_transfer_task_history ORDER BY balance_task_id desc,task_id DESC\G *************************** 1. row *************************** TASK_ID: 2 CREATE_TIME: 2025-01-10 15:42:30.831545 FINISH_TIME: 2025-01-10 15:42:35.867446 SRC_LS: 1001 DEST_LS: 1003 PART_LIST: 500002:500006,500002:500009 PART_COUNT: 2 NOT_EXIST_PART_LIST: NULL LOCK_CONFLICT_PART_LIST: NULL TABLE_LOCK_TABLET_LIST: 200002,200005 TABLET_LIST: 200002:0,200005:0 TABLET_COUNT: 2 START_SCN: 1736494954291675329 FINISH_SCN: 1736494955611670066 STATUS: COMPLETED TRACE_ID: YB420BA6506F-00062B440C68B616-0-0 RESULT: 0 BALANCE_TASK_ID: 13312 TABLE_LOCK_OWNER_ID: 13433 COMMENT: *************************** 2. row *************************** TASK_ID: 1 CREATE_TIME: 2025-01-10 15:42:23.613131 FINISH_TIME: 2025-01-10 15:42:26.384991 SRC_LS: 1001 DEST_LS: 1002 PART_LIST: 500002:500007,500002:500010 PART_COUNT: 2 NOT_EXIST_PART_LIST: NULL LOCK_CONFLICT_PART_LIST: NULL TABLE_LOCK_TABLET_LIST: 200003,200006 TABLET_LIST: 200003:0,200006:0 TABLET_COUNT: 2 START_SCN: 1736494945188087952 FINISH_SCN: 1736494946145749986 STATUS: COMPLETED TRACE_ID: YB420BA6506F-00062B440C68B60E-0-0 RESULT: 0 BALANCE_TASK_ID: 13310 TABLE_LOCK_OWNER_ID: 13356 COMMENT: 2 rows in set (0.03 sec) ``` ### 再次查询 Primary Zone 信息 通过如下 SQL 查询。 ``` 从如下查询结果可以看出，Primary Zone 已变为 `zone1,zone2,zone3`。 ```shell +-----------+-------------+-------------+-------------------+ | tenant_id | tenant_name | tenant_type | primary_zone | +-----------+-------------+-------------+-------------------+ | 1074 | obmysql | USER | zone1,zone2,zone3 | +-----------+-------------+-------------+-------------------+ 1 row in set (0.04 sec) ``` ### 再次查询日志流信息 通过如下 SQL 查询。 ``` 从如下查询结果可以看出，当前已有三个日志流。 ```shell +-------+---------------+----------+-------+--------+----------------------------------------------------------------+ | ls_id | svr_ip | svr_port | zone | role | member_list | +-------+---------------+----------+-------+--------+----------------------------------------------------------------+ | 1001 | 10.10.20.20 | 2882 | zone1 | LEADER | 10.10.20.20:2882:1,10.10.20.21:2882:1,10.10.20.22:2882:1 | | 1002 | 10.10.20.21 | 2882 | zone2 | LEADER | 10.10.20.20:2882:1,10.10.20.21:2882:1,10.10.20.22:2882:1 | | 1003 | 10.10.20.22 | 2882 | zone3 | LEADER | 10.10.20.20:2882:1,10.10.20.21:2882:1,10.10.20.22:2882:1 | +-------+---------------+----------+-------+--------+----------------------------------------------------------------+ 3 rows in set (0.04 sec) ``` ### 再次查询各分区 LEADER 分布 通过如下 SQL 查询各分区 LEADER 分布。 ``` 从如下查询结果可以看出，当前所有 LEADER 都已均匀分布。 ```shell +-------+-------+---------------+----------+--------+----------+ | ls_id | zone | svr_ip | svr_port | role | count(1) | +-------+-------+---------------+----------+--------+----------+ | 1001 | zone1 | 10.10.20.20 | 2882 | LEADER | 2 | | 1002 | zone2 | 10.10.20.21 | 2882 | LEADER | 2 | | 1003 | zone3 | 10.10.20.22 | 2882 | LEADER | 2 | +-------+-------+---------------+----------+--------+----------+ 3 rows in set (0.05 sec) ``` ## 影响租户 影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。 ## 适用版本 OceanBase 数据库 V4.x 版本。 上一篇 [Unit 在服务器之间自动地频繁来回迁移](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005437575) 下一篇 [OceanBase 数据库 V4.x 版本中 ADAPTIVE 表组使用示例及手动触发负载均衡任务](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002830178) ![有帮助](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) 咨询热线