Aggregation of user tables refers to binding specified user tables under the same tenant to the same log stream. OceanBase AI Database achieves aggregation for all user tables under a database by binding the database to a table group with Sharding = 'NONE'. Similarly, aggregation of user tables across multiple databases can be achieved by binding multiple databases to the same table group.
Enable automatic aggregation for user tables under a new database
The automatic aggregation switch for user tables under a database is controlled by the tenant-level parameter enable_database_sharding_none. By default, it is set to False, which means automatic aggregation is disabled.
You can enable automatic aggregation for user tables under a database based on your business needs. To enable it for a new database, follow these steps:
Connect to the database as the tenant administrator of the user tenant.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -ACheck 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 mode. | LOAD_BALANCE | TENANT | DEFAULT | DYNAMIC_EFFECTIVE | False | 1 | +-------+----------+----------------+----------+-------------------------------+-----------+-------+----------------------------------------------------------------------------------------+--------------+--------+---------+-------------------+---------------+-----------+ 1 row in setSet the value of the parameter
enable_database_sharding_nonetoTrue.obclient(root@mysql001)[(none)]> ALTER SYSTEM SET enable_database_sharding_none = True;
After enabling automatic aggregation for user tables under a database (enable_database_sharding_none is set to True), all databases created subsequently under that tenant will be bound to a table group with a Sharding attribute of NONE by default. The format of the table group name is: TG_DB_{database_id}.
Notice
After enabling automatic aggregation for user tables under a database:
- It takes effect only for databases created subsequently and does not affect existing databases.
- When creating a database, if the name of the default table group conflicts with a user-defined table group name, the database is created successfully but is not automatically bound to a table group. You must then use the
ALTER DATABASE TABLEGROUPstatement to bind it to a table group with a Sharding attribute ofNONE.
For example, assume that after enabling automatic aggregation for user tables under a database, a database named infotest is created.
obclient(root@mysql001)[(none)]> CREATE DATABASE infotest;
You can view the default table group for this database through the DBA_OB_DATABASES view (query the CDB_OB_DATABASES view under the sys tenant).
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 for this database is TG_DB_500004. Here, 500004 is the database ID.
Set up aggregation of user tables across multiple databases
You can modify the TABLEGROUP attribute of multiple databases to bind them under the same table group with Sharding = 'NONE', thereby enabling aggregation of user tables across multiple databases.
The procedure is as follows:
Connect to the database as the tenant administrator of the user tenant.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -AExecute the following statement to modify the
TABLEGROUPattribute of the target database:ALTER DATABASE [database_name] TABLEGROUP [=] tablegroup_name;where:
database_name: the name of the target database. This parameter is optional. If you do not specify this parameter, the current database is used.tablegroup_name: the name of the table group to which the database is bound. The table group must be a table group whose Sharding attribute isNONE.
This statement modifies the
TABLEGROUPattribute of all tables in the target database. If any table in the target database does not meet the distribution requirements of the table group's sharding, the statement fails to be executed.Here is an example:
obclient(root@mysql001)[infotest]> ALTER DATABASE infotest TABLEGROUP =tg_test;Confirm whether the table group has been successfully modified by querying the
DBA_OB_DATABASESview (orCDB_OB_DATABASESin thesystenant).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 setRepeat the previous steps to bind multiple target databases to the same table group.
What to do next
After modifying 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 user tables as soon as possible, you can manually trigger a 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 trigger. For information about scheduled partition balancing tasks, see Configure a scheduled partition balancing task.
References
For more information about application scenarios of user table aggregation, see Application scenarios of table group weight balancing.