Aggregation of user tables in a database

Aggregation of user tables in a database refers to binding specified user tables of the same tenant to the same log stream. OceanBase Database achieves this by binding the database to a table group with Sharding = 'NONE'. Additionally, multiple databases can be bound to the same table group to aggregate user tables across these databases.

Limitations

Currently, the aggregation feature for user tables under Database is only supported in MySQL-compatible mode, and is not supported in Oracle-compatible mode.

Enable automatic aggregation of user tables under a database

The automatic aggregation of user tables under a database is controlled by the tenant-level parameter enable_database_sharding_none, which defaults to False, indicating that automatic aggregation is disabled.

You can enable automatic aggregation of user tables under a database based on your business needs. To enable it, follow these steps:

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

   Here is an example of how to connect to the database. Please adjust the connection details according to your actual environment.

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

2. Query the value of the tenant 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 parameter enable_database_sharding_none to True.

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

After you enable automatic aggregation of user tables in a database (enable_database_sharding_none is set to True), all databases created subsequently under this tenant will be bound to a table group with the NONE sharding attribute by default. The name of the table group follows the format TG_DB_{database_id}.

For example, after you enable automatic aggregation of user tables in a database, you create a database named infotest.

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

You can query the DBA_OB_DATABASES view (or CDB_OB_DATABASES view for the sys tenant) to view the default table group of this 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

The query result shows that the default table group of this database is TG_DB_500004. In this example, 500004 is the ID of the database.

Aggregate user tables across multiple databases

You can modify the TABLEGROUP attribute of multiple databases to bind them to the same Sharding = 'NONE' tablegroup, thereby aggregating user tables across multiple databases.

Here are the steps:

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

   Here is an example of the connection command. Please adjust it based on your actual environment.

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

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

   The syntax is as follows:

   ALTER DATABASE [database_name] TABLEGROUP [=] tablegroup_name;
   

   Here, the parameters are described as follows:

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

   • tablegroup_name: the name of the tablegroup with the Sharding = 'NONE' attribute to bind to.

   • This statement will modify the TABLEGROUP attribute of all tables in the target database.

   • If there are tables in the target database that do not meet the distribution requirements of the tablegroup's sharding attribute, the statement will fail.

   Here is an example:

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

3. Query the DBA_OB_DATABASES view (or CDB_OB_DATABASES view for the sys tenant) to confirm whether the tablegroup was 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 above steps to bind multiple target databases to the same tablegroup.

What to do next

After modifying the tablegroup attribute of the database, the system will not immediately align 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 partition balance by calling the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE procedure. For more information, see Manually trigger a partition balance.

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

References

For an application example of table aggregation, see Table group weight balancing application examples.