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 in MySQL-compatible mode.

• In V4.4.2:

  • In versions earlier than V4.4.2 BP1, you can set the weight for a table group only if the Sharding attribute of the table group is NONE.

  • In V4.4.2 BP1 and later, you can set the weight for a table group only if the Sharding attribute of the table group is SHARDING = 'NONE' and the Scope attribute is SCOPE = 'SERVER' or SCOPE = 'ZONE'. You cannot set the weight for a table group whose Sharding attribute is SHARDING = 'NONE' and Scope attribute is SCOPE = 'CLUSTER'.

• 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 the default table group created for a new database is 1. For other Sharding = 'NONE' table groups that have never had a table group weight set, the default weight is 0, and they do 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

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';
   

   Note

   In V4.4.2, for V4.4.2 BP1 and later versions, if you create a table group with Sharding = 'NONE' without specifying the SCOPE attribute, the default is to create a table group with SHARDING = 'NONE' and SCOPE = 'SERVER'.

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.