表设置了tablegroup属性后，为什么不会聚集到某个zone的observer上呢

bull020 · 2025 年7 月 22 日 09:46

ob：ce-4.2.1.8

mysql> select * from oceanbase.dba_ob_zones;
+-------+----------------------------+----------------------------+--------+------+----------+-----------+
| ZONE  | CREATE_TIME                | MODIFY_TIME                | STATUS | IDC  | REGION   | TYPE      |
+-------+----------------------------+----------------------------+--------+------+----------+-----------+
| zone1 | 2025-07-11 16:45:32.610645 | 2025-07-19 22:32:42.072684 | ACTIVE | idc1 | HANGZHOU | ReadWrite |
| zone2 | 2025-07-11 16:45:32.611700 | 2025-07-22 09:32:52.408101 | ACTIVE | idc1 | HANGZHOU | ReadWrite |
| zone3 | 2025-07-11 16:45:32.611700 | 2025-07-22 09:32:57.479488 | ACTIVE | idc1 | HANGZHOU | ReadWrite |
+-------+----------------------------+----------------------------+--------+------+----------+-----------+

mysql> select * from oceanbase.dba_ob_tenants where tenant_name='test' \G
*************************** 1. row ***************************
                 TENANT_ID: 1014
               TENANT_NAME: test
               TENANT_TYPE: USER
               CREATE_TIME: 2025-07-21 18:34:54.538432
               MODIFY_TIME: 2025-07-21 18:35:18.347468
              PRIMARY_ZONE: zone1,zone2,zone3
                  LOCALITY: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3
         PREVIOUS_LOCALITY: NULL
        COMPATIBILITY_MODE: MYSQL
                    STATUS: NORMAL
             IN_RECYCLEBIN: NO
                    LOCKED: NO
               TENANT_ROLE: PRIMARY
         SWITCHOVER_STATUS: NORMAL
          SWITCHOVER_EPOCH: 0
                  SYNC_SCN: 1753148742326969483
            REPLAYABLE_SCN: 1753148742326969483
              READABLE_SCN: 1753148742326969483
        RECOVERY_UNTIL_SCN: 4611686018427387903
                  LOG_MODE: NOARCHIVELOG
ARBITRATION_SERVICE_STATUS: DISABLED
                  UNIT_NUM: 1
                COMPATIBLE: 4.2.1.8
                 MAX_LS_ID: 1003
1 row in set (0.02 sec)

mysql> select * from oceanbase.DBA_OB_TABLEGROUP_TABLES where owner='test';
+-----------------+-------+------------+----------+
| TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
+-----------------+-------+------------+----------+
| tbg3            | test  | sbtest3    | NONE     |
| tbg3            | test  | sbtest1    | NONE     |
| tbg3            | test  | sbtest2    | NONE     |
+-----------------+-------+------------+----------+
3 rows in set (0.01 sec)

mysql> select table_name, partition_name, svr_ip, role from oceanbase.dba_ob_table_locations where database_name='test' and table_name like 'sbtest%' and role='leader' order by 1,2;
+------------+----------------+--------------+--------+
| table_name | partition_name | svr_ip       | role   |
+------------+----------------+--------------+--------+
| sbtest1    | NULL           | 10.xx.xx.221 | LEADER |
| sbtest2    | NULL           | 10.xx.xx.220 | LEADER |
| sbtest3    | NULL           | 10.xx.xx.232 | LEADER |
+------------+----------------+--------------+--------+
3 rows in set (0.01 sec)

而sbtest1 、sbtest2、sbtest3都是非分区表，如下：

       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 100001 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 TABLEGROUP = 'tbg3'

疑问：
为什么sbtest1 sbtest2 sbtest3 不能聚合在某个zone的某个observer上呢？

来轩 · 2025 年7 月 22 日 09:49

enable_rebalance和enbale_transfer参数是打开的吗

bull020 · 2025 年7 月 22 日 09:52

叶飞 · 2025 年7 月 22 日 10:09

执行平衡任务，需要一点时间，是不是时间没到

淇铭 · 2025 年7 月 22 日 10:18

根据表组查询一下信息
select * from DBA_OB_TABLE_LOCATIONS where tablegroup_name = ‘tblgroup1’;

bull020 · 2025 年7 月 22 日 10:22

mysql> select DATABASE_NAME,TABLE_NAME,TABLE_ID,PARTITION_NAME,TABLET_ID,LS_ID,ZONE,ROLE,SVR_IP from oceanbase.DBA_OB_TABLE_LOCATIONS where tablegroup_name = 'tbg3'  order by 2;
+---------------+------------+----------+----------------+-----------+-------+-------+----------+--------------+
| DATABASE_NAME | TABLE_NAME | TABLE_ID | PARTITION_NAME | TABLET_ID | LS_ID | ZONE  | ROLE     | SVR_IP       |
+---------------+------------+----------+----------------+-----------+-------+-------+----------+--------------+
| test          | sbtest1    |   500003 | NULL           |    200002 |  1002 | zone3 | FOLLOWER | 10.**.**.232 |
| test          | sbtest1    |   500003 | NULL           |    200002 |  1002 | zone2 | LEADER   | 10.**.**.221 |
| test          | sbtest1    |   500003 | NULL           |    200002 |  1002 | zone1 | FOLLOWER | 10.**.**.220 |
| test          | sbtest2    |   500002 | NULL           |    200001 |  1001 | zone3 | FOLLOWER | 10.**.**.232 |
| test          | sbtest2    |   500002 | NULL           |    200001 |  1001 | zone2 | FOLLOWER | 10.**.**.221 |
| test          | sbtest2    |   500002 | NULL           |    200001 |  1001 | zone1 | LEADER   | 10.**.**.220 |
| test          | sbtest3    |   500004 | NULL           |    200003 |  1003 | zone3 | LEADER   | 10.**.**.232 |
| test          | sbtest3    |   500004 | NULL           |    200003 |  1003 | zone2 | FOLLOWER | 10.**.**.221 |
| test          | sbtest3    |   500004 | NULL           |    200003 |  1003 | zone1 | FOLLOWER | 10.**.**.220 |
+---------------+------------+----------+----------------+-----------+-------+-------+----------+--------------+

淇铭 · 2025 年7 月 22 日 10:36

查一下这个参数
show parameters like ‘%partition_balance_schedule_interval%’;

SELECT * FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE TENANT_ID = 1002 AND JOB_TYPE = ‘PARTITION_BALANCE’;
SELECT * FROM oceanbase.CDB_OB_BALANCE_TASKS WHERE TENANT_ID = 1002 AND BALANCE_STRATEGY LIKE ‘%partition%’;
SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASKS WHERE TENANT_ID = 1002\G
先查一下信息

bull020 · 2025 年7 月 22 日 10:53

这个上面有贴，是20s

mysql> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE TENANT_ID = 1014 AND JOB_TYPE = 'PARTITION_BALANCE';
Empty set (0.01 sec)

mysql> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOB_HISTORY WHERE TENANT_ID = 1014 AND JOB_TYPE = 'PARTITION_BALANCE';
+-----------+--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
| TENANT_ID | JOB_ID | CREATE_TIME                | FINISH_TIME                | BALANCE_STRATEGY  | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS    | COMMENT |
+-----------+--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
|      1014 | 305363 | 2025-07-22 10:35:46.557257 | 2025-07-22 10:35:56.567106 | partition balance | PARTITION_BALANCE |               1 |                       3 | COMPLETED | NULL    |
+-----------+--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
1 row in set (0.01 sec)

mysql> SELECT * FROM oceanbase.CDB_OB_BALANCE_TASKS WHERE TENANT_ID = 1014 AND BALANCE_STRATEGY LIKE '%partition%';
ERROR 1054 (42S22): Unknown column 'BALANCE_STRATEGY' in 'where clause'
mysql> 
mysql> 
mysql> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE TENANT_ID = 1014 AND BALANCE_STRATEGY LIKE '%partition%';
Empty set (0.00 sec)

mysql> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOB_HISTORY WHERE TENANT_ID = 1014 AND BALANCE_STRATEGY LIKE '%partition%';
+-----------+--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
| TENANT_ID | JOB_ID | CREATE_TIME                | FINISH_TIME                | BALANCE_STRATEGY  | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS    | COMMENT |
+-----------+--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
|      1014 | 305363 | 2025-07-22 10:35:46.557257 | 2025-07-22 10:35:56.567106 | partition balance | PARTITION_BALANCE |               1 |                       3 | COMPLETED | NULL    |
+-----------+--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
1 row in set (0.02 sec)

mysql> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASKS WHERE TENANT_ID = 1014\G
Empty set (0.01 sec)

mysql> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASK_HISTORY WHERE TENANT_ID = 1014\G
*************************** 1. row ***************************
              TENANT_ID: 1014
                TASK_ID: 1
            CREATE_TIME: 2025-07-22 10:35:47.756442
            FINISH_TIME: 2025-07-22 10:35:48.617697
                 SRC_LS: 1003
                DEST_LS: 1001
              PART_LIST: 500004:500004
             PART_COUNT: 1
    NOT_EXIST_PART_LIST: NULL
LOCK_CONFLICT_PART_LIST: NULL
 TABLE_LOCK_TABLET_LIST: NULL
            TABLET_LIST: 200003:0,1152921504606846977:0
           TABLET_COUNT: 2
              START_SCN: 1753151747787454856
             FINISH_SCN: 1753151748046777584
                 STATUS: COMPLETED
               TRACE_ID: YB420A1012DC-000639A39D566A0B-0-0
                 RESULT: 0
        BALANCE_TASK_ID: 305364
    TABLE_LOCK_OWNER_ID: 305377
                COMMENT: 
*************************** 2. row ***************************
              TENANT_ID: 1014
                TASK_ID: 2
            CREATE_TIME: 2025-07-22 10:35:51.094365
            FINISH_TIME: 2025-07-22 10:35:52.582982
                 SRC_LS: 1002
                DEST_LS: 1001
              PART_LIST: 500003:500003
             PART_COUNT: 1
    NOT_EXIST_PART_LIST: NULL
LOCK_CONFLICT_PART_LIST: NULL
 TABLE_LOCK_TABLET_LIST: NULL
            TABLET_LIST: 200002:0,1152921504606846978:0
           TABLET_COUNT: 2
              START_SCN: 1753151751132132622
             FINISH_SCN: 1753151752098028439
                 STATUS: COMPLETED
               TRACE_ID: YB420A1012DC-000639A39D566A0F-0-0
                 RESULT: 0
        BALANCE_TASK_ID: 305365
    TABLE_LOCK_OWNER_ID: 305397
                COMMENT: 
2 rows in set (0.01 sec)

mysql> SELECT * FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASKS WHERE TENANT_ID = 1014\G
Empty set (0.01 sec)

mysql> SELECT * FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TENANT_ID = 1014\G
Empty set (0.01 sec)

查询日志的时候发现是刚刚完成的，然后

mysql> select table_name, partition_name, svr_ip, role from oceanbase.dba_ob_table_locations where database_name='test' and table_name like 'sbtest%' and role='leader' order by 1,2;
+------------+----------------+--------------+--------+
| table_name | partition_name | svr_ip       | role   |
+------------+----------------+--------------+--------+
| sbtest1    | NULL           | 10.xx.xx.220 | LEADER |
| sbtest2    | NULL           | 10.xx.xx.220 | LEADER |
| sbtest3    | NULL           | 10.xx.xx.220 | LEADER |
+------------+----------------+--------------+--------+
3 rows in set (0.02 sec)

感觉是partition_balance_schedule_interval这个参数没有生效，默认值2H，确实是过了大概2H才正常。

淇铭 · 2025 年7 月 22 日 11:33

但是从任务查看的信息创建和完成时间很短你查一下这个参数 show parameters like ‘%balancer_idle_time%’;

bull020 · 2025 年7 月 22 日 11:35

淇铭 · 2025 年7 月 22 日 14:06

partition_balance_schedule_interval 这个参数代表的是每2h做一次分区均衡并不代表两个小时内一定能完成但是从历史CDB_OB_BALANCE_JOB_HISTORY CDB_OB_TRANSFER_TASK_HISTORY信息开始时间结束的时间并不是很长

bull020 · 2025 年7 月 22 日 14:50

嗯，理解就是 partition_balance_schedule_interval单位时间内触发一次partition调度

淇铭 · 2025 年7 月 22 日 15:10

可以这么理解 partition_balance_schedule_interval为时间间隔默认从observer启动时间算起，每2h做一次分区均衡。

bull020 · 2025 年7 月 22 日 15:16

partition_balance_schedule_interval=‘100s’ 不生效
有没有什么办法手动立马触发一次partition rebalance呢？
要不然很痛苦，做一些测试，每次都要等2H左右的样子

淇铭 · 2025 年7 月 22 日 15:29

这个修改完不需要重启会立即生效的你是怎么判断不生效的

bull020 · 2025 年7 月 22 日 15:32

上面1楼说的那些，修改的tablegroup属性，发现分区没有在 {partition_balance_schedule_interval} 时间段内（均衡时间可以忽略）聚合在一起(某个zone的observer上)，需要等2H才会聚合在一起

淇铭 · 2025 年7 月 22 日 15:36

你查询的CDB_OB_BALANCE_JOB_HISTORY CDB_OB_TRANSFER_TASK_HISTORY 执行的开始时间是你调整完 partition_balance_schedule_interval间隔时间执行的么？

bull020 · 2025 年7 月 22 日 15:39

不是
我早上2025-07-22 09:17:33设置的tablegroup属性，*_history表中查询到的是10:35开始和完成的。
而当时 partition_balance_schedule_interval是设置的 ‘20s’

淇铭 · 2025 年7 月 22 日 15:46

partition_balance_schedule_interval 这个配置项是在当前的业务租户下设置的吧

HK · 2025 年7 月 22 日 15:47

大赞！