Connections
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.
Features
ODP has the following characteristics:
- Proxy: ODP serves both as a client and as a server. It also ensures that the interaction behavior conforms to the MySQL protocol.
- Features: ODP implements many 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 commands such as
killandshow processlist. - 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 connect to an OceanBase database by using an ODP, in addition to the physical connection between your client and the ODP, multiple physical connections may exist between ODP and the OBServer nodes, as shown in the following figure. The connection between your client and an ODP is called a client connection, and those between the ODP and OBServer nodes are called server connections.
If the data accessed by your client is stored on different OBServer nodes, ODP will create multiple physical connections with the OBServer nodes. It will also manage and reuse 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 characteristics
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 run the show processlist command to view 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 status synchronization for the related features in the order of their significance.
- Combined use of the
show processlistandkillcommands: Theshow processlistcommand shows the connections between a client and servers. For ODP, this command shows only the connection between the client and ODPs. It does not show the connections between ODPs and OBServer nodes. Thekillcommand kills a client connection. After the client connection is closed, ODP closes the corresponding server connections. To run thekillcommand to kill a client connection with ODP, you must first obtain the ID of the connection by using theshow processlistcommand. - Impact of load balancing: The
show processlistandkillcommands are treated specially when used with ODP. They work properly only when they are sent to the same ODP. In environments that require load balancing, such as a public cloud, multiple ODPs are mounted to a load balancing agent. In this case, if theshow prcesslistandkillcommands are sent over two different connections, they may be forwarded to different ODPs. Therefore, we recommend that you do not use these commands 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 maps to one partition. A partitioned table maps 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 status are used as the input, and an available OBServer IP address is generated as the output.
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 needs to determine the optimal routing rule based on different conditions. 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) routing to preferentially route requests to an OBServer node in the same IDC, then an OBServer node in the same city, and finally an OBServer node in a different city. If the OceanBase cluster adopts the read-write splitting mode, ODP provides rules of RZone-first read, RZone-only read, and non-major-compaction-first read. You can set these rules based on your business needs. In practice, the previous rules can be combined to generate a specific routing rule.
Based on the request SQL statements of a user, ODP obtains the IP address of the OBServer node where the corresponding replica is located. ODP attempts to obtain the route 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 route table. ODP forwards requests to OBServer nodes based on the route 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 route table in the local cache based on the feedback. Generally, the route table changes only when an OBServer node undergoes a major compaction or when leader switching is caused by load balancing.
ODP selects an OBServer node from the route table based on the determined routing rule in the previous step and forwards requests to the OBServer node after this OBServer node passes the blocklist and graylist checks.