ODP considers the replica locations involved in user requests, the read/write splitting routing strategy configured by users, the optimal path to OceanBase Database instances deployed in multiple regions, and the status and load of each server in OceanBase Database. ODP then routes user requests to the optimal OBServer node, ensuring high-performance operation of OceanBase Database.
Before you read this section, we recommend 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
Partitioned table
Partitioning key
Execution plans of OceanBase Database Proxy
OceanBase Database Proxy supports three types of execution plans: local, remote, and distribute. ODP is designed to avoid remote plans, which are inefficient and have poor performance, and to generate local plans.
Role of ODP routing
After you understand the basic concepts and physical meanings of the preceding terms, you can understand the routing logic of ODP. ODP needs to accurately route SQL statements based on the design and physical distribution of partitions and the high efficiency of local execution plans. The process includes SQL statement parsing, partition calculation, partition information acquisition, and replica strategy selection.
Routing for non-partitioned tables
ODP can directly use the replica information in the Location Cache to route requests to non-partitioned tables. ODP maintains a mapping between partitions and OBServer nodes. The cache validity of the mapping information between tables and partitions and OBServer nodes is as follows:
If the required information is not found in the cache, ODP queries the latest mapping information from the OBServer node and caches the information.
If the required information exists in the cache but is unavailable, ODP requeries the OBServer node for the information and updates the cache.
If the required information exists and is available in the cache, ODP directly uses the information.
Routing for partitioned tables
Compared with non-partitioned tables, routing for partitioned tables involves additional steps for calculating and querying partition IDs. After obtaining the information in the Location Cache, the system determines whether the table has primary and secondary partitions. Based on the partition key type and calculation method, the system calculates the partition ID and obtains the information about the primary and secondary replicas.
The system performs the following operations during partition calculation: Obtain the partition key and its type from the table structure, and obtain the values of the partition key from the SQL statement. Based on the table structure and the partition key type, the system performs partition calculation to forward the request to the corresponding partition.
In most cases, after ODP calculates the partition route, ODP can route SQL statements to the corresponding server of the partition, thereby avoiding remote execution and improving efficiency. In ODP V3.2.0, an optimization is introduced for scenarios where the partition route cannot be calculated. Instead of randomly selecting a tenant server, ODP randomly selects a server from those 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, login authentication, and strong-consistency read without specified table name, the system uses one of the following routing strategies: primary-standby load balancing (default), 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:
A node in the same region and IDC as the current node that is not involved in a major compaction.
A node in the same region but a different IDC from the current node that is not involved in a major compaction.
A node in the same region and IDC as the current node that is involved in a major compaction.
A node in the same region but a different IDC from the current node that is involved in a major compaction.
A node in a different region that is not involved in a major compaction.
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 takes precedence over primary-standby load balancing.
To use this strategy for weak-consistency read requests in a general deployment, execute the set @proxy_route_policy='follower_first'; statement. In this case, the system routes read requests to followers instead of using primary-standby load balancing. The system selects a node for routing based on the following priority:
A node in the same region and IDC as the current node that is not involved in a major compaction.
A node in the same region but a different IDC from the current node that is not involved in a major compaction.
A node in the same region and IDC as the current node that is involved in a major compaction.
A node in the same region but a different IDC from the current node that is involved in a major compaction.
A node in the same region and IDC as the current node that is not involved in a major compaction.
A node in the same region but a different IDC from the current node that is not involved in a major compaction.
A node in the same region and IDC as the current node that is involved in a major compaction.
A node in the same region but a different IDC from the current node that is involved in a major compaction.
A node in the same region and IDC as the current node that is not involved in a major compaction.
A node in the same region but a different IDC from the current node that is not 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 read requests to followers that are not involved in a major compaction, with the following priority:
A node in the same region and IDC as the current node that is not involved in a major compaction.
A node in the same region but a different IDC from the current node that is not involved in a major compaction.
A node in the same region and IDC as the current node that is involved in a major compaction.
A node in the same region but a different IDC from the current node that is involved in a major compaction.
A node in the same region and IDC as the current node that is not involved in a major compaction.
A node in the same region but a different IDC from the current node that is not involved in a major compaction.
A node in the same region and IDC as the current node that is involved in a major compaction.
A node in the same region but a different IDC from the current node that is involved in a major compaction.
A node in the same region and IDC as the current node that is not involved in a major compaction.
A node in the same region but a different IDC from the current node that is not involved in a major compaction.
Others
In a general deployment, for weak-consistency read requests, if the proxy_route_policy variable has a value other than 'unmerge_follower_first', the system uses the primary-standby load balancing strategy.