SQL routing

OceanBase Database Proxy (ODP) fully considers the location of replicas that are included in each user request, read/write split routing strategy configured by each user, optimal procedure for multi-region deployment of OceanBase Database, and the status and load of each OBServer node. ODP routes user requests to an OBServer node, and ensures the performance of OceanBase Database operations.

We recommend that you learn about the concepts related to routing in advance for a better understanding of this topic.

• Zone

• Region

• Server list

• RootService list

• Location cache

• Replica

• Major compaction

• Strong consistency read/Weak consistency read

• Read/Write zone and read-only zone

• Partitioned table

• Partitioning key

OceanBase Database execution plans

OceanBase Database supports three types of execution plans: local, remote, and distribute. ODP aims to avoid remote execution plans, which feature low efficiency and poor performance, and to adopt local execution plans.

Purpose of ODP routing

We recommend that you learn about the basic meaning and physical meaning of zone, region, partition, and replica before you read the following content of ODP routing. Based on the partition design and distribution, as well as the consideration for efficiency of local execution plans, ODP must accurately route SQL queries. The routing process covers the following phases: SQL parsing, partition calculation, partition information retrieval, and replica strategy selection.

Routing based on non-partitioned tables

Non-partitioned tables can directly use the replica information in the location cache. ODP saves the mapping between partitions and OBServer IP addresses. ODP parses the table name in an SQL statement and queries the IP address of the OBServer node corresponding to the partition in the ODP cache by using the table name. The following list describes the three possible situations in terms of cache effectiveness:

• If the OBServer IP address cannot be found in the cache, ODP needs to access the OBServer node to query and cache the latest mapping.

• If the OBServer IP address exists in the cache but is unavailable, ODP needs to access the OBServer node to query and update the OBServer IP address.

• If the OBServer IP address exists in the cache and is available, the IP address can be directly used.

Routing based on partitioned tables

This routing strategy involves partition IDs and relevant calculation and query procedures, which are not involved in routing based on non-partitioned tables. After ODP obtains the location cache, ODP needs to determine the partitions and subpartitions of a table, generate partition IDs based on different partitioning key types and calculation methods, and obtain the information about the leader and followers.

During partition calculation, ODP can obtain the partitioning key and its type based on the table schema. Then, ODP parses an SQL statement to obtain the value of the partitioning key, performs partition calculation based on the table schema and partitioning key type, and forwards the request to the OBServer node corresponding to the partition.

In most cases, ODP can route SQL statements to the OBServer node corresponding to the partition by performing partition calculation. In this way, remote execution is avoided, and the processing efficiency is improved. In ODP V3.2.0, the scenario is optimized where a routing table exists, but partition calculation cannot be performed, and SQL statements are randomly routed to an arbitrary OBServer node. After optimization, SQL statements are randomly routed to an OBServer node that is assigned with a partition. The hit ratio is increased, and remote execution can be avoided.

Replica route selection (normal deployment)

In scenarios that involve strong consistency read, if an SQL statement has a specified table name, ODP routes the SQL statement to the leader OBServer node for the corresponding partition of the table. In scenarios that involve weak consistency reads, logon authentication requests, or strong consistency reads without table names specified in SQL statements, three routing strategies are available: load-balanced routing (default strategy), follower-first routing, and non-compaction-follower-first routing.

Load-balanced routing (default strategy)

An SQL statement is routed to an OBServer node based on the following rules. A smaller rule number indicates a higher priority.

1. The OBServer node that is in the same region and IDC and is not undergoing a major compaction.

2. The OBServer node that is in the same region but a different IDC, and is not undergoing a major compaction.

3. The OBServer node that is in the same region and IDC but is undergoing a major compaction.

4. The OBServer node that is in the same region but a different IDC, and is undergoing a major compaction.

5. The OBServer node that is in a different region and is not undergoing a major compaction.

6. The OBServer node that is in a different region but is undergoing a major compaction.

Follower-first routing

In normal deployment scenarios, the follower-first read strategy is supported. This strategy is controlled by the user-level system variable proxy_route_policy, and takes effect only in the case of normal deployment with weak consistency reads. That is, the follower-first read strategy instead of the load-balanced routing strategy is used.

In the case of normal deployment with weak consistency reads, set proxy_route_policy to follower_first. Then, SQL statements are preferentially routed to a follower OBServer node, even if the OBServer node is undergoing a major compaction. An SQL statement is routed to an OBServer node based on the following rules. A smaller rule number indicates a higher priority.

1. The follower OBServer node that is in the same region and IDC and is not undergoing a major compaction.

2. The follower OBServer node that is in the same region but a different IDC, and is not undergoing a major compaction.

3. The follower OBServer node that is in the same region and IDC but is undergoing a major compaction.

4. The follower OBServer node that is in the same region but a different IDC, and is undergoing a major compaction.

5. The leader OBServer node that is in the same region and IDC and is not undergoing a major compaction.

6. The leader OBServer node that is in the same region but a different IDC, and is not undergoing a major compaction.

7. The follower OBServer node that is in a different region and is not undergoing a major compaction.

8. The follower OBServer node that is in a different region but is undergoing a major compaction.

9. The leader OBServer node that is in a different region and is not undergoing a major compaction.

10. The leader OBServer node that is in a different region but is undergoing a major compaction.

Non-compaction-follower-first routing

In the case of normal deployment with weak consistency reads, set proxy_route_policy to unmerge_follower_first. Then, SQL statements are preferentially routed to a follower OBServer node that is not undergoing a major compaction. An SQL statement is routed to an OBServer node based on the following rules. A smaller rule number indicates a higher priority.

1. The follower OBServer node that is in the same region and IDC and is not undergoing a major compaction.

2. The follower OBServer node that is in the same region but a different IDC, and is not undergoing a major compaction.

3. The leader OBServer node that is in the same region and IDC and is not undergoing a major compaction.

4. The leader OBServer node that is in the same region but a different IDC, and is not undergoing a major compaction.

5. The follower OBServer node that is in the same region and IDC but is undergoing a major compaction.

6. The follower OBServer node that is in the same region but a different IDC, and is undergoing a major compaction.

7. The follower OBServer node that is in a different region and is not undergoing a major compaction.

8. The leader OBServer node that is in a different region and is not undergoing a major compaction.

9. The follower OBServer node that is in a different region but is undergoing a major compaction.

10. The leader OBServer node that is in a different region but is undergoing a major compaction.

Others

In the case of normal deployment with weak consistency reads, if proxy_route_policy is set to another value, the load-balanced routing strategy prevails.

Replica route selection (read/write splitting)

In read/write splitting mode, read-only replicas and read-only zones are used. In this deployment mode, the follower-first read routing strategy does not apply. The routing strategy is subject to the system variable ob_route_policy. The deployment mode involves the following scenarios:

1. If an SQL statement is to implement a strong consistency read and has a specified table name, the statement is directly routed to the leader OBServer node for the corresponding table partition.

2. If an SQL statement is to implement a strong consistency read and is in the format of "SELECT with table name unspecified/USE DATABASE/SET SESSION system variables", the zone attribute is ignored. This scenario is similar to using load-balanced routing in normal deployment.

3. Strong consistency read statements, excluding logon authentication requests and the preceding two types of requests, are routed based on the following priorities:

   1. The OBServer node that is in the same region and IDC and is in a read/write zone not undergoing a major compaction.

   2. The OBServer node that is in the same region but a different IDC, and is in a read/write zone not undergoing a major compaction.

   3. The OBServer node that is in the same region and IDC and is in a read/write zone undergoing a major compaction.

   4. The OBServer node that is in the same region but a different IDC, and is in a read/write zone undergoing a major compaction.

   5. The OBServer node that is in a different region and is in a read/write zone not undergoing a major compaction.

   6. The OBServer node that is in a different region and is in a read/write zone undergoing a major compaction.

Read-only-zone-first routing (default)

Statements to implement weak consistency reads and logon authentication requests are routed based on the value of the system variable ob_route_policy. If ob_route_policy is set to readonly_zone_first, which is the default value, SQL statements are routed based on the following rules. A smaller rule number indicates a higher priority.

1. The OBServer node that is in the same region and IDC and is in the read-only zone not undergoing a major compaction.

2. The OBServer node that is in the same region but a different IDC, and is in the read-only zone not undergoing a major compaction.

3. The OBServer node that is in the same region and IDC and is in the read-only zone undergoing a major compaction.

4. The OBServer node that is in the same region but a different IDC, and is in the read-only zone undergoing a major compaction.

5. The OBServer node that is in the same region and IDC and is in the read/write zone not undergoing a major compaction.

6. The OBServer node that is in the same region but a different IDC, and is in the read/write zone not undergoing a major compaction.

7. The OBServer node that is in the same region and IDC and is in the read/write zone undergoing a major compaction.

8. The OBServer node that is in the same region but a different IDC, and is in the read/write zone undergoing a major compaction.

9. The OBServer node that is in a different region and is in the read-only zone not undergoing a major compaction.

10. The OBServer node that is in a different region and is in the read-only zone undergoing a major compaction.

11. The OBServer node that is in a different region and is in the read/write zone not undergoing a major compaction.

12. The OBServer node that is in a different region and is in the read/write zone undergoing a major compaction.

Routing only to read-only zones

If ob_route_policy is set to only_readonly_zone, SQL statements are routed based on the following rules. A smaller rule number indicates a higher priority.

1. The OBServer node that is in the same region and IDC and is in the read-only zone not undergoing a major compaction.

2. The OBServer node that is in the same region but a different IDC, and is in the read-only zone not undergoing a major compaction.

3. The OBServer node that is in the same region and IDC and is in the read-only zone undergoing a major compaction.

4. The OBServer node that is in the same region but a different IDC, and is in the read-only zone undergoing a major compaction.

5. The OBServer node that is in a different region and is in the read-only zone not undergoing a major compaction.

6. The OBServer node that is in a different region and is in the read-only zone undergoing a major compaction.

Non-compaction-zone-first routing

If ob_route_policy is set to unmerge_zone_first, SQL statements are routed based on the following rules. A smaller rule number indicates a higher priority.

1. The OBServer node that is in the same region and IDC and is in the read-only zone not undergoing a major compaction.

2. The OBServer node that is in the same region but a different IDC, and is in the read-only zone not undergoing a major compaction.

3. The OBServer node that is in the same region and IDC and is in the read/write zone not undergoing a major compaction.

4. The OBServer node that is in the same region but a different IDC, and is in the read/write zone not undergoing a major compaction.

5. The OBServer node that is in the same region and IDC and is in the read-only zone undergoing a major compaction.

6. The OBServer node that is in the same region but a different IDC, and is in the read-only zone undergoing a major compaction.

7. The OBServer node that is in the same region and IDC and is in the read/write zone undergoing a major compaction.

8. The OBServer node that is in the same region but a different IDC, and is in the read/write zone undergoing a major compaction.

9. The OBServer node that is in a different region and is in the read-only zone not undergoing a major compaction.

10. The OBServer node that is in a different region and is in the read/write zone not undergoing a major compaction.

11. The OBServer node that is in a different region and is in the read-only zone undergoing a major compaction.

12. The OBServer node that is in a different region and is in the read/write zone undergoing a major compaction.

Considerations on read/write splitting

A read-only zone must not be set only in a remote region. Otherwise, a session to an OBServer node in the zone may need to be established for each request, which is time-consuming.

Based on an internal ODP strategy, if weak consistency reads are enabled for a session, and the tenant has a read-only zone, ODP checks whether the previously accessed OBServer node is in the read-only zone. If not, ODP closes this server session. In addition, for an SQL statement, if a read/write zone is in the same region and a read-only zone is in a remote region, ODP always routes the SQL statement to the read/write zone in the same region. Therefore, ODP will constantly establish sessions to the OBServer node in the read/write zone of the same region. After the processing of a request is complete, ODP closes the current session and establishes another session for the next request.