Aggregation of user tables in a database

Aggregation of user tables refers to binding the specified user tables in the same tenant to the same log stream. OceanBase Database implements aggregation of all user tables in a database by binding the database to a table group with Sharding = 'NONE'. In addition, you can also implement aggregation of user tables in multiple databases by binding multiple databases to the same table group.

Limitations

Currently, only MySQL-compatible mode supports aggregation of user tables in a database. Oracle-compatible mode does not support this feature.

Enable automatic aggregation of user tables in a new database

The switch for automatic aggregation of user tables in a database is controlled by the tenant-level configuration parameter enable_database_sharding_none. The default value is False, which indicates that automatic aggregation of user tables in a database is disabled.

You can enable automatic aggregation of user tables in a database based on your business needs. To enable automatic aggregation of user tables in a new database, perform the following steps:

1. Connect to the database as the tenant administrator of a MySQL tenant.

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

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

2. Query the value of the tenant-level configuration parameter enable_database_sharding_none.

   obclient(root@mysql001)[(none)]> SHOW PARAMETERS LIKE '%enable_database_sharding_none%';
   

   The query result is as follows:

   +-------+----------+----------------+----------+-------------------------------+-----------+-------+----------------------------------------------------------------------------------------+--------------+--------+---------+-------------------+---------------+-----------+
   | zone  | svr_type | svr_ip         | svr_port | name                          | data_type | value | info                                                                                   | section      | scope  | source  | edit_level        | default_value | isdefault |
   +-------+----------+----------------+----------+-------------------------------+-----------+-------+----------------------------------------------------------------------------------------+--------------+--------+---------+-------------------+---------------+-----------+
   | zone1 | observer | 172.xx.xxx.xxx |     2882 | enable_database_sharding_none | BOOL      | False | Enable automatic creation of sharding none tablegroup for new databases in MySQL-compatible mode. | LOAD_BALANCE | TENANT | DEFAULT | DYNAMIC_EFFECTIVE | False         |         1 |
   +-------+----------+----------------+----------+-------------------------------+-----------+-------+----------------------------------------------------------------------------------------+--------------+--------+---------+-------------------+---------------+-----------+
   1 row in set
   

3. Set the value of the configuration parameter enable_database_sharding_none to True.

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

After automatic aggregation of user tables in a database is enabled (the value of enable_database_sharding_none is True), all databases created in the tenant are bound to a table group with the NONE Sharding attribute by default, and the name of the table group is in the format TG_DB_{database_id}.

For example, assume that after automatic aggregation of user tables in a database is enabled, you create a database named infotest.

obclient(root@mysql001)[(none)]> CREATE DATABASE infotest;

You can query the DBA_OB_DATABASES view (in the sys tenant, query the CDB_OB_DATABASES view) to view the default table group of the database.

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

+---------------+---------------+--------------------+-----------+---------+-----------------+
| DATABASE_NAME | IN_RECYCLEBIN | COLLATION          | READ_ONLY | COMMENT | TABLEGROUP_NAME |
+---------------+---------------+--------------------+-----------+---------+-----------------+
| infotest      | NO            | utf8mb4_general_ci | NO        |         | TG_DB_500004    |
+---------------+---------------+--------------------+-----------+---------+-----------------+
1 row in set

According to the query result, the default table group of the database is TG_DB_500004, where 500004 is the ID of the database.

Aggregate user tables in multiple databases

You can modify the TABLEGROUP attribute of multiple databases to bind them to the same table group with Sharding = 'NONE', to aggregate the user tables in multiple databases.

Perform the following steps:

1. Connect to the database as the tenant administrator of a MySQL tenant.

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

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

2. Execute the following statement to modify the TABLEGROUP attribute of the target database.

   The statement is as follows:

   ALTER DATABASE [database_name] TABLEGROUP [=] tablegroup_name;
   

   The parameters are described as follows:

   • database_name: the name of the target database. This parameter is optional. If it is not specified, the database to be modified is the current database.

   • tablegroup_name: the name of the table group with the NONE Sharding attribute to be bound.

   • The statement modifies the TABLEGROUP attribute of all tables in the target database.

   • If the distribution of tables in the target database cannot meet the Sharding requirements of the table group, an error is returned when the statement is executed.

   Here is an example:

   obclient(root@mysql001)[infotest]> ALTER DATABASE infotest TABLEGROUP =tg_test;
   

3. Query the DBA_OB_DATABASES view (in the sys tenant, query the CDB_OB_DATABASES view) to check whether the table group is modified successfully.

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

   The query result is as follows:

   +---------------+---------------+--------------------+-----------+---------+-----------------+
   | DATABASE_NAME | IN_RECYCLEBIN | COLLATION          | READ_ONLY | COMMENT | TABLEGROUP_NAME |
   +---------------+---------------+--------------------+-----------+---------+-----------------+
   | infotest      | NO            | utf8mb4_general_ci | NO        |         | tg_test         |
   +---------------+---------------+--------------------+-----------+---------+-----------------+
   1 row in set
   

4. Repeat the preceding operations to bind multiple target databases to the same table group.

What to do next

After you modify the tablegroup attribute of a database, the system does not immediately align the partitions of all user tables to the same log stream. If you want to aggregate the user tables 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.

References

For an application case of user table aggregation, see Application scenarios of table group weight balancing.