自动负载均衡

培训中心
#1

obclient(root@tenant1)[TEST_DB]> CREATE TABLEGROUP tbg1 SHARDING=‘NONE’;
ALTER TABLEGROUP tbg1 ADD TABLE np_t1,hp_t1;
SHOW TABLEGROUPS LIKE ‘tbg1’;Query OK, 0 rows affected (0.079 sec)

obclient(root@tenant1)[TEST_DB]> ALTER TABLEGROUP tbg1 ADD TABLE np_t1,hp_t1;
Query OK, 0 rows affected (0.094 sec)

obclient(root@tenant1)[TEST_DB]> SHOW TABLEGROUPS LIKE ‘tbg1’;
±----------------±-----------±--------------±---------+
| Tablegroup_name | Table_name | Database_name | Sharding |
±----------------±-----------±--------------±---------+
| tbg1 | hp_t1 | test_db | NONE |
| tbg1 | np_t1 | test_db | NONE |
±----------------±-----------±--------------±---------+
2 rows in set (0.012 sec)

obclient(root@tenant1)[TEST_DB]> SELECT TABLE_NAME, TABLE_ID, PARTITION_NAME, TABLET_ID, OBJECT_ID,
→ LS_ID, ZONE, SVR_IP
→ FROM oceanbase.DBA_OB_TABLE_LOCATIONS
→ WHERE DATABASE_NAME=‘TEST_DB’ AND ROLE=‘LEADER’
→ ORDER BY SVR_IP;
±-----------±---------±---------------±----------±----------±------±------±------------+
| TABLE_NAME | TABLE_ID | PARTITION_NAME | TABLET_ID | OBJECT_ID | LS_ID | ZONE | SVR_IP |
±-----------±---------±---------------±----------±----------±------±------±------------+
| hp_t1 | 500004 | p6 | 200008 | 500011 | 1001 | zone1 | 10.13.9.171 |
| hp_t1 | 500004 | p3 | 200005 | 500008 | 1001 | zone1 | 10.13.9.171 |
| hp_t1 | 500004 | p0 | 200002 | 500005 | 1001 | zone1 | 10.13.9.171 |
| hp_t1 | 500004 | p5 | 200007 | 500010 | 1003 | zone3 | 10.13.9.189 |
| hp_t1 | 500004 | p2 | 200004 | 500007 | 1003 | zone3 | 10.13.9.189 |
| np_t1 | 500003 | NULL | 200001 | 500003 | 1003 | zone3 | 10.13.9.189 |
| hp_t1 | 500004 | p7 | 200009 | 500012 | 1002 | zone2 | 10.13.9.193 |
| hp_t1 | 500004 | p4 | 200006 | 500009 | 1002 | zone2 | 10.13.9.193 |
| hp_t1 | 500004 | p1 | 200003 | 500006 | 1002 | zone2 | 10.13.9.193 |
±-----------±---------±---------------±----------±----------±------±------±------------+
9 rows in set (0.061 sec)

obclient(root@tenant1)[TEST_DB]> ALTER SYSTEM SET partition_balance_schedule_interval=‘10s’;
Query OK, 0 rows affected (0.562 sec)

obclient(root@tenant1)[TEST_DB]> SELECT TABLE_NAME, TABLE_ID, PARTITION_NAME, TABLET_ID, OBJECT_ID,
→ LS_ID, ZONE, SVR_IP
→ FROM oceanbase.DBA_OB_TABLE_LOCATIONS
→ WHERE DATABASE_NAME=‘TEST_DB’ AND ROLE=‘LEADER’
→ ORDER BY SVR_IP;
±-----------±---------±---------------±----------±----------±------±------±------------+
| TABLE_NAME | TABLE_ID | PARTITION_NAME | TABLET_ID | OBJECT_ID | LS_ID | ZONE | SVR_IP |
±-----------±---------±---------------±----------±----------±------±------±------------+
| hp_t1 | 500004 | p6 | 200008 | 500011 | 1003 | zone3 | 10.13.9.189 |
| hp_t1 | 500004 | p5 | 200007 | 500010 | 1003 | zone3 | 10.13.9.189 |
| hp_t1 | 500004 | p3 | 200005 | 500008 | 1003 | zone3 | 10.13.9.189 |
| hp_t1 | 500004 | p2 | 200004 | 500007 | 1003 | zone3 | 10.13.9.189 |
| hp_t1 | 500004 | p0 | 200002 | 500005 | 1003 | zone3 | 10.13.9.189 |
| np_t1 | 500003 | NULL | 200001 | 500003 | 1003 | zone3 | 10.13.9.189 |
| hp_t1 | 500004 | p7 | 200009 | 500012 | 1002 | zone2 | 10.13.9.193 |
| hp_t1 | 500004 | p4 | 200006 | 500009 | 1002 | zone2 | 10.13.9.193 |
| hp_t1 | 500004 | p1 | 200003 | 500006 | 1002 | zone2 | 10.13.9.193 |
±-----------±---------±---------------±----------±----------±------±------±------------+
9 rows in set (0.022 sec)

我这里的操作和实验手册里面的结果怎么不一样啊，我没到一个日志流里面

1 个赞
#2

注意一下实验里面的sys租户，还是用户租户，我之前遇到一毛一样的情形

#3

我用的是tenant1这个租户啊

#4

ob4.x 版本吗？
使用root@业务租户名，登录进去，执行手工触发：立即执行表组均衡命令，看看是否有效果？
call dbms_balance.trigger_partition_balance();

#5

这个是BP8以上的命令触发的，不行

#6

老师，你当时怎么操作的啊，我着急啊

#7

触发命令后，select partition_name,svr_ip from dba_ob_table_locations where table_name in (‘hp_t1’,‘np_t1’) and role = ‘LEADER’
没有聚合在一起吗？

看看有没有任务失败的记录：
select * from dba_ob_transfer_task_history;

#8

obclient(root@tenant1)[oceanbase]> select * from dba_ob_transfer_task_history;
±--------±---------------------------±---------------------------±-------±--------±------------------------------------------±-----------±--------------------±------------------------±-----------------------±---------------------------±-------------±--------------------±--------------------±----------±----------------------------------±-------±----------------±--------------------±--------+
| TASK_ID | CREATE_TIME | FINISH_TIME | SRC_LS | DEST_LS | PART_LIST | PART_COUNT | NOT_EXIST_PART_LIST | LOCK_CONFLICT_PART_LIST | TABLE_LOCK_TABLET_LIST | TABLET_LIST | TABLET_COUNT | START_SCN | FINISH_SCN | STATUS | TRACE_ID | RESULT | BALANCE_TASK_ID | TABLE_LOCK_OWNER_ID | COMMENT |
±--------±---------------------------±---------------------------±-------±--------±------------------------------------------±-----------±--------------------±------------------------±-----------------------±---------------------------±-------------±--------------------±--------------------±----------±----------------------------------±-------±----------------±--------------------±--------+
| 1 | 2025-09-16 14:17:33.328298 | 2025-09-16 14:17:45.122542 | 1001 | 1003 | 500003:500003 | 1 | NULL | NULL | NULL | 200001:0 | 1 | 1758003463375119782 | 1758003464416552878 | COMPLETED | YB420A0D09AB-00063EE2A5A78ABE-0-0 | 0 | 1379 | 1437 | |
| 2 | 2025-09-16 14:19:13.576098 | 2025-09-16 14:19:15.132223 | 1001 | 1003 | 500004:500005,500004:500008,500004:500011 | 3 | NULL | NULL | 200002,200005,200008 | 200002:0,200005:0,200008:0 | 3 | 1758003553819507790 | 1758003554950160206 | COMPLETED | YB420A0D09AB-00063EE2A5A78B22-0-0 | 0 | 1739 | 1751 | |
±--------±---------------------------±---------------------------±-------±--------±------------------------------------------±-----------±--------------------±------------------------±-----------------------±---------------------------±-------------±--------------------±--------------------±----------±----------------------------------±-------±----------------±--------------------±--------+
2 rows in set (0.013 sec)

确实没有失败的

#9

select partition_name,svr_ip from dba_ob_table_locations where table_name in (‘hp_t1’,‘np_t1’) and role = ‘LEADER’;
的输出结果是啥

#10

obclient(root@tenant1)[oceanbase]> select partition_name,svr_ip from oceanbase.dba_ob_table_locations where table_name in (‘hp_t1’,‘np_t1’) and role = ‘LEADEr’;
±---------------±------------+
| partition_name | svr_ip |
±---------------±------------+
| NULL | 10.13.9.189 |
| p0 | 10.13.9.189 |
| p1 | 10.13.9.193 |
| p2 | 10.13.9.189 |
| p3 | 10.13.9.189 |
| p4 | 10.13.9.193 |
| p5 | 10.13.9.189 |
| p6 | 10.13.9.189 |
| p7 | 10.13.9.193 |
±---------------±------------+
9 rows in set (0.054 sec)

#11

这有点奇怪啊，很少见，再检查下这两个参数看看：

  • enable_transfer
  • enable_rebalance
    看是不是都为ture.
    SELECT * from gv$ob_parameters where name = ‘enable_transfer’;
    SELECT * from gv$ob_parameters where name = ‘enable_rebalance’;
#12

obclient(root@tenant1)[oceanbase]> SELECT * from gv$ob_parameters where name = ‘enable_transfer’;
±------------±---------±------±-------±----------±----------------±----------±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------±------------------+
| SVR_IP | SVR_PORT | ZONE | SCOPE | TENANT_ID | NAME | DATA_TYPE | VALUE | INFO | SECTION | EDIT_LEVEL |
±------------±---------±------±-------±----------±----------------±----------±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------±------------------+
| 10.13.9.193 | 2882 | zone2 | TENANT | 1004 | enable_transfer | NULL | True | controls whether transfers are allowed in the tenant. This config does not take effect when enable_rebalance is disabled. Value: True:turned on False:turned off | LOAD_BALANCE | DYNAMIC_EFFECTIVE |
| 10.13.9.189 | 2882 | zone3 | TENANT | 1004 | enable_transfer | NULL | True | controls whether transfers are allowed in the tenant. This config does not take effect when enable_rebalance is disabled. Value: True:turned on False:turned off | LOAD_BALANCE | DYNAMIC_EFFECTIVE |
| 10.13.9.171 | 2882 | zone1 | TENANT | 1004 | enable_transfer | NULL | True | controls whether transfers are allowed in the tenant. This config does not take effect when enable_rebalance is disabled. Value: True:turned on False:turned off | LOAD_BALANCE | DYNAMIC_EFFECTIVE |
±------------±---------±------±-------±----------±----------------±----------±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------±------------------+
3 rows in set (0.019 sec)

obclient(root@tenant1)[oceanbase]>
obclient(root@tenant1)[oceanbase]> SELECT * from gv$ob_parameters where name = ‘enable_rebalance’;
±------------±---------±------±-------±----------±-----------------±----------±------±----------------------------------------------------------------------------------------------------±-------------±------------------+
| SVR_IP | SVR_PORT | ZONE | SCOPE | TENANT_ID | NAME | DATA_TYPE | VALUE | INFO | SECTION | EDIT_LEVEL |
±------------±---------±------±-------±----------±-----------------±----------±------±----------------------------------------------------------------------------------------------------±-------------±------------------+
| 10.13.9.189 | 2882 | zone3 | TENANT | 1004 | enable_rebalance | NULL | True | specifies whether the tenant load-balancing is turned on. Value: True:turned on False: turned off | LOAD_BALANCE | DYNAMIC_EFFECTIVE |
| 10.13.9.193 | 2882 | zone2 | TENANT | 1004 | enable_rebalance | NULL | True | specifies whether the tenant load-balancing is turned on. Value: True:turned on False: turned off | LOAD_BALANCE | DYNAMIC_EFFECTIVE |
| 10.13.9.171 | 2882 | zone1 | TENANT | 1004 | enable_rebalance | NULL | True | specifies whether the tenant load-balancing is turned on. Value: True:turned on False: turned off | LOAD_BALANCE | DYNAMIC_EFFECTIVE |
±------------±---------±------±-------±----------±-----------------±----------±------±----------------------------------------------------------------------------------------------------±-------------±------------------+
3 rows in set (0.017 sec)

都是true,z这些参数没有修改过

#13

我感觉你的均衡是不是已经生效了。

最开始你的输出是：

hp_t1 表的

  • p1,p4,p7 在 193上

  • p0,p3,p6 在 171上

  • p2,p5 在 189上

np_t1 表，没分区 在 189上

，后面的输出变成了

hp_t1 表的

  • p0,p2,p3,p5,p6 在 189上

  • p1,p4,p7 在 193 上

np_t1 表，没分区 在 189上

是不是分区的数据量大了？ 我看OB的负载均衡机制还是在调度中的。再次检查看看？