Set load balancing by primary zone

The primary_zone parameter specifies the preferred locations of the leaders. When you specify the primary_zone parameter, you specify the zones to which the leaders tend to be scheduled. Assume that the value of primary_zone is set to "zone1" for the t1 table, RootService schedules the leaders in t1 to zone1 where possible.

The value of the primary_zone parameter is a list of multiple zones. In this list, zones with different priorities are arranged in descending order and separated with a semicolon (;). Zones with the same priority are separated with a comma (,).

For example, hz1,hz2;sh1,sh2;sz1 indicates that hz1 and hz2 share the same priority and they have higher priority than sh1, sh2, and sz1. sh1 and sh2 share the same priority and they have higher priority than sz1.

Region

In OceanBase Database, each zone has a region attribute, which indicates the region to which the zone belongs. You can configure only one region for each zone. Multiple zones can belong to the same region. The primary_zone setting also specifies the preferred regions of the leaders. Therefore, setting the primary_zone parameter has the following meanings:

• The value of primary_zone indicates the preferred zones of leaders.

• The regions of the zones that you specified for the primary_zone parameter are the preferred regions of the leaders.

Leaders will be first scheduled to the highest-priority zone. If the replicas in this zone cannot be switched to the leader role, another zone in the same region is selected as the location of the leaders.

Rewrite the value of the primary_zone parameter

OceanBase Database rewrites the value that you specified for the primary_zone parameter based on the regions of the zones. Rewriting rules:

• List the regions corresponding to all zones in the primary zone list.

  For example, assume that primary_zone is set to hz1,hz2;sh1,sh2;sz1 and the corresponding primary_region is hz,hz;sh,sh;sz.

• Remove repeated regions in the primary region list and reserve the regions that appear for the first time. Then, the value of primary_region is changed to hz;sh;sz.

• Rearrange the zones in the primary zone list based on the priorities of the regions in the primary region list. For zones of different regions, zones in a high-priority region have higher priority than those in a low-priority region. Zones of the same region are arranged based on the priorities of the original primary zone.

Inheritance

The primary_zone parameter can be set at the table, table group, database, and tenant levels.

The primary_zone parameter must be specified at the tenant level and that at other levels can be specified as needed. If primary_zone at a level other than the tenant is unspecified, the primary_zone setting at the higher level is inherited. If you do not specify primary_zone when you create a tenant, the system sets it to RANDOM, indicating that all zones share the same priority.

• Table-level primary_zone setting

  • Check whether primary_zone is specified for the target table. If yes, the primary_zone setting of the table is used.

  • Otherwise, check whether the table belongs to a table group.

• Table group-level primary_zone setting

  • If the table belongs to a table group, check whether primary_zone is specified for the table group. If yes, the primary_zone setting of the table group also applies to the table.

  • If the table does not belong to a table group or primary_zone of its table group is unspecified, check whether primary_zone is specified for the database to which the table belongs.

• Database-level primary_zone setting

  • Check whether primary_zone is specified for the database. If yes, the primary_zone setting of the database also applies to the table.

  • Otherwise, the primary_zone setting of the tenant is used.

Examples

Assume that you have nine zones. sh1, sh2, and sh3 belong to Region SH. hz1, hz2, and hz3 belong to Region HZ. sz1, sz2, and sz3 belong to Region SZ.

Example 1

If the primary_zone parameter is set to sh1;hz1;hz2;sz1, the value of primary_region is SH;HZ;HZ;SZ based on rewriting rule 1, the value of primary_region is SH;HZ;SZ based on rewriting rule 2, and the value of primary_zone is sh1;sh2,sh3;hz1;hz2;hz3;sz1;sz2,sz3 based on rewriting rule 3.

The priority order of the three regions is SH > HZ > SZ. Zones in Region SH have higher priority than those in Region HZ and those in Region SZ. Zones in Region HZ have higher priority than those in Region SZ. The priority order of zones in each region is sh1 > sh2 = sh3 in Region SH; hz1 > hz2 > hz3 in Region HZ; sz1 > sz2 = sz3 in Region SZ. Therefore, the new value of primary_zone is sh1;sh2,sh3;hz1;hz2;hz3;sz1;sz2,sz3. Leaders are preferred to be distributed on sh1. If sh1 fails, the leaders are distributed on sh2 and sh3 based on the preceding priorities of the primary zones.

Example 2

If the primary_zone parameter is set to sh1,sh2;hz1;hz2;sz1, the value of primary_region is SH,SH;HZ;HZ;SZ based on rewriting rule 1, the value of primary_region is SH;HZ;SZ based on rewriting rule 2, and the value of primary_zone is sh1,sh2;sh3;hz1;hz2;hz3;sz1;sz2,sz3 based on rewriting rule 3.

The priority order of the three regions is SH > HZ > SZ. The zones in Region SH have higher priority than those in Region HZ and those in Region SZ. The zones in Region HZ have higher priority than those in Region SZ. The priority order of zones in each region is sh1 = sh2 > sh3 in Region SH; hz1 > hz2 > hz3 in Region HZ; sz1 > sz2 = sz3 in Region SZ. Therefore, the new value of primary_zone is sh1,sh2;sh3;hz1;hz2;hz3;sz1;sz2,sz3. Leaders are preferred to be evenly distributed to sh1 and sh2. If sh1 and sh2 fail, the leaders are distributed to sh3 based on the preceding priorities of the primary zones.

Example 3

If the primary_zone parameter is set to sh1,hz1;hz2;sz1, the value of primary_region is SH,HZ;HZ;SZ based on rewriting rule 1, the value of primary_region is SH,HZ;SZ based on rewriting rule 2, and the value of primary_zone is sh1,hz1;hz2;sh2,sh3,hz3;sz1;sz2,sz3 based on rewriting rule 3.

The priority order of the three regions is SH = HZ > SZ. The zones in Region SH and Region HZ have higher priority than those in Region SZ. The priority order of the zones is sh1 = hz1 > hz2 > sh2 = sh3 = hz3 > sz1 > sz2 = sz3. Therefore, the new value of primary_zone is sh1,hz1;hz2;sh2,sh3,hz3;sz1;sz2,sz3. Leaders are preferred to be evenly distributed to sh1 and hz1. If sh1 and hz1 fail, the leaders are distributed to hz2 based on the preceding priorities of the primary zones.

Set and modify the primary_zone parameter

You can set and modify the primary_zone parameter as the database administrator by using the following SQL syntax:

CREATE TENANT [IF NOT EXISTS] tenant_name PRIMARY_ZONE [=] zone;
CREATE TABLE [IF NOT EXISTS] table_name PRIMARY_ZONE [=] zone;
CREATE TABLEGROUP [IF NOT EXISTS] tablegroupname PRIMARY_ZONE [=] zone;
CREATE DATABASE [IF NOT EXISTS] database_name PRIMARY_ZONE [=] zone;
CREATE USER username IDENTIFIED BY ****** PRIMARY_ZONE 'zone_name'; -- This syntax is supported only for OceanBase Database in Oracle mode.
ALTER TENANT tenant_name [SET] PRIMARY_ZONE [=] zone;
ALTER TABLE [SET] PRIMARY_ZONE [=] zone;
ALTER TABLEGROUP tablegroup_namee SET PRIMARY_ZONE [=] zone;
ALTER DATABASE [database_name] [SET] PRIMARY_ZONE [=] zone;
ALTER USER username PRIMARY_ZONE 'zone_name'; -- This syntax is supported only for OceanBase Database in Oracle mode.

Field description:

• tenant_name: the tenant name. The name cannot exceed 128 characters in length. It must contain 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.

• table_name: the name of the specified table in the database.

• tablegroupname: the name of the table group to which the specified table belongs. The name cannot exceed 64 characters in length. It must contain 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.

  If the name of the created table group already exists and IF NOT EXISTS is not specified, an error is returned.

• database_name: the name of the database to be modified.

• zone: the primary zone of the specified database.

Examples

Example for creating a tenant and setting PRIMARY_ZONE to zone2:

obclient> CREATE TENANT IF NOT EXISTS t1 charset='utf8mb4', replica_num=1, zone_list=('zone1'), primary_zone='zone2', resource_pool_list=('pool1');

Example for creating a database table test and setting PRIMARY_ZONE to zone2:

obclient> CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(50)) REPLICA_NUM = 3,
       PRIMARY_ZONE = 'zone2';
Query OK, 0 rows affected

Example for creating a table group and setting PRIMARY_ZONE to zone2:

obclient> CREATE TABLEGROUP myTableGroup1 PRIMARY_ZONE = 'zone2';
Query OK, 0 rows affected

obclient> CREATE TABLE myt1 (c1 int, c2 int ) TABLEGROUP = myTableGroup1 PRIMARY_ZONE = 'zone2';
Query OK, 0 rows affected

obclient> CREATE TABLE myt2 (c1 int, c2 int ) TABLEGROUP = myTableGroup1 PRIMARY_ZONE = 'zone2';
Query OK, 0 rows affected

Example for creating a database and setting PRIMARY_ZONE to zone2:

obclient> CREATE DATABASE test2 DEFAULT CHARACTER SET UTF8 PRIMARY_ZONE = 'zone2';
Query OK, 1 row affected (0.00 sec)

Example for changing the value of PRIMARY_ZONE of tenant1 to zone2:

obclient> ALTER TENANT tenant1 PRIMARY_ZONE ='zone2';

Example for changing the value of PRIMARY_ZONE of Table t1 to zone2:

obclient> ALTER TABLE t1 PRIMARY_ZONE ='zone2';

Example for changing the value of PRIMARY_ZONE of Table group tg1 to zone2:

obclient> CREATE TABLEGROUP tg1 PRIMARY_ZONE ='zone2';

Example for changing the value of PRIMARY_ZONE of the test2 database to zone2:

obclient> ALTER DATABASE test2 PRIMARY_ZONE ='zone2';