Application scenarios of table group weight balancing|V4.4.2| docs|Distributed Database

Application scenarios of table group weight balancing

Last Updated：2026-03-06 07:02:41 Updated

This section introduces the application of table group weight balancing in daily business through several typical scenarios.

Scenario 1: Automatic aggregation when creating tables in the same database

Scenario description

You need to create a batch of user tables and manage them by database to avoid distributed read/write operations within the same database.

Procedure

First enable the automatic aggregation feature for user tables in the new database by setting the parameter enable_database_sharding_none to True, then create Database and table. The specific steps are as follows:

Connect to the MySQL-compatible tenant as the tenant administrator.

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@mysql_tenant#obdemo -p***** -A
```

Set the tenant-level parameter enable_database_sharding_none to True.

obclient(root@mysql001)[(none)]> ALTER SYSTEM SET enable_database_sharding_none = True;

Create multiple databases as needed.

After each database is created, it will automatically bind to a new table group with Sharding = 'NONE'.

For more information about how to create a database, see Create a database.
Create user tables in batches by database. User tables in the same database will automatically be bound to the same log stream.

Scenario 2: Aggregating tables across multiple databases

Scenario description

You need to create a batch of user tables that are closely associated with user tables in Database A. Assume that Database A has been bound to a table group with Sharding = 'NONE'. You need to aggregate the newly created tables with the user tables in Database A to avoid distributed read and write operations across multiple databases.

Procedure

You can create a new database, Database B, and bind it to the same Sharding = 'NONE' table group as Database A. Then, create user tables in Database B. The specific steps are as follows:

Connect to the MySQL-compatible tenant as the tenant administrator.

Here is an example of the connection command. Please replace the actual environment parameters as needed.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -A
```

Query the table group information to which Database A is bound.

obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.DBA_OB_DATABASES WHERE DATABASE_NAME = 'databse_name';

Create a new database, Database B, and specify its table group as the same one to which Database A is bound.
```
CREATE DATABASE databae_name DEFAULT tablegroup = tablegroup_name;
```
For more information about the CREATE DATABASE statement, see CREATE DATABASE.
Create user tables in Database B. These tables will automatically be bound to the same log stream as the user tables in Database A.

Scenario 3: Re-aggregation of multiple databases

Scenario description

The user table has been created. Now, the user tables across multiple databases need to be re-aggregated to avoid distributed read and write operations across databases.

Procedure

Connect to the MySQL-compatible tenant as the tenant administrator.

Here is an example of the connection command. Please replace the parameters with those in your environment.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -A
```
Create a table group with Sharding = 'NONE' using the following statement.
```
CREATE TABLEGROUP tablegroup_name Sharding = 'NONE';
```
Bind the multiple databases to the newly created table group using the following statement.
```
ALTER DATABASE database_name tablegroup [=] tablegroup_name;
```
For more information about the ALTER DATABASE statement, see ALTER DATABASE.
Manually trigger partition balancing.
```
obclient(root@mysql001)[(none)]> CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE();
```
For more information about how to manually trigger partition balancing, see Manually trigger a partition balancing task.
Wait for the partition balancing task to complete. The user tables across multiple databases are then re-aggregated.

During partition balancing, you can view the progress of the balancing task by querying the following view:
```
obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.DBA_OB_BALANCE_JOBS WHERE JOB_TYPE = 'PARTITION_BALANCE';
```
For more information about how to view the progress of a partition balancing task, see View the progress of a partition balancing task.

Scenario 4: Hot table group exclusivity

Scenario description

After enabling the auto-aggregation feature for user tables in newly created databases (by setting the enable_database_sharding_none parameter to True), each new database will automatically create a table group with Sharding = 'NONE' (with a default weight of 1). To ensure that the hot table group TG_DB_1 exclusively uses a log stream, we need to adjust its weight.

Procedure

Assume the current tenant has 9 Sharding = 'NONE' table groups: TG_DB_1 to TG_DB_9. Among them, TG_DB_1 aggregates user tables from multiple databases and is identified as the hot table group. We want it to exclusively use a log stream.

Currently, the weight distribution of these table groups is as follows:

Initial weight distribution of table groups

To make TG_DB_1 exclusive, we can set its weight to 100% of the total weight of all Sharding = 'NONE' table groups. Here's how to do it:

Connect to the database as the tenant administrator in MySQL-compatible mode.

Here's an example command. Please replace the parameters with those in your actual environment.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -A
```

Verify the total number of Sharding = 'NONE' table groups with weights in the tenant.

obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT;

Set the weight of the hot table group TG_DB_1.

Assuming the query result shows 9 Sharding = 'NONE' table groups with weights, the SQL statement to set the weight of TG_DB_1 is as follows:
```
obclient(root@mysql001)[(none)]> CALL DBMS_BALANCE.SET_TABLEGROUP_BALANCE_WEIGHT(9,'TG_DB_1');
```
Manually trigger a partition balance.
```
obclient(root@mysql001)[(none)]> CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE();
```
For more information about manually triggering a partition balance, see Manually trigger a partition balance.
Wait for the partition balance task to complete. This will aggregate user tables across multiple databases.

During the partition balance, you can monitor the progress using the following statement:
```
obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.DBA_OB_BALANCE_JOBS WHERE JOB_TYPE = 'PARTITION_BALANCE';
```
For more information about viewing the progress of a partition balance task, see View the progress of a partition balance task.

After the partition balance is complete, the weight distribution of the table groups will be as follows:

Weight distribution after hot table group exclusivity

Scenario 5: Table groups distributed by weight

Scenario description

After enabling the auto-aggregation feature for the user table in the new database (by setting the parameter enable_database_sharding_none to True), a table group with Sharding = 'NONE' is automatically created for each new database, with a default weight of 1. To better distribute user tables, the goal is to spread out the table groups that have a larger number of tables, higher data volume, or greater access traffic.

Procedure

Assume that the current tenant has 9 Sharding = 'NONE' table groups named TG_DB_1 to TG_DB_9. Among them, TG_DB_1, TG_DB_2, and TG_DB_4 have a large amount of data, but not enough to occupy a dedicated log stream.

Currently, the weight distribution of these table groups is as follows.

Initial weight distribution of table groups

You can set the weights of TG_DB_1, TG_DB_2, and TG_DB_4 to the middle weight, which is 50% of the total number of Sharding = 'NONE' table groups. The specific steps are as follows:

Connect to the database as the tenant administrator of the MySQL-compatible tenant.

Here is an example of the connection command. Please replace the parameters with those in your environment.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -A
```

Query the total number of Sharding = 'NONE' table groups with weights.

obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT;

Set the weights of TG_DB_1, TG_DB_2, and TG_DB_4.

Assuming that the total number of Sharding = 'NONE' table groups with weights is 9, the weights of TG_DB_1, TG_DB_2, and TG_DB_4 are set to 4 (the result of 50% * 9 rounded down). The statements are as follows:

obclient(root@mysql001)[(none)]> CALL DBMS_BALANCE.SET_TABLEGROUP_BALANCE_WEIGHT(4,'TG_DB_1');

obclient(root@mysql001)[(none)]> CALL DBMS_BALANCE.SET_TABLEGROUP_BALANCE_WEIGHT(4,'TG_DB_2');

obclient(root@mysql001)[(none)]> CALL DBMS_BALANCE.SET_TABLEGROUP_BALANCE_WEIGHT(4,'TG_DB_4');

Manually trigger partition balancing.
```
obclient(root@mysql001)[(none)]> CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE();
```
For more information about how to manually trigger partition balancing, see Manually trigger a partition balancing task.
Wait for the partition balancing task to complete to aggregate the user tables across multiple databases.

During the partition balancing process, you can view the progress of the balancing task by querying the view. The statement is as follows:
```
obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.DBA_OB_BALANCE_JOBS WHERE JOB_TYPE = 'PARTITION_BALANCE';
```
For more information about how to view the progress of a partition balancing task, see View the progress of a partition balancing task.

After the balancing is completed, the weight distribution of these table groups is as follows:

Final weight distribution of table groups