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 a user request to the optimal 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

• Partitioned table

• Partitioning key

OceanBase Database execution plans

OceanBase Database supports three types of execution plans: local, remote, and distributed. 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 IP addresses of OBServer nodes. 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 IP address of the OBServer node cannot be found in the cache, ODP needs to access the OBServer node to query and cache the latest mapping.

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

• If the IP address of the OBServer node 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, optimization is implemented for the scenario where partition calculation cannot be performed for a partitioned table 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 reads, 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, login 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 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 login 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.

Columnstore replica-only routing

If ob_route_policy is set to column_store_only, SQL statements are routed based on the following rules.

• If weak-consistency read is not configured for the session, error 4007 is returned, indicating that columnstore replicas do not support strong-consistency read.

• If the SQL statement is a DML operation, error 4007 is returned, indicating that columnstore replicas do not support write operations.

• If the columnstore replica is not found, error NO_REPLICA_VALID is returned without any retry.