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. ODP routes user requests to an OBServer, and ensures the performance of OceanBase Database operations.
We recommend that you learn about the concepts related to routing in advance for better understanding of this topic. For more information about the concepts, see Appendix: Basic concepts of OceanBase Database.
zone
region
server list
RootService (RS) list
location cache
replica
major compaction
strong consistency read/weak consistency read
read/write zone and read-only zone (RZ)
partitioned table
partition 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 availability zone (AZ), region, partition, and replica before you read the following content of ODP routing. Based on the partition design and distribution, and 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-partition 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 OBServer IP address 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 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 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 partition key types and calculation methods, and obtain the information about the leader and followers.
During partition calculation, ODP can obtain the partition key and its type based on the table schema. Then, ODP parses an SQL statement to obtain the value of the partition key, performs partition calculation based on the table schema and partition key type, and forwards the request to the OBServer corresponding to the partition.
In most cases, ODP can route SQL statements to the OBServer corresponding to the partition by performing partition calculation. 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. After optimization, SQL statements are randomly routed to an OBServer 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 for the corresponding partition of the table. In scenarios that involve weak consistency read, logon authentication requests, or strong consistency read without specifying table names for 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)
An SQL statement is routed to an OBServer based on the following rules. A smaller rule number indicates a higher priority.
The OBServer that is in the same region and IDC but is not undergoing a major compaction.
The OBServer that is in the same region but a different IDC, and is not undergoing a major compaction.
The OBServer that is in the same region and IDC and is undergoing a major compaction.
The OBServer that is in the same region but a different IDC, and is undergoing a major compaction.
The OBServer that is in a different region and is not undergoing a major compaction.
The OBServer that is in a different region and 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 read. 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 read, set proxy_route_policy to follower_first. Then, SQL statements are preferentially routed to a follower OBServer, even if this OBServer is undergoing a major compaction. An SQL statement is routed to an OBServer based on the following rules. A smaller rule number indicates a higher priority.
The follower OBServer that is in the same region and IDC but is not undergoing a major compaction.
The follower OBServer that is in the same region but a different IDC, and is not undergoing a major compaction.
The follower OBServer that is in the same region and IDC and is undergoing a major compaction.
The follower OBServer that is in the same region but a different IDC, and is undergoing a major compaction.
The leader OBServer that is in the same region and IDC but is not undergoing a major compaction.
The leader OBServer that is in the same region but a different IDC, and is not undergoing a major compaction.
The follower OBServer that is in a different region and is not undergoing a major compaction.
The follower OBServer that is in a different region and is undergoing a major compaction.
The leader OBServer that is in a different region and is not undergoing a major compaction.
The leader OBServer that is in a different region and is undergoing a major compaction.
Non-compaction-follower-first routing
In the case of normal deployment with weak consistency read, set proxy_route_policy to unmerge_follower_first. Then, SQL statements are preferentially routed to a follower OBServer that is not undergoing a major compaction. An SQL statement is routed to an OBServer based on the following rules. A smaller rule number indicates a higher priority.
The follower OBServer that is in the same region and IDC but is not undergoing a major compaction.
The follower OBServer that is in the same region but a different IDC, and is not undergoing a major compaction.
The leader OBServer that is in the same region and IDC but is not undergoing a major compaction.
The leader OBServer that is in the same region but a different IDC, and is not undergoing a major compaction.
The follower OBServer that is in the same region and IDC and is undergoing a major compaction.
The follower OBServer that is in the same region but a different IDC, and is undergoing a major compaction.
The follower OBServer that is in a different region and is not undergoing a major compaction.
The leader OBServer that is in a different region and is not undergoing a major compaction.
The follower OBServer that is in a different region and is undergoing a major compaction.
The leader OBServer that is in a different region and is undergoing a major compaction.
Other scenarios
In the case of normal deployment with weak consistency read, 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 RZs 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:
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 for the corresponding table partition.
If an SQL statement is to implement a strong consistency read and is in the format of "select 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.
Strong consistency read statements, excluding logon authentication requests and the preceding two types of requests, are routed based on the following priorities:
The OBServer that is in the same region and IDC, in a read/write zone, and not undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in a read/write zone, and not undergoing a major compaction.
The OBServer that is in the same region and IDC, in a read/write zone, and undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in a read/write zone, and undergoing a major compaction.
The OBServer that is in a different region, in a read/write zone, and not undergoing a major compaction.
The OBServer that is in a different region, in a read/write zone, and undergoing a major compaction.
RZ-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.
The OBServer that is in the same region and IDC, in an RZ, and not undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in an RZ, and not undergoing a major compaction.
The OBServer that is in the same region and IDC, in an RZ, and undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in an RZ, and undergoing a major compaction.
The OBServer that is in the same region and IDC, in a read/write zone, and not undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in a read/write zone, and not undergoing a major compaction.
The OBServer that is in the same region and IDC, in a read/write zone, and undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in a read/write zone, and undergoing a major compaction.
The OBServer that is in a different region, in an RZ, and not undergoing a major compaction.
The OBServer that is in a different region, in an RZ, and undergoing a major compaction.
The OBServer that is in a different region, in a read/write zone, and not undergoing a major compaction.
The OBServer that is in a different region, in a read/write zone, and undergoing a major compaction.
RZ-only routing
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.
The OBServer that is in the same region and IDC, in an RZ, and not undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in an RZ, and not undergoing a major compaction.
The OBServer that is in the same region and IDC, in an RZ, and undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in an RZ, and undergoing a major compaction.
The OBServer that is in a different region, in an RZ, and not undergoing a major compaction.
The OBServer that is in a different region, in an RZ, and 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.
The OBServer that is in the same region and IDC, in an RZ, and not undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in an RZ, and not undergoing a major compaction.
The OBServer that is in the same region and IDC, in a read/write zone, and not undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in a read/write zone, and not undergoing a major compaction.
The OBServer that is in the same region and IDC, in an RZ, and undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in an RZ, and undergoing a major compaction.
The OBServer that is in the same region and IDC, in a read/write zone, and undergoing a major compaction.
The OBServer that is in the same region but a different IDC, in a read/write zone, and undergoing a major compaction.
The OBServer that is in a different region, in an RZ, and not undergoing a major compaction.
The OBServer that is in a different region, in a read/write zone, and not undergoing a major compaction.
The OBServer that is in a different region, in an RZ, and undergoing a major compaction.
The OBServer that is in a different region, in a read/write zone, and undergoing a major compaction.
Usage notes of read/write splitting
An RZ must not be set only in a remote region. Otherwise, a session to the OBServer may need to be established for each request, which is time-consuming.
Based on an internal ODP strategy, if weak consistency read is enabled for a session, and the tenant has an RZ, the ODP checks whether the previously accessed OBServer is in the RZ. If not, the ODP closes this server session. In addition, if a read/write zone is in the same region and an RZ is in a remote region, the ODP always routes SQL statements to the read/write zone in the same region. Therefore, the ODP will constantly establish sessions to the OBServer in the read/write zone of the same region. After the processing of a request is complete, the ODP closes the current session and establishes another session for the next request.