Clear table group weights

After table group weights are set, you can clear the set table group weights based on your business needs.

Notes

Clearing table group weights deletes the weights of the table group. If table-level or partition-level weights are set for tables in the table group, the system retains the table-level and partition-level weights.

Clear weights

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

   Here is an example of the connection command. Replace the parameters with those in your actual environment.

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

2. Get the table group weight information.

   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
   

3. Clear the table group weights.

   Execute the following statement to clear the weights of the TG_DB_test table group:

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

   The sys tenant can also clear the weights of the table group for the specified user tenant mysql001. The statement is as follows:

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

4. After the table group weights are cleared, you can query the table group weight information again.

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

   If the query result is empty, the table group weights are cleared successfully.

What to do next

After the table group weights are cleared, the distribution of partitions in tables of the table group may be unbalanced. If you want to perform partition balancing as soon as possible, you can manually trigger a round of 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 information about scheduled partition balancing tasks, see Configure a scheduled partition balancing task.