Background information
OceanBase Database Proxy (ODP) offers users a range of features including database access and routing. By connecting directly to ODP, users can seamlessly utilize the OceanBase database. During the usage of database features, ODP interacts with OBServer nodes, ensuring a transparent and streamlined interaction process. Notably, connection management plays a crucial role in facilitating this process.
Features
ODP's connection management provides the following features:
Proxy feature: ODP functions as both a client and a server. It ensures that the interaction behavior conforms to the MySQL protocol.
Connection feature: ODP implements many connection functionalities, including accessing different clusters and tenants, and supporting primary and backup databases, prepared statements in distributed architecture, and commands like
killandshow processlist.High availability feature: ODP can handle issues such as timeouts and changes in server or network status, which shields users from backend exceptions.
Connection mapping
When you establish a connection between a client and a standalone database, there is only one physical connection between them, as shown in the following figure.
However, when you use ODP to establish a connection between a client and an OceanBase database, the situation is different. As shown in the following figure, there is one physical connection between the client and ODP, while there may be multiple physical connections between ODP and OBServer nodes. The connection between the client and ODP is referred to as a client connection, and the connection between ODP and an OBServer node is referred to as a server connection.
If the data accessed by the client is stored on different OBServer nodes, ODP will establish multiple physical connections with these OBServer nodes. It will also manage and reuse these connections, presenting only one logical connection from the client's perspective. Based on this, ODP can provide a wide range of features, such as primary/standby database splitting, read/write splitting, data access request routing for partitioned tables, prepared statements in distributed architecture, and backend exception shielding.
Connection feature
ODP changes the mapping mode of database connections to M:N. Therefore, some connection functionalities require additional handling. For example, when using the show processlist command to query the number of connections, users want to query the number of connections between the client and ODP, rather than the number of connections between ODP and OBServer nodes.
The following describes several common connection functionalities:
Connection stickiness
ODP has not yet implemented status synchronization for all functionalities, such as transaction status, temporary table status, and cursor status. For these functionalities, ODP will only forward subsequent requests to the node where the status starts, eliminating the need for status synchronization. However, the drawback is that the advantages of a distributed system cannot be fully leveraged. Therefore, ODP will gradually support status synchronization for related functionalities in the order of significance.
Combined use of
show processlistandkillThe
show processlistcommand is used to display the connections between the client and the server. For ODP, this command displays only the connection between the client and ODP, not the connections between ODP and OBServer nodes.The
killcommand is used to terminate a client connection. When a client connection is closed, ODP will also close the corresponding server connections. Before usingkillto terminate a client connection, you must first obtain the connection ID by usingshow processlist.Impact on load balancing
Because ODP handles
show processlistandkill, both commands must be sent to the same ODP node to work properly.In scenarios that require load balancing, such as a public cloud, multiple ODP nodes are mounted to a load balancer. In this case, if
show processlistandkillare 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 commands in such scenarios.