Intra-tenant balancing

In OceanBase Database V3.x, replicas are distributed on multiple nodes at the partition granularity. The load balancing module can implement partition balancing within a tenant by performing a partition leader/follower switchover or migrating partition replicas. OceanBase Database V4.x adopts the standalone log stream (LS) architecture, where replicas are distributed at the LS granularity and each LS serves a large number of partitions. Therefore, the load balancing module in OceanBase Database V4.2.x cannot directly control the partition distribution. Instead, the module needs to balance partitions based on LS distribution after achieving LS quantity balancing and LS leader balancing.

LS balancing and partition balancing are in the following priority order:

LS balancing > Partition balancing

LS balancing (homogeneous zone mode)

LS quantity balancing

When you modify the UNIT_NUM parameter, change the priority of zones in PRIMARY_ZONE, or modify the locality (which affects PRIMARY_ZONE) for a tenant, the background thread of the load balancing module will immediately change the LS quantity and location through LS splitting, merging, regrouping, or other actions to achieve the desired tenant status. That is, after the balancing is complete, each tenant unit has a LS group, the number of LSs in a LS group is equal to the number of top priority zones in PRIMARY_ZONE, and leaders in a LS group are evenly distributed to primary zones of the top priority.

In the homogeneous zone mode, the LS quantity is calculated by using the following formula:

LS quantity = UNIT_NUM * first_level_primary_zone_num

The following table describes sample scenarios where LS quantity balancing is triggered.

Example 1: As shown in the figure below, top priority zones in PRIMARY_ZONE are changed from Z1,Z2 to Z1 and UNIT_NUM is changed from 1 to 2. The total LS quantity remains unchanged. Each LS2 is migrated to a new unit, and the leader role is switched to the LS2 in Z1 based on the change of PRIMARY_ZONE, thus achieving LS balancing.

Example 2: As shown in the figure below, PRIMARY_ZONE = Z1 is configured, and UNIT_NUM is changed from 1 to 2. To ensure that every unit has a LS, LS2 is created through LS splitting and 1/2 of the tablets originally served by LS1 are transferred to LS2. Each LS2 is migrated to a new unit.

Example 3: As shown in the figure below, UNIT_NUM = 1 is configured, and top priority zones in PRIMARY_ZONE are changed from RANDOM to Z1,Z2. After the balancing is complete, leaders must be only in top priority zones in PRIMARY_ZONE and the tablets must be evenly distributed to LSs. Because LS3 is in the same LS group as LS1 and LS2, you can directly transfer 1/2 of the tablets from LS3 to LS1, and then merge LS3 into LS2. Then, the remaining 1/2 of the tablets will be served by LS2. In this case, tablet distribution remains balanced even though the LS quantity is reduced.

LS leader balancing

LS leader balancing ensures that LS leaders are evenly distributed to primary zones of the top priority on the premise of LS quantity balancing. For example, if you modify PRIMARY_ZONE for a tenant, the load balancing background thread dynamically adjusts the LS quantity based on the priority settings in PRIMARY_ZONE and switches the leader to ensure that each primary zone of the top priority has only one LS leader.

LS leader balancing is controlled by the tenant-level parameter enable_ls_leader_balance. It is enabled by default.

Example 1: As shown in the figure below, UNIT_NUM = 1 is configured, and top priority zones in PRIMARY_ZONE are changed from Z1 to Z1,Z2. To ensure that each top priority zone in PRIMARY_ZONE has a leader, a new LS is created in each unit by splitting the original LS in the same unit, and the new LS leader role is switched to the new LS in Z2.

Example 2: As shown in the figure below, UNIT_NUM = 1 is configured and top priority zones in PRIMARY_ZONE are changed from RANDOM to Z1,Z2. After LS3 is merged into another LS to realize LS quantity balancing, LS leaders are naturally evenly distributed in Z1 and Z2 without any additional LS leader balancing actions.

LS balancing (heterogeneous zone mode)

In the current version, OceanBase Database supports the heterogeneous zone mode for tenants. In the heterogeneous zone mode, the number of units in each zone of a tenant can differ, but a tenant can have at most two different UNIT_NUM values.

In the heterogeneous zone mode, the load balancing algorithm controls the distribution of log streams at the unit granularity:

• Within each zone of a tenant, each unit has the same number of log streams.

• Within zones that have the same UNIT_NUM, log streams are aggregated in the same way as in the homogeneous zone mode.

In the heterogeneous zone mode, the LS quantity is calculated by using the following formula:

LS quantity = (LCM of all UNIT_NUM values of the tenant) * first_level_primary_zone_num

Partition distribution during table creation

When you create a user table, OceanBase Database scatters and aggregates partitions to LSs based on a set of balancing distribution strategies to ensure relative balance of partitions among LSs.

User tables of a specified table group

You can specify a table group to flexibly configure partition aggregation and scattering rules for different tables. If you specify a table group with the corresponding sharding attribute when you create a user table, the partitions of the new table are distributed to LSs of the corresponding user based on the alignment rules of the table group. The following table describes the rules.

For more information about table groups, see Overview of table groups.

Normal user tables

If you create a normal user table without specifying the table group, OceanBase Database scatters the partitions of the new table to all LSs of the user by default. The specific rules are as follows:

• For non-partitioned tables, the new partitions are distributed to the LS with the least partitions.

• For partitioned tables, primary partitions are evenly distributed to all user log streams based on the round-robin algorithm.

• For subpartitioned tables, all subpartitions under each partition are evenly distributed to all user log streams based on the round-robin algorithm.

Here are some examples:

• Example 1: In a MySQL-compatible tenant, create non-partitioned tables named tt1, tt2, tt3, and tt4.

  obclient [test]> CREATE TABLE tt1(c1 int);
  

  obclient [test]> CREATE TABLE tt2(c1 int);
  

  obclient [test]> CREATE TABLE tt3(c1 int);
  

  obclient [test]> CREATE TABLE tt4(c1 int);
  

  Query the partition distribution of the four tables.

  obclient [test]> SELECT table_name,partition_name,subpartition_name,ls_id,zone FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name in('tt1','tt2','tt3','tt4') AND role='LEADER';
  

  The query result is as follows:

  +------------+----------------+-------------------+-------+------+
  | table_name | partition_name | subpartition_name | ls_id | zone |
  +------------+----------------+-------------------+-------+------+
  | tt1        | NULL           | NULL              |  1001 | z1   |
  | tt2        | NULL           | NULL              |  1002 | z2   |
  | tt3        | NULL           | NULL              |  1003 | z3   |
  | tt4        | NULL           | NULL              |  1001 | z1   |
  +------------+----------------+-------------------+-------+------+
  4 rows in set
  

• Example 2: Create a partitioned table named tt5.

  obclient [test]> CREATE TABLE tt5(c1 int) PARTITION BY HASH(c1) PARTITIONS 6;
  

  Query the distribution of the partitioned table.

  obclient [test]> SELECT table_name,partition_name,subpartition_name,ls_id,zone FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name ='tt5' AND role='LEADER';
  

  The query result is as follows. The primary partitions of the table are evenly distributed to 1001, 1002, and 1003 based on the round-robin algorithm.

  +------------+----------------+-------------------+-------+------+
  | table_name | partition_name | subpartition_name | ls_id | zone |
  +------------+----------------+-------------------+-------+------+
  | tt5        | p0             | NULL              |  1003 | z3   |
  | tt5        | p1             | NULL              |  1001 | z1   |
  | tt5        | p2             | NULL              |  1002 | z2   |
  | tt5        | p3             | NULL              |  1003 | z3   |
  | tt5        | p4             | NULL              |  1001 | z1   |
  | tt5        | p5             | NULL              |  1002 | z2   |
  +------------+----------------+-------------------+-------+------+
  6 rows in set
  

• Example 3: Create a subpartitioned table named tt8.

  obclient [test]> CREATE TABLE tt8 (c1 int, c2 int, PRIMARY KEY(c1, c2)) 
      PARTITION BY HASH(c1) SUBPARTITION BY RANGE(c2) SUBPARTITION TEMPLATE 
      (SUBPARTITION p0 VALUES LESS THAN (1990), 
       SUBPARTITION p1 VALUES LESS THAN (2000),
       SUBPARTITION p2 VALUES LESS THAN (3000),
       SUBPARTITION p3 VALUES LESS THAN (4000),
       SUBPARTITION p4 VALUES LESS THAN (5000),
       SUBPARTITION p5 VALUES LESS THAN (MAXVALUE)) 
       PARTITIONS 2;
  

  Query the partition distribution of the tt8 table.

  obclient [test]> SELECT table_name,partition_name,subpartition_name,ls_id,zone FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name ='tt8' AND role='LEADER';
  

  The query result is as follows. The subpartitions under each primary partition of the table are evenly distributed to all user log streams based on the round-robin algorithm.

  +------------+----------------+-------------------+-------+------+
  | table_name | partition_name | subpartition_name | ls_id | zone |
  +------------+----------------+-------------------+-------+------+
  | tt8        | p0             | p0sp0             |  1001 | z1   |
  | tt8        | p0             | p0sp1             |  1002 | z2   |
  | tt8        | p0             | p0sp2             |  1003 | z3   |
  | tt8        | p0             | p0sp3             |  1001 | z1   |
  | tt8        | p0             | p0sp4             |  1002 | z2   |
  | tt8        | p0             | p0sp5             |  1003 | z3   |
  | tt8        | p1             | p1sp0             |  1002 | z2   |
  | tt8        | p1             | p1sp1             |  1003 | z3   |
  | tt8        | p1             | p1sp2             |  1001 | z1   |
  | tt8        | p1             | p1sp3             |  1002 | z2   |
  | tt8        | p1             | p1sp4             |  1003 | z3   |
  | tt8        | p1             | p1sp5             |  1001 | z1   |
  +------------+----------------+-------------------+-------+------+
  12 rows in set
  

Special user tables

For special user tables, such as local index tables, global index tables, and replicated tables, OceanBase Database distributes their partitions to LSs based on the following special rules:

• Local index tables have the same partitioning rules as primary user tables. Each partition is bound to the partition distribution of the primary user table.

• Global index tables are non-partitioned tables by default. Partitions in such tables are distributed to the user LS with the least partitions during table creation.

• Replicated tables exist only on broadcast LSs.

Partition balancing

On the basis of LS balancing, the load balancing module uses the transfer feature to scatter and aggregate partition tablets to LSs, thus achieving partition balancing within a tenant. The partition balancing task SCHEDULED_TRIGGER_PARTITION_BALANCE is controlled by the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE procedure. By default, partition balancing is triggered once daily at 00:00. For more information about partition balancing operations, see Configure a scheduled partition balancing task.

The partition balancing strategies are in the following priority order:

Partition attribute alignment > Table group and partition weight balancing > Partition quantity balancing > Partition disk balancing

Partition attribute alignment

Table group alignment

When you use an SQL statement to modify the table group attribute of a table or the sharding attribute of a table group, you must wait for the background partition balancing task to complete before the partition distribution meets your expectations. You can manually call the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE procedure to trigger partition balancing for quick alignment.

For more information about the partition distribution rules of tables in a table group, see User tables of a specified table group in this topic.

For more information about table groups, see Overview of table groups.

duplicate_scope alignment

In the current version of OceanBase Database, changes to the attributes of replicated tables are supported. Replicated tables can only exist on broadcast log streams. After modifying the duplicate_scope attribute, you must wait for partition balancing to complete before using the attribute as expected. You can manually call the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE procedure to trigger partition balancing for quick alignment.

For more information about how to change replicated table attributes, see Change the attributes of a replicated table (MySQL-compatible mode) and Change the attributes of a replicated table (Oracle-compatible mode).

Table group and partition weight balancing

Table group weight balancing

In the MySQL-compatible mode of OceanBase Database, user tables are aggregated by binding a database to a table group with Sharding = 'NONE'. The current version supports automatic aggregation of user tables within the same database and aggregation of user tables across multiple databases.

By setting weights for table groups with Sharding = 'NONE', table group weight balancing helps distribute user tables in automatically aggregated databases according to their weights. Table group weight balancing is a process in the partition balancing (PARTITION_BALANCE) task. You can trigger it manually or wait for the scheduled partition balancing task to run.

Partition weight balancing (non-table group)

Partition weight is the relative proportion of CPU, memory, disk, and other resources that users set for partitions. Only integer values are supported, in the range [1, +∞). By default, partitions have no weight.

Non-table group partition weight balancing is a process in the partition balancing task. After you set partition weights for a table, you can trigger the partition balancing task manually or wait for the scheduled partition balancing task to run. The partition weight balancing algorithm uses partition movement and partition exchange to minimize the variance of the sum of partition weights on each user log stream.

Only tables with partition weights set participate in partition weight balancing. Partitions without weights in a table are still balanced by partition count.

Application of partition weight balancing

Partition weights for non-table groups are mainly used for partition hotspot scattering. The following two scenarios are supported:

• Hotspot partition scattering within a primary partitioned table

• Hotspot partition scattering across non-partitioned tables

When setting partition weights, we recommend using at most three tiers:

• Large weight = 100% × partition count

• Medium weight = 50% × partition count

• Small weight = 1

The following examples illustrate the application of partition weight balancing.

Hotspot partition scattering within a primary partitioned table

For example, assume a primary partitioned table t1 with 6 partitions. After the table is created, the partition distribution is as follows.

Application scenarios:

• Scenario 1: Scattering a few hotspots

  Assume that only p0 and p3 in table t1 are hotspot partitions that need to be scattered. To minimize the number of weight tiers, you can use only one tier: "small weight = 1".

  Set partition weights p0 = 1 and p3 = 1. During partition balancing, only these two partitions participate in weight balancing. The remaining partitions do not participate and are balanced by partition count with minimal movement. After partition balancing (manual trigger or scheduled), the overall distribution is as follows.

  

• Scenario 2: Distributing all partitions by weight

  Assume that p0 in table t1 has the highest traffic, p1, p2, and p3 have medium traffic, and the rest have low traffic. To scatter traffic, use the following three weight tiers:

  • Large weight = 100% × partition count of the primary partitioned table = 6

  • Medium weight = 50% × partition count of the primary partitioned table = 3

  • Small weight = 1

  First set the table-level partition weight to 1 to define the scope of weight balancing for the entire table. Then set partition weights p0 = 6, p1 = 3, p2 = 3, and p3 = 3. After partition balancing, the overall distribution is as follows.

  

• Scenario 3: Hotspot partitions exclusive within a balancing group

  Assume that the 6 partitions in table t1 have highly uneven traffic, with two oversized partitions p0 and p3. You want other partitions in the table to not affect the read and write performance of these two. Use the following two weight tiers:

  • Large weight = 100% × partition count of the primary partitioned table = 6

  • Small weight = 1

  First set the table-level partition weight to 1, then set partition weights p0 = 6 and p3 = 6. After partition balancing, the overall distribution is as follows.

  

Hotspot scattering for non-partitioned tables

For example, assume 6 non-partitioned tables. After they are created, the distribution is as follows.

Application scenarios:

• Scenario 1: Scattering a few hotspots

  Assume that non_part_t1 and non_part_t4 are two hotspots on the same log stream that need to be scattered. To minimize the number of weight tiers, use only one tier: "small weight = 1".

  Set table-level weights non_part_t1 = 1 and non_part_t4 = 1. After partition balancing, the distribution is as follows.

  

• Scenario 2: Distributing by traffic

  Assume that the traffic of each non-partitioned table is known: non_part_t1 is highest, non_part_t2 and non_part_t3 are medium, and non_part_t4, non_part_t5, and non_part_t6 are lowest. To scatter by traffic, use the following three tiers:

  • Large weight = 100% × partition count = 6

  • Medium weight = 50% × partition count = 3

  • Small weight = 1

  Set table-level partition weights non_part_t1 = 6, non_part_t2 = 3, non_part_t3 = 3, non_part_t4 = 1, non_part_t5 = 1, and non_part_t6 = 1. After partition balancing, the distribution is as follows.

  

Impact of partition weight on table groups

Tables with partition weights can be added to table groups. For table groups, if a table group contains tables with partition weights, partition balancing calculates distribution based on the weighted sum within the partition group. The following table describes the impact of adding tables with partition weights to table groups.

The following example illustrates the impact of setting partition weights for a table in a table group on the overall distribution.

Assume a table group with SHARDING = PARTITION that contains two primary partitioned tables t1 and t2, each with 6 partitions. The primary partitions of each table form a partition group. The initial distribution is as follows.

Set the weight of all partitions in table t1 to 1, then set t1_p0 = 6 and t1_p1 = 6. After partition balancing, the partition distribution in the table group is as follows.

Partition quantity balancing

Partition quantity balancing aims to evenly distribute the partitions of the primary user tables to all LSs of the user, with a partition quantity deviation no greater than 1 between each two LSs.

OceanBase Database uses the concept of balancing group to describe the relationship between partitions to be scattered. Such partitions are divided into balancing groups to achieve partition quantity balancing based on intra-group balancing and inter-group balancing.

The following table describes the balancing group division rules in scenarios where no table group exists.

In the balancing stage, the system first evenly distributes partitions for each balancing group to achieve intra-group balancing, and then transfers partitions from the LS with the most partitions to achieve inter-group balancing, thus achieving partition quantity balancing.

Assume that all partitions of four tables are in LS1. The partitions are divided into three balancing groups:

• Balancing group 1: non-partitioned tables non_part_t1 and non_part_t2

• Balancing group 2: partitions part_one_t3_p0 and part_one_t3_p1 of a partitioned table

• Balancing group 3: subpartitions part_two_t4_p0s0, part_two_t4_p0s1, part_two_t4_p1s0, and part_two_t4_p1s1 of a subpartitioned table

The initial distribution of partitions is 8-0-0.

Upon intra-group balancing, the distribution of partitions is 4-4-0. Overall balancing is not achieved.

One partition is transferred each from LS1 and LS2, which have the most partitions, to LS3, which has the least partitions, to achieve inter-group balancing. The distribution of partitions is now 3-3-2, which means that overall balancing is achieved.

When table groups exist, each table group is an independent balancing group. The system binds the partitions to be aggregated in a table group and takes them as one partition. Then the system adopts the same intra-group balancing and inter-group balancing methods to achieve partition quantity balancing. When table groups exist, partition quantity balancing may not be fully achieved in a tenant.

The following table describes the balancing group division rules in scenarios where table groups exist.

Assume that you add a table group named tg1 with the sharding = 'NONE' configuration after the preceding balancing is achieved. The table group contains non-partitioned tables non_part_t5_in_tg1, non_part_t6_in_tg1, non_part_t7_in_tg1, non_part_t8_in_tg1, and non_part_t9_in_tg1. Because all tables in the tg1 table group must be bound together, the partition distribution is 4-4-5 even after balancing is completed.

Partition disk balancing

On the basis of partition quantity balancing or partition weight balancing, the load balancing module exchanges partitions between LSs as much as possible to ensure that the disk usage difference between each two LSs does not exceed the percentage specified by the cluster-level parameter balancer_tolerance_percentage. If a single partition occupies too much disk space, this balancing effect may not be achieved.

To avoid frequent balancing in scenarios with a small data volume, the threshold for triggering partition disk balancing is set to 50 GB in the current version. If the disk usage of a single LS is less than this threshold, disk balancing will not be triggered.

Partition balancing judgement

Partition balancing refers to the balancing for primary user tables. You need to specify table_type = 'USER TABLE' in the query. You can query the data_size column in the CDB_OB_TABLET_REPLICAS view to judge whether partition disk balancing is achieved.

• Query statement for judging partition quantity balancing

  obclient [test]> SELECT svr_ip,svr_port,ls_id,count(*) FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE tenant_id=xxx AND role='leader' AND table_type='USER TABLE' GROUP BY svr_ip,svr_port,ls_id;
  

• Query statement for judging partition disk balancing

  obclient [test]> SELECT a.svr_ip,a.svr_port,b.ls_id,sum(data_size)/1024/1024/1024 as total_data_size FROM oceanbase.CDB_OB_TABLET_REPLICAS a, oceanbase.CDB_OB_TABLE_LOCATIONS b WHERE a.tenant_id=b.tenant_id AND a.svr_ip=b.svr_ip AND a.svr_port=b.svr_port AND a.tablet_id=b.tablet_id AND b.role='leader' AND b.table_type='USER TABLE' AND a.tenant_id=xxxx GROUP BY svr_ip,svr_port,ls_id;
  

Scenarios of partition aggregation followed by redistribution

Partition balancing is a continuous process for maintaining system load balance. In actual business operations, the system aggregates and redistributes partitions based on real-time load conditions to achieve optimal resource utilization and performance. Common scenarios involving partition aggregation followed by redistribution include:

• A tenant undergoes horizontal scaling down and then scaling up. For example, the number of units (UNIT_NUM) for a tenant changes from N to 1, and then from 1 back to N.

• The number of primary zones (PRIMARY_ZONE) for a tenant first decreases and then increases. For example, the value of PRIMARY_ZONE changes from RANDOM to zone1, and then from zone1 back to RANDOM.

• A large number of tables are added to a table group with the sharding attribute set to NONE, and then the tables are removed from the table group.

Based on the existing log stream balancing and partition balancing algorithms, after partition aggregation and redistribution, consecutive partitions within a partitioned table may be grouped on the same log stream, or non-partitioned tables within the same database may be concentrated on the same log stream. To address this issue, OceanBase Database has optimized the partition balancing algorithm:

• For non-partitioned tables, after partition aggregation and redistribution, multiple non-partitioned tables within the same database are distributed across user log streams according to the number of partitions, preventing consecutive non-partitioned tables in the same database from being grouped on the same log stream.

• For partitioned tables, after partition aggregation and redistribution, consecutive partitions within a partitioned table are distributed across user log streams in a round-robin manner according to the number of partitions, preventing consecutive partitions of a partitioned table from being grouped on the same log stream.