PolarProxy works as a proxy in a PolarDB cluster. This topic describes the following features of PolarProxy: consistency levels, transaction splitting, offloading of reads from the primary node, and hints.
PolarDB architecture and overview
PolarDB runs in a cluster architecture. Each cluster contains a primary node and one or more read-only nodes. By default, PolarDB provides two types of endpoints: primary endpoints and cluster endpoints. PolarProxy supports the cluster endpoint feature. Cluster endpoints include read/write endpoints and read-only endpoints. Read/write cluster endpoints support read/write splitting. secondary cluster endpoints can be used to evenly distribute connections to read-only nodes. For more information about read/write splitting, see Read/write splitting. The following sections describe the features of PolarProxy.
PolarDB uses asynchronous replication to synchronize data from the primary node to read-only nodes. In the read/write splitting mode, a read request that follows a write request may fail to obtain the latest write result. This issue can result in data inconsistency. PolarDB supports the following consistency levels:
- Eventual consistency
PolarDB synchronizes data from the primary node to read-only nodes by using asynchronous physical replication. Updates to the primary node are replicated to read-only nodes. In most cases, data changes are synchronized to read-only nodes with a latency of a few milliseconds. The latency is based on the load of write requests on the primary node. Asynchronous replication ensures eventual data consistency between the primary node and read-only nodes. This level does not guarantee data consistency when you query data through a read/write endpoint.
- Session consistency (recommended)
PolarDB uses efficient physical replication to avoid inconsistent query results caused by eventual consistency. The internal database proxy PolarProxy can be used to send read requests to read-only nodes that have updated data to achieve session consistency.Note Session consistency ensures that the query results are consistent only within the same session. Session consistency cannot ensure the consistency between different sessions. If none of the read-only nodes meet the consistency requirement, read requests are routed to the primary node. This may result in the heavy load on the primary node.
At the default Read Committed isolation level, PolarDB does not immediately start a transaction after it receives a transactional statement. You can use BEGIN or SET AUTOCOMMIT=0 to verify that PolarDB starts the transaction after a write operation occurs.
By default, PolarProxy forwards all requests of a transaction to the primary node. However, some frameworks encapsulate all requests in the same transaction. This results in the heavy load on the primary node. To fix this issue, you can enable the transaction splitting feature. This feature allows PolarProxy to identify the current transaction status. Then, you can distribute read requests to read-only nodes by using the load balancing module before the transaction is started.
- By default, the transaction splitting feature is enabled in the read/write splitting mode. For more information about how to modify the transaction splitting status, see Configure transaction splitting.
- The transaction splitting feature is not suitable for workloads that require global consistency. Therefore, before you enable transaction splitting, make sure that you are fully aware of the impacts of transaction splitting on your workloads.
- The configuration takes effect only on the connections that occur after the configuration.
Offload reads from the primary node
If you set the Offload Reads from Primary Node parameter to On for the primary node, normal read requests are no longer routed to the primary node. In transactions, read requests that require consistency are routed to the primary node to fit your needs. In addition, if all read-only nodes fail, the read requests are routed to the primary node. If your workloads do not require high consistency, you can set Consistency Level to Eventual Consistency to reduce the read requests that are routed to the primary node. You can also set the Transaction Splitting parameter to On to reduce the read requests that are routed to the primary node before a transaction is started. Broadcast requests, such as SET and PREPARE, are still routed to the primary node.
- By default, the Offload Reads from Primary Node parameter is set to On in the read/write splitting mode.
- Any changes to Offload Reads from Primary Node immediately take effect.
Add the prefix
/* FORCE_MASTER * / or
/* FORCE_SLAVE * / to a SQL statement to specify the direction that you want to route the SQL statement.
SELECT * FROM test is routed to a read-only node. If the SQL statement is changed to
/* FORCE_MASTER */ SELECT * FROM test, it is routed to the primary node.
- Hints have the highest routing priority and are not constrained by the consistency level or transaction splitting. Before you use a hint, evaluate the configuration.
- A hint cannot contain statements that change environment variables such as
/*FORCE_SLAVE*/ set names utf8;. Otherwise, an error may occur in the subsequent procedure.