PolarDB for MySQL clusters come with the read/write splitting feature. This feature requires that applications connect to a cluster endpoint in Read/Write (Automatic Read/Write Splitting) mode. After the connection is established, write requests are automatically forwarded to the primary node. Read requests are intelligently distributed to the primary node or read-only nodes, based on the workload (number of pending requests) on each node.
Advantages
Read consistency
After a client establishes a connection to the backend by using a cluster endpoint, the built-in database proxy for read/write splitting automatically creates connections to the primary node and read-only nodes for the client. Over the connection (the same session), the proxy intelligently directs requests to the most suitable nodes based on the data synchronization status of each database node. This process ensures data accuracy (correct read results after write operations) and balances read and write requests among the nodes.
Native read/write splitting for enhanced performance
You can create your proxy in the cloud to implement read/write splitting. However, high latency may occur because data is parsed and forwarded by multiple components before the data is written to a database. PolarDB uses a built-in proxy that is deployed in existing secure links to implement read/write splitting and ensure that data does not pass through multiple components. This reduces latency and accelerates data processing.
Easy maintenance
Implementing read/write splitting in traditional mode is complex and time-consuming. You must specify the endpoints of the primary node and each read-only node in applications. You must also configure forwarding rules to send write requests to the primary node and read requests to all nodes.
PolarDB provides a cluster endpoint that applications can use to establish a connection to the cluster. When the connection is established, the applications can send read and write requests to the cluster. Write requests are automatically forwarded to the primary node, and read requests are intelligently forwarded to the primary node or read-only nodes. The read/write splitting process is transparent to users and effectively reduces maintenance costs.
You can scale the processing capacity of the system by adding more read-only nodes, without the need to modify the applications.
Node health checks for enhanced database availability
The read/write splitting module of PolarDB automatically performs health checks on all nodes in a cluster. If a node fails or the latency exceeds a specified threshold, PolarDB stops sending read requests to the node. Read and write requests are sent to other healthy nodes to ensure that applications can access the cluster even if a read-only node fails. After the node recovers, PolarDB automatically adds the node to the list of nodes that are available to receive requests.
Free feature that reduces resource and maintenance costs
The read/write splitting feature is provided free of charge.
Rules for forwarding requests
Forwarding rules in read/write splitting mode:
The following requests are forwarded only to the primary node:
All DML operations, including statements such as INSERT, UPDATE, DELETE, and SELECT FOR UPDATE.
All DDL operations, such as creating databases or tables, deleting databases or tables, and modifying table schemas or permissions.
Transaction requests.
User-defined functions.
Stored procedures.
EXECUTE statements.
Multi-statement queries. For more information, see Multi-statement.
Requests that involve temporary tables.
SELECT last_insert_id()
statements.User environment variable queries or modifications.
The following requests are forwarded to the primary node or read-only nodes:
NoteThe requests are forwarded to the primary node only if the Primary Node Accepts Read Requests parameter is set to Yes.
Non-transaction read requests.
COM_STMT_EXECUTE statements.
The following requests are forwarded to all nodes:
System environment variable modifications.
USE statements.
COM_STMT_PREPARE statements.
COM_CHANGE_USER, COM_QUIT, and COM_SET_OPTION statements.
SHOW PROCESSLIST
statements.NoteAfter you execute a
SHOW PROCESSLIST
statement, PolarDB returns all processes that are running on nodes in your database system.KILL statements in SQL (not KILL commands in Linux).
Forwarding rules in read-only mode:
DDL and DML operations are not supported.
Requests are forwarded to read-only nodes in load balancing mode.
Read requests are not forwarded to the primary node even if the primary node is added to the list of selected nodes.
Features
PolarDB provides the following features for read/write splitting: