Set table group weights

You can use the DBMS_BALANCE package in the PL system to set weights for table groups with Sharding = 'NONE', enabling different table groups to distribute according to their weights, so that databases that are automatically aggregated to table groups are distributed by weight.

Limitations and considerations

• Currently, you can set the weight for a table group only if the Sharding attribute of the table group is NONE.

• When you set the weight for a table group, we recommend that you set the weight to only a few levels. We recommend that you set the weight to at most three levels:

  • High weight = 100% * the number of table groups with the Sharding attribute of NONE

  • Medium weight = 50% * the number of table groups with the Sharding attribute of NONE

  • Low weight = 1

• After you set the enable_database_sharding_none parameter to True, the default weight of a table group created in a new database is 1. The default weight of a table group with the Sharding attribute of NONE that has not been set is 0. Therefore, the table group does not participate in weight balancing.

• For a table group with a weight, the weights of tables and partitions in the table group are invalid. For more information about how to set the weights of tables and partitions, see Set the partition weight.

Set the weight

Currently, you can set the weight for a table group only in MySQL-compatible mode.

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

   The following example shows how to connect to the database. Replace the actual values with those in your environment.

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

2. (Optional) Create a table group with the Sharding attribute of NONE.

   For example, create a table group named TG_DB_test.

   obclient(root@mysql001)[test]> CREATE TABLEGROUP TG_DB_test Sharding = 'NONE';
   

3. Set the weight for the table group based on your business requirements.

   The weight of a table group must be an integer in the [1, +∞) range. For example, you can set the weight of the TG_DB_test table group to 10.

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

   You can also set the weight for a table group of a specified user tenant as the sys tenant. For example, you can set the weight of the TG_DB_test table group of the mysql001 tenant to 10.

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

4. View the weight of the table group.

   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 the table group weights, the system does not immediately perform weight balancing. If you want to initiate weight balancing sooner, you can call the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE procedure to manually trigger a partition balancing task. For more information on manually triggering a partition balancing task, see Manually trigger a partition balancing task.

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

References

For application cases of table group weights, see Table group weight balancing application cases.