You can use the PL package DBMS_BALANCE to set weights for table groups with Sharding = 'NONE'. This allows different table groups to be distributed according to their weights, enabling automatic distribution of databases aggregated into those table groups based on their weights.
Limitations and considerations
Currently, weights can only be set for table groups with a Sharding attribute of
NONE.When setting table group weights, it is recommended to have as few weight tiers as possible. It is suggested to divide them into at most three tiers:
High weight = 100% * total number of table groups with
Sharding = 'NONE'Medium weight = 50% * total number of table groups with
Sharding = 'NONE'Low weight = 1
For the default table group created when a new database is created after modifying the parameter
enable_database_sharding_none=True, its weight is 1. OtherSharding = 'NONE'table groups that have not had their weights set have a default weight of 0 and do not participate in weight balancing.For table groups with assigned weights, all table-level and partition-level weights within the table group become invalid. For more information about table-level and partition-level weights, see Set partition weights.
Set weights
Log in to the user tenant of the cluster.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A(Optional) Create a table group with
Sharding = 'NONE'.For example, create a table group named
TG_DB_test.obclient(root@mysql001)[test]> CREATE TABLEGROUP TG_DB_test Sharding = 'NONE';Set the table group weights based on your business requirements.
Table group weights must be integers and can range from 1 to +∞. To set the weight of a table group to 10, use the following statement:
obclient(root@mysql001)[test]> CALL DBMS_BALANCE.SET_TABLEGROUP_BALANCE_WEIGHT(10,'TG_DB_test');The
systenant can also be used to set weights for table groups belonging to a specified user tenant, using the following statement:obclient(root@sys)[(none)]> CALL DBMS_BALANCE.SET_TABLEGROUP_BALANCE_WEIGHT(10,'TG_DB_test') TENANT = 'mysql001';After completing the settings, check the weights of the table groups.
obclient(root@mysql001)[test]> SELECT TABLEGROUP_NAME, WEIGHT, TABLEGROUP_ID, OBJECT_ID FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT WHERE TABLEGROUP_NAME = 'TG_DB_test';The query result is as follows:
+-----------------+--------+---------------+-----------+ | TABLEGROUP_NAME | WEIGHT | TABLEGROUP_ID | OBJECT_ID | +-----------------+--------+---------------+-----------+ | TG_DB_test | 10 | 500007 | 500007 | +-----------------+--------+---------------+-----------+ 1 row in set
What to do next
After setting table group weights, the system does not immediately perform weight balancing. If you want to trigger weight balancing as soon as possible, you can manually initiate a partition balancing by calling the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE subprogram. For more information about how to manually trigger partition balancing, see Manually trigger partition balancing.
Alternatively, you can wait for the scheduled partition balancing task to be triggered. For more information about scheduled partition balancing tasks, see Configure a scheduled partition balancing task.
References
For more information about application scenarios of table group weights, see Application scenarios of table group weight balancing.