PolarDB PolarProxy resides between your database system and your application, provides advanced features, such as automatic read/write splitting, transaction splitting, and connection pool. You can connect to the PolarDB cluster endpoint to use those advanced features.
PolarDB architecture and PolarProxy overview
A standard PolarDB cluster consists of a primary node and one or more read-only nodes. By default, PolarDB provides two types of endpoints: primary endpoints and cluster endpoints. PolarDB PolarProxy supports the cluster endpoint feature. Cluster endpoints include read/write endpoints and read-only endpoints. Read/write endpoints support read/writing splitting. For more information, see Read/write splitting. Read-only endpoints enable PolarDB clusters to evenly distribute read requests to read-only nodes based on the number of connections.
Only the PolarDB Cluster Edition supports the features that are related to cluster endpoints and PolarProxy. Single Node and Archive Database of PolarDB use a single-node architecture and do not require the cluster endpoint and PolarProxy features.
PolarDB clusters provide read/write splitting. This feature enables PolarDB clusters to distribute read and write requests from applications by using cluster endpoints. The built-in proxy of a PolarDB cluster forwards write requests to the primary node, and forwards read requests to the primary node or read-only nodes based on the loads. The number of requests that are not processed on a node indicates the loads on the node. For more information, see Read/write splitting.
PolarDB provides session-level connection pools and transaction-level connection pools.
- Session-level connection pools
A session-level connection pool applies to PHP short-lived connections.
Frequent PHP short-lived connections increase the loads on a database. Session-level connection pools help you reduce the loads that are caused by frequent PHP short-lived connections. If a client is disconnected from a connection, the system determines whether the connection is idle. If the connection is idle, PolarProxy retains the connection in the connection pool for a short period. If the client sends a connection request again and the idle connection is available in the connection pool, the system assigns the idle connection to the client. The assigned connection must match the
dbnamevariables that are specified in the client request. This reduces overheads that are required to establish a new connection to a database. If no idle connections are available in the connection pool, the system establishes a new connection to the database.Note If the session-level connection pool feature is used, the number of concurrent connections to a database is not reduced. However, the frequency at which connections between the client and the database are established is reduced. This helps you reduce the number of main threads that are consumed to execute MySQL statements and improve service performance. However, the connections to the database include the idle connections that are retained in the connection pool.
- Transaction-level connection pools
Transaction-level connection pools apply to a large number of connections, such as more than 10,000 connections.
Transaction-level connection pools are used to reduce the number of connections to the database and the loads that are caused by frequent short-lived connections. A large number of connections can be established between a client and PolarProxy, but only a small number of connections can be established between PolarProxy and a database. When a client sends a connection request, PolarProxy selects an appropriate connection in the connection pool to forward the request to the database. The selected connection matches the system variables that are specified in the request. After the current transaction is terminated, PolarProxy retains the connection in the connection pool.
Each transaction connection pool has the following limits:
- If you perform one of the following operations, your connection is locked until the
connection is closed. The locked connection is no longer retained in the connection
pool and become unavailable to other clients.
- Execute the PREPARE statement.
- Create a temporary table.
- Change user variables.
- Process large packets, for example, a packet of 16 MB or a larger size.
- Execute the LOCK TABLE statements.
- Execute multiple statements in a query.
- Call stored procedures.
- The FOUND_ROWS, ROW_COUNT, and LAST_INSERT_ID functions are not supported. These functions may return invalid results even if you can call these functions.
- If you specify the
wait_timeoutparameter for a connection, the value of
wait_timeoutmay not be applied to the client. This indicates that the connection to the client may not time out. This is because the system assigns a connection in the connection pool to each client request. If the time that is specified by the
wait_timeoutparameter is exceeded, the system closes only the connection between PolarProxy and the database. In this case, the connection between the client and PolarProxy remains established.
- The connection pool matches requests to connections by using the
time_zonevariables. If the requests include other session-level system variables, you must execute the SET statement in an explicit way to specify these additional variables after the connections are established. Otherwise, the connection pool may reuse connections whose system variables are already changed.
- Connections may be reused. Therefore, the
SELECT CONNECTION_ID()statement may return different
thread IDsfor the same connection in multiple queries.
- Connections may be reused. Therefore, after you execute the
SHOW PROCESSLISTstatement, the returned IP addresses and port numbers may be different from the actual IP addresses and port numbers of the clients.
- PolarProxy merges the results of the SHOW PROCESSLIST statements for all the nodes
and returns the merged result to clients. After the transaction-level connection pool
is enabled, the
thread IDof the connection between the client and PolarProxy is different from that between PolarProxy and the database. As a result, the KILL statement may return an error message even if the KILL statement is executed. You can execute the SHOW PROCESSLIST statement to check whether the connection is closed.
- If you perform one of the following operations, your connection is locked until the connection is closed. The locked connection is no longer retained in the connection pool and become unavailable to other clients.
- The configuration data about the connection pool takes effect on only the connections that are established after the configuration. For more information about how to configure the Offload Reads from Primary Node parameter, see Modify a cluster endpoint.
- If you need to enable the connection pool feature for a database account, multiple
client IP addresses that are allowed to access the database must be granted the same
permission. If you enable the connection pool feature and grant different database
or table permissions to these IP addresses, a permission error may occur. For example,
firstname.lastname@example.org granted the
email@example.com not granted the
database_apermission. In this case, a permission error may occur if the connection to one client is reused by the other client.
- This connection pool feature is provided by PolarDB PolarProxy and does not affect the connection pool feature of your client. If your client provides a connection pool, you do not need to enable the connection pool feature of PolarDB PolarProxy.
The parallel query feature is a parallel query framework that is launched in PolarDB for MySQL 8.0. PolarDB enables the query framework based on database costs and rules. For more information about parallel queries, see Parallel query.
- Why am I unable to retrieve a record immediately after I insert the record?
This is because in a read/write splitting architecture, a replication delay may occur during data replication between the primary node and read-only nodes. However, PolarDB supports session consistency. This allows you to query the updates within a session. Therefore, you can retrieve the inserted record after the data replication is completed.
- Why do read-only nodes have no workloads?
By default, requests in transactions are routed to only the primary node. If you use sysbench for stress testing, you can add
--oltp-skip-trx=onfor sysbench 0.5 to your code or add
--skip-trx=onto your code for sysbench 1.0. This skips BEGIN and COMMIT statements. If a large number of transactions cause excessively low workloads on read-only nodes, you can submit a ticket to enable the transaction splitting feature.
- Why does a node receive more requests than other nodes?
Requests are distributed to each node based on workloads. The node that has low workloads receives more requests.
- Does PolarDB support zero-latency data access?
No, PolarDB does not support zero-latency data access. If the primary node and read-only nodes of a PolarDB cluster process normal workloads, a replication delay is in milliseconds when data is replicated between the primary node and read-only nodes. If the read/write mode of a cluster endpoint is Read and Write (Automatic Read-write Splitting), PolarDB does not support zero-latency data access. If you require zero-latency data access, you can connect your applications to the primary endpoint so that all the read and write requests can be sent to the primary node of the PolarDB cluster.
- Are new read-only nodes automatically available to receive read requests?
Yes, a read/write splitting connection that is created after a read-only node is added forwards requests to the read-only node. Assume that the read/write splitting connection is created before a read-only node is added. If you need the connection to forward requests to the new read-only node, you must restart the application or perform other operations to close the connection and reestablish a connection.
Related API operations
|CreateDBEndpointAddress||Creates a public endpoint for a specified PolarDB cluster.|
|CreateDBClusterEndpoint||Creates a custom cluster endpoint for a specified PolarDB cluster.|
|DescribeDBClusterEndpoints||Queries the endpoint information about a specified PolarDB cluster.|
|ModifyDBClusterEndpoint||Modifies the configurations of a cluster endpoint for a specified PolarDB cluster.|
|ModifyDBEndpointAddress||Changes the endpoints of a specified PolarDB cluster, such as a custom cluster endpoint.|
|DeleteDBEndpointAddress||Releases the public-facing endpoint of the primary endpoint, the default cluster endpoint, or a custom cluster endpoint for a specified PolarDB cluster.|
|DeleteDBClusterEndpoint||Deletes a custom cluster endpoint for a specified PolarDB cluster.|