Set load balancing by using table groups

Concept of table group

A table group is a logical concept that refers to a set of tables.

All tables in the group must meet the following requirements:

• They must have the same locality (replica type, number, and location), primary zone (leader location and priority), and partitioning method.

• They must have the same number of partitions.

Concept of partition group

Assume that a table group named TG0 contains k tables: T1, T2, ..., and Tk, and each of them has m partitions:

• Partitions of T1: P11, P12, P13, P14, ..., and P1m

• Partitions of T2: P21, P22, P23, P24, ..., and P2m

• Partitions of T3: P31, P32, P33, P34, ..., and P3m

...

• Partitions of Tk: Pk1, Pk2, Pk3, Pk4, ..., and Pkm

A group of partitions with the same offset is referred to as a partition group. In this case, the TG0 table group contains m partition groups.

• pg1 contains partitions P11, P21, P31, P41, ..., and Pk1.

• pg2 contains partitions P12, P22, P32, P42, ..., and Pk2.

• pg3 contains partitions P13, P23, P33, P43, ..., and Pk3.

...

• pgm contains partitions P1m, P2m, P3m, P4m, ..., and Pkm.

Characteristics of a partition group

Based on the preceding description, the TG0 table group contains m partition groups: pg1, pg2, pg3, ..., and pgm. In OceanBase Database, partitions of the same partition group are likely to be modified in the same transaction. To concentrate modifications in each transaction on the same OBServer and reduce the occurrence of distributed transactions, RootService tries to schedule partitions of the same partition group to the same OBServer. Sample statement:

• Partitions P11, P21, P31, P41, ..., and Pk1 in pg1 are scheduled to the same OBServer.

• Partitions P12, P22, P32, P42, ..., and Pk2 in pg2 are scheduled to the same OBServer.

• Partitions P13, P23, P33, P43, ..., and Pk3 in pg3 are scheduled to the same OBServer.

...

• Partitions P1m, P2m, P3m, P4m, ..., and Pkm in pgm are scheduled to the same OBServer.

Create a table group

You can use the following SQL syntax to create a table group:

CREATE TABLEGROUP tablegroup_name
                  [opt_tablegroup_option_list] [opt_tg_partition_option]

opt_tablegroup_option_list:
tablegroup_option [tablegroup_option]

tablegroup_option:
LOCALITY [=] locality_name
| PRIMARY_ZONE [=] primary_zone_name

opt_tg_partition_option:
PARTITION BY
 HASH column_num [tg_subpartition_option] PARTITIONS int_num
| RANGE COLUMNS column_num [tg_subpartition_option] {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| LIST COLUMNS column_num [tg_subpartition_option] {PARTITION partition_name VALUES  list_partition_expr, ...}

tg_subpartition_option:
SUBPARTITION BY
RANGE COLUMNS column_num SUBPARTITION TEMPLATE {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| HASH column_num [SUBPARTITIONS INTNUM]
| LIST COLUMNS column_num SUBPARTITION TEMPLATE {SUBPARTITION partition_name VALUES  list_partition_expr, ...}

Field description:

• tablegroupname: the name of the table group, which contains at most 64 characters in length. It contains only uppercase and lowercase letters, digits, and underscores (). The name must start with a letter or an underscore () and must not contain reserved keywords of OceanBase Database.

• opt_tablegroup_option_list: the partitioning method of the table group.

  The locality and primary zone of the table group must be consistent with those of the tables in the group. You cannot modify a single item in a table. You must perform batch operations on the entire table group.

  Consistent locality means that the replica type, quantity, and location must be consistent.

  Consistent primary zone means that the location and priority of the leaders must be consistent.

• opt_tg_partition_option: the partitioning rule of the table group, which is consistent with that specified in the CREATE TABLE statement.

  A table group does not require specific column definitions. Therefore, you do not need to specify the column names for the HASH, RANGE, and LIST options. You only need to specify the number of columns (COLUMN_NUM).

  Consistent partitioning method:

  • Tables in the same table group must share the same partitioning method such as HASH-RANGE partitioning.

  • HASH-partitioned tables must have the same number of referenced columns and the same number of partitions.

  • RANGE-partitioned tables must have the same number of referenced columns, the same number of partitions, and the same range definitions.

• tg_subpartition_option: the subpartitioning rule. The partitioning requirements of this field are consistent with those of the opt_tg_partition_option field, depending on the partition type.