SQL routing

ODP considers the replica locations involved in user requests, the user-configured read/write separation routing strategy, the optimal network path for the multi-region deployment of OceanBase Database, and the status and load of each server in OceanBase Database. ODP then routes the requests to the optimal OBServer node, ensuring high-performance operation of OceanBase Database.

Before you read this section, it is recommended that you first understand some routing concepts to better understand the following content.

• Zone

• Region

• Server list

• RS list

• Location cache

• Replica

• Major compaction

• Strong-consistency read/weak-consistency read

• Read/write zone/readonly zone

• Partitioned table

• Partitioning key

Execution plans of OceanBase Database Proxy

OceanBase Database Proxy supports three types of execution plans: local, remote, and distributed. ODP tries its best to avoid using remote plans and changes remote plans into local plans.

Routing of ODP

After you understand the basic concepts and physical meanings of the above regions, zones, partitions, and replicas, you can understand the routing logic of ODP. ODP needs to accurately route SQL statements based on the efficient local execution plans, so ODP goes through the process of SQL parsing, partition calculation, partition information acquisition, and replica strategy selection.

Routing of non-partitioned tables

For non-partitioned tables, ODP can directly use the replica information in the Location Cache. ODP maintains the mapping between partitions and OBServer nodes. ODP can obtain the IP address of the server where a partition is located based on the table name in an SQL statement and the cache. The following situations can occur based on the validity of the cache:

• The cache does not contain the required information. In this case, ODP needs to query the latest mapping information from the OBServer node and cache the information.

• The cache contains the required information, but the information is invalid. In this case, ODP needs to query the OBServer node again and update the information.

• The cache contains the required valid information. In this case, ODP can directly use the information.

Routing of partitioned tables

Compared with non-partitioned tables, partitioned tables require additional processes for partition ID calculation and query. After partitioned tables obtain the replica information in the Location Cache, the tables need to determine whether they have primary and secondary partitions. Then, the tables calculate the partition ID based on the partition key type and calculation method, and obtain the information about the primary and secondary replicas.

During partition calculation, the table structure indicates the partition key and its type. Then, ODP parses the SQL statement to obtain the value of the partition key and calculates the partition based on the table structure and partition key type. In this way, ODP can forward the SQL statement to the server that hosts the partition.

In normal cases, ODP can accurately route an SQL statement to the server that hosts the corresponding partition based on the partition calculation result, thereby avoiding remote execution and improving efficiency. In ODP V3.2.0, an optimization is made for scenarios where the partition cannot be calculated. In these scenarios, ODP randomly selects a server from the tenants and optimizes the random selection of servers that distribute the partitions to improve the hit rate and minimize remote execution.

Replica routing (general deployment)

For strong-consistency read requests where the SQL statement specifies the table name, the system routes the request to the OBServer node that is the leader of the specified table partition. For weak-consistency read requests, login authentication requests, and strong-consistency read requests without specified table names, the system uses one of the following routing strategies: primary-standby load balancing (the default strategy), prefer standby, or prefer standby without major compaction.

Primary-standby load balancing (default)

The system selects a node for routing based on the following priority:

1. A node in the same region and IDC as the current node that is not involved in a major compaction.

2. A node in the same region but a different IDC from the current node that is not involved in a major compaction.

3. A node in the same region and IDC as the current node that is involved in a major compaction.

4. A node in the same region but a different IDC from the current node that is involved in a major compaction.

5. A node in a different region that is not involved in a major compaction.

6. A node in a different region that is involved in a major compaction.

Prefer standby

In a general deployment, the system supports the prefer standby read strategy. You can enable this strategy for a user by setting the proxy_route_policy variable. This strategy applies only to weak-consistency read requests and general deployments. If you want the system to route requests to followers instead of using primary-standby load balancing, set the proxy_route_policy variable to 'follower_first'.

If you set the proxy_route_policy variable to 'follower_first' in a general deployment and for weak-consistency read requests, the system routes requests to followers with the following priority:

1. A follower in the same region and IDC as the current node that is not involved in a major compaction.

2. A follower in the same region but a different IDC from the current node that is not involved in a major compaction.

3. A follower in the same region and IDC as the current node that is involved in a major compaction.

4. A follower in the same region but a different IDC from the current node that is involved in a major compaction.

5. A leader in the same region and IDC as the current node that is not involved in a major compaction.

6. A leader in the same region but a different IDC from the current node that is not involved in a major compaction.

7. A follower in a different region that is not involved in a major compaction.

8. A follower in a different region that is involved in a major compaction.

9. A leader in a different region that is not involved in a major compaction.

10. A leader in a different region that is involved in a major compaction.

Prefer standby without major compaction

In a general deployment, for weak-consistency read requests, if you set the proxy_route_policy variable to 'unmerge_follower_first', the system routes requests to followers that are not involved in a major compaction with the following priority:

1. A follower in the same region and IDC as the current node that is not involved in a major compaction.

2. A follower in the same region but a different IDC from the current node that is not involved in a major compaction.

3. A leader in the same region and IDC as the current node that is not involved in a major compaction.

4. A leader in the same region but a different IDC from the current node that is not involved in a major compaction.

5. A follower in the same region and IDC as the current node that is involved in a major compaction.

6. A follower in the same region but a different IDC from the current node that is involved in a major compaction.

7. A follower in a different region that is not involved in a major compaction.

8. A leader in a different region that is not involved in a major compaction.

9. A follower in a different region that is involved in a major compaction.

10. A leader in a different region that is involved in a major compaction.

Other cases

In a general deployment, for weak-consistency read requests, if the proxy_route_policy variable has a value other than 'follower_first' or 'unmerge_follower_first', the system uses the primary-standby load balancing strategy, which is the default strategy.