划线反馈

负载均衡任务查询

更新时间：2026-07-01 08:46

适用版本：V4.1.x、V4.2.x内容类型：TechNote

OceanBase 数据库 V4.2 版本是首个完整实现单机分布式一体化架构的版本，具有里程碑意义。OceanBase 采用分区转移技术实现了日志流的分裂和合并，支持了以分区为单位进行跨节点的数据搬迁，完善了负载均衡策略，补齐了可扩展性能力，真正实现了单机和分布式形态的动态变换。

本文将介绍在租户水平扩缩容 (如 1-1-1 集群扩展为 2-2-2 集群或修改 Primary Zone) 和分区均衡 (如设置 Table Group) 等情况时如何查询负载均衡任务。

详细说明

本文以 1-1-1 集群 MySQL 租户修改 Primary Zone 为例，展示如何查询负载均衡任务。

除特别说明外，如下 SQL 语法适用于 MySQL 模式和 Oracle 模式。

负载均衡任务相关视图

从如下图中可以直观地了解负载均衡任务之间的关系：

视图名称	说明
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 任务历史

创建测试用例

为方便演示，在用户租户下创建测试表如下。

MySQL 租户下语法如下：

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 租户下语法如下：

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

创建测试表后查询各分区位置信息。

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;

查询结果如下：

+------------+----------+-----------+----------------+-------------------+---------------+--------+-----------+-------+
| 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 时才会进行正常的分区均衡操作。

SHOW PARAMETERS LIKE 'enable_rebalance';
SHOW PARAMETERS LIKE 'enable_transfer';

查询 Primary Zone 信息

通过如下 SQL 查询 Primary Zone 信息。

SELECT tenant_id,tenant_name,tenant_type,primary_zone FROM dba_ob_tenants ORDER BY tenant_id DESC;

可以看到当前 Primary Zone 为 zone1。

+-----------+-------------+-------------+-------------------+
| tenant_id | tenant_name | tenant_type | primary_zone      |
+-----------+-------------+-------------+-------------------+
|      1074 | obmysql     | USER        | zone1;zone2;zone3 |
+-----------+-------------+-------------+-------------------+
1 row in set (0.04 sec)

查询日志流信息

通过如下 SQL 查询用户租户日志流信息。

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 时，仅有一条用户租户的日志流。

+-------+---------------+----------+-------+--------+----------------------------------------------------------------+
| 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 分布。

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。

+-------+-------+---------------+----------+--------+----------+
| 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，将 Primary Zone 从 zone1;zone2;zone3 修改为 zone1,zone2,zone3，即打散所有分区。

查看负载均衡任务

通过如下 SQL 查询正在执行的负载均衡任务。

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 可以看出此为扩容任务。

+--------+----------------------------+----------------------+------------+-----------------+-------------------------+--------+
| 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，日志流) 均衡任务。

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 查看执行的进度。

*************************** 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 查询。

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) 可以看到正在迁移的对象。

*************************** 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 任务。

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
  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;

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

查询结果如下：

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 查询。

SELECT tenant_id,tenant_name,tenant_type,primary_zone FROM dba_ob_tenants ORDER BY tenant_id DESC;

从如下查询结果可以看出，Primary Zone 已变为 zone1,zone2,zone3。

+-----------+-------------+-------------+-------------------+
| tenant_id | tenant_name | tenant_type | primary_zone      |
+-----------+-------------+-------------+-------------------+
|      1074 | obmysql     | USER        | zone1,zone2,zone3 |
+-----------+-------------+-------------+-------------------+
1 row in set (0.04 sec)

再次查询日志流信息

通过如下 SQL 查询。

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;

从如下查询结果可以看出，当前已有三个日志流。

+-------+---------------+----------+-------+--------+----------------------------------------------------------------+
| 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 分布。

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 都已均匀分布。

+-------+-------+---------------+----------+--------+----------+
| 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 在服务器之间自动地频繁来回迁移

OceanBase 数据库 V4.x 版本中 ADAPTIVE 表组使用示例及手动触发负载均衡任务

OceanBase 数据库