Set table group weights

You can use the PL package DBMS_BALANCE to set weights for table groups with Sharding = 'NONE', so that different table groups are distributed according to their weights, thereby achieving weighted distribution of databases automatically aggregated into those table groups.

Limitations and considerations

• Currently, only MySQL-compatible mode supports setting table group weights.

• In the current version, weights can only be set for table groups with SHARDING = 'NONE' + SCOPE = 'SERVER' or SHARDING = 'NONE' + SCOPE = 'ZONE'. Table groups with SHARDING = 'NONE' + SCOPE = 'CLUSTER' do not support weight setting.

• When setting table group weights, it is recommended to minimize the number of weight tiers. 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 default table groups created when a new database is created after modifying the parameter enable_database_sharding_none=True, the weight is set to 1. Other Sharding = 'NONE' table groups that have not had their weights set previously 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 operations and descriptions related to table-level and partition-level weights, see Set partition weights.

Set weights

Currently, only MySQL-compatible mode supports setting table group weights.

1. Log in to a MySQL-compatible tenant of the cluster.

   The following is an example connection string. Please adjust it according to your actual database configuration.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
   

2. (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'; 
   

   Note

   In the current version, if you create a table group with Sharding = 'NONE' without specifying the SCOPE attribute, it defaults to creating a table group with SHARDING = 'NONE' + SCOPE = 'SERVER'.

3. Set the table group weights based on your business requirements.

   Table group weights must be integers, with a range of [1, +∞). To set a table group's weight to 10, use the following statement:

   obclient(root@mysql001)[test]> CALL DBMS_BALANCE.SET_TABLEGROUP_BALANCE_WEIGHT(10,'TG_DB_test');
   

   The sys tenant can also set weights for table groups of a specified user tenant. The statement is as follows:

   obclient(root@sys)[(none)]> CALL DBMS_BALANCE.SET_TABLEGROUP_BALANCE_WEIGHT(10,'TG_DB_test') TENANT = 'mysql001';
   

4. After completion, view the weight information 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 trigger a partition balance by calling the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE subprogram. For detailed operations on manually triggering partition balance, see Manually trigger partition balancing.

Alternatively, you can wait for the scheduled partition balancing task to be triggered. For information about scheduled partition balancing tasks, see Configure a scheduled partition balancing task.

References

For application scenarios of table group weights, see Application scenarios of table group weight balancing.