Connection and routing

Connection

OceanBase Database Proxy (ODP, also known as OBProxy) provides database access and routing features for you to connect to and use OceanBase Database. When you use database features, ODP interacts with OBServer nodes. The interaction is transparent to users, and connection management is crucial to the interaction process. Database connections are categorized into physical connections and logical connections. Physical connections refer to network connections, and logical connections refer to sessions between ODP and OceanBase Database.

Characteristics

ODP has the following characteristics:

1. Proxy: ODP serves both as a client and as a server. It also ensures that the interaction behavior conforms to the MySQL protocol.
2. Features: ODP provides a wide range of connection features. For example, it allows you to access different clusters and tenants. It also supports physical standby databases, prepared statements in the distributed architecture, and the KILL and SHOW PROCESSLIST statements.
3. High availability: ODP can handle issues such as timeouts, server status changes, and network status changes and shield backend exceptions to ensure that users are not distracted by such issues.

Connection mapping

When you connect to a standalone database from a client, only one physical connection exists between your client and the database, as shown in the following figure.

When you use ODP to connect to OceanBase Database, one physical connection exists between your client and ODP, and multiple physical connections can exist between ODP and OBServer nodes, as shown in the following figure. The connection between your client and ODP is called a client session, and those between ODP and OBServer nodes are called server sessions.

If the data accessed by your client is stored on different OBServer nodes, ODP creates multiple physical connections with the OBServer nodes. It also manages and reuses these connections. However, the client perceives only one logical connection. This allows ODP to provide a wide range of features, such as read/write splitting, data access request routing for partitioned tables, prepared statements in a distributed architecture, and backend exception shielding.

Connection features

ODP changes the mapping of database connections from 1-to-1, as in the case of a standalone database, to M-to-N. Therefore, some connection features require additional treatment. For example, when you execute the SHOW PROCESSLIST statement to query the number of connections, you want to view the number of connections between your client and ODP, rather than the number of connections between ODP and OBServer nodes.

The following paragraphs describe general connection characteristics in detail:

• Connection stickiness: ODP does not support status synchronization for features such as transactions, temporary tables, and cursors. For these features, ODP sends subsequent requests to the node where the status starts, which makes status synchronization unnecessary. The drawback of this design is that the advantages of distributed systems cannot be fully utilized. Therefore, ODP will gradually support the distributed implementation of related features in the sequence of significance.
• Combined use of the SHOW PROCESSLIST and KILL statements: The SHOW PROCESSLIST statement shows the connections between a client and servers. For ODP, this statement shows only the connection between the client and ODP. It does not show the connections between ODP and OBServer nodes. The kill statement closes a client connection. After the client connection is closed, ODP closes the corresponding server connections. Before you execute the KILL statement, you must obtain the connection ID by using the SHOW PROCESSLIST statement.
• Impact on load balancing: The SHOW PROCESSLIST and KILL statements are specially treated. Therefore, they work properly only when they are sent to the same ODP node. In scenarios that require load balancing, such as a public cloud, multiple ODP nodes are mounted to a load balancer. In this case, if the SHOW PROCESSLIST and KILL statements are sent over two different connections, the load balancer may forward the requests to different ODP nodes. Therefore, we recommend that you do not use these statements in such scenarios.

Routing

As an important feature of OceanBase Database, routing enables quick data access in a distributed architecture.

A partition is a basic unit of data storage in OceanBase Database. After a table is created, mappings between the table and its partitions are generated. If the primary/standby mode is not adopted, a non-partitioned table is mapped to one partition. A partitioned table is mapped to multiple partitions.

Based on the routing feature, a query can be accurately routed to the OBServer node where the desired data is located. In addition, a read request without high consistency requirements can be routed to an OBServer node where a replica is located. This maximizes the usage of server resources. During the routing process, SQL statements, configuration rules, and OBServer node status are used as the input, and an available OBServer node IP address is generated as the output. The following figure shows the routing logic.

The custom parser module of ODP is used to parse SQL statements. ODP needs only to parse the database names, table names, and hints in DML statements. No complex expression analysis is required.

ODP must determine the optimal routing rule based on the actual situation. For example, DML requests for strong-consistency reads are routed to the OBServer node where the leader of the partition log stream is located, whereas DML requests for weak-consistency reads and other requests are evenly distributed to the OBServer nodes where the leader and followers are located. If an OceanBase cluster is deployed across regions, ODP provides logical data center (LDC)-based routing to preferentially route requests to an OBServer node in the same IDC, then an OBServer node in the same region, and finally an OBServer node in a different region. If the OceanBase cluster adopts the read-write splitting mode, ODP provides rules such as RZ-first, RZ-only, and non-major-compaction-first. You can set these rules based on your business needs. Here, RZ refers to read-only zone. In practice, the previous rules can be combined to generate a specific routing rule.

ODP obtains the location of the replica involved in the SQL request from a user. ODP attempts to obtain the routing table first from the local cache, then from the global cache, and finally from the OBServer node by initiating an asynchronous query task. ODP uses the trigger mechanism to update the routing table. ODP forwards requests to OBServer nodes based on the routing table. If a request cannot be locally processed by an OBServer node, the OBServer node places the feedback in the response packet and sends the packet to ODP. ODP determines whether to forcibly update the routing table in the local cache based on the feedback. Generally, the routing table changes only when an OBServer node undergoes a major compaction or when leader switchover is caused by load balancing.

ODP selects an OBServer node from the routing table obtained in the previous step based on the determined routing rule and forwards requests to the OBServer node after this OBServer node passes the blocklist and graylist checks.