A PolarDB for MySQL cluster supports the read/write splitting feature. In read/write splitting mode, you need only to use a cluster endpoint to send requests from applications to a PolarDB cluster. In this mode, write requests are automatically forwarded to the primary node. Read requests are automatically forwarded to the primary node or read-only nodes based on the load of each node. The load of a node is based on the number of pending requests.
- Read consistency
When a client connects to a backend server by using a cluster endpoint, the built-in database proxy for read/write splitting automatically establishes connections between the primary node and read-only nodes. In a session, the built-in database proxy first selects an appropriate node based on the data synchronization progress of each database node. Then, the proxy forwards read and write requests to the nodes whose data is up-to-date and valid. This helps balance read and write requests among the nodes.
- Native read/write splitting for enhanced performance
You can create your own 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 the latency and speeds up data processing.
- Easy maintenance
For traditional database services, read/write splitting is time-consuming. You must specify the endpoints of the primary node and each read-only node in applications. You also need to configure forwarding rules to send write requests to the primary node and read requests to read-only nodes.
PolarDB provides a cluster endpoint that can be used by an application to connect to the cluster. After the application is connected to the cluster, you can send read and write requests from the application to the cluster. Write requests are automatically forwarded to the primary node, and read requests are automatically forwarded to the primary node or read-only nodes. The process of read/write splitting is transparent to users. This reduces maintenance costs.
You need only to add read-only nodes to scale the processing capabilities of your cluster. You do not need to modify your application.
- 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 this node. Write and read requests are sent to other healthy nodes. This ensures 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 available free of charge.
Logic to forward requestsForwarding logic in read/write splitting mode:
- The following requests are forwarded only to the primary node:
- Requests for DML statements such as INSERT, UPDATE, DELETE, and SELECT FOR UPDATE
- All data definition language (DDL) statements used to perform operations such as creating databases or tables, deleting databases or tables, and changing schemas or permissions.
- All requests that are encapsulated in transactions
- Requests for user-defined functions
- Requests for stored procedures
- Requests for EXECUTE statements
- Requests for multi-statements
- Requests that involve temporary tables
- Requests for SELECT last_insert_id() statements
- All requests to query or modify user environment variables
- All requests for KILL statements in SQL (not KILL commands in Linux)
- The following requests are forwarded to the primary node or read-only nodes:
Note The following requests are forwarded to the primary node only after Primary Node Accepts Read Requests is disabled. By default, the primary node does not process read requests.
- Read requests that are not encapsulated in transactions
- Requests for COM_STMT_EXECUTE statements
- The following requests are forwarded to all nodes:
- All requests to modify system environment variables
- Requests for USE statements
- Requests for COM_STMT_PREPARE statements
- Requests that are sent to execute COM_CHANGE_USER, COM_QUIT, and COM_SET_OPTION statements.
- Requests for SHOW PROCESSLIST statements
Note After a SHOW PROCESSLIST statement is executed, PolarDB returns all processes that are running on any nodes in your database system.
- 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 service nodes, read requests are still not forwarded to it.
PolarDB provides the following features for read/write splitting:
- Load balancing
PolarDB supports automatic scheduling based on the load of each node. Read requests are automatically forwarded to read-only nodes based on the number of active connections. This ensures load balancing between read-only nodes.Load balancing includes the Primary Node Accepts Read Requests and Transaction Splitting features:
- Primary Node Accepts Read Requests
After you enable the feature that offloads read requests from the primary node to read-only nodes, common read requests are no longer forwarded to the primary node. In a transaction, read requests that require high consistency are still forwarded to the primary node to meet business requirements. If all read-only nodes fail, read requests are forwarded to the primary node. If your workloads do not require high consistency, you can set the consistency level to eventual consistency to reduce the number of read requests that are forwarded to the primary node. You can also use the transaction splitting feature to reduce the number of read requests that are forwarded to the primary node before a transaction is started. However, broadcast requests such as SET and PREPARE requests are forwarded to the primary node.Note
- The Primary Node Accepts Read Requests parameter is available only if the Read/write Mode parameter is set to Read and Write (Automatic Read-write Splitting). The Primary Node Accepts Read Requests feature is disabled by default. For information about how to modify Primary Node Accepts Read Requests settings, see Configure PolarProxy.
- The configuration of Primary Node Accepts Read Requests immediately takes effect after it is modified.
- Transaction Splitting
Before transaction splitting is enabled, the database proxy sends all requests in a transaction to the primary node. This ensures the read and write consistency of transactions in a session. However, this causes heavy loads on the primary node. After transaction splitting is enabled, the database proxy identifies the transaction status. Then, read requests that are sent before the transaction is started are forwarded to read-only nodes by using the load balancing module. During this process, the read and write consistency is ensured. For more information, see Split transactions.
- Primary Node Accepts Read Requests
- Consistency Level
PolarDB asynchronously replicates the updates from the primary node to read-only nodes. In read/write splitting mode, a read request that follows a write request may fail to fetch the latest data. PolarDB provides the eventual consistency, session consistency, and global consistency options. For more information, see Consistency levels.
- Connection Pool
PolarDB supports session-level connection pools and transaction-level connection pools. You can select a connection pool based on your business requirements to reduce the database loads that are caused by a large number of connections. For more information, see Connection pools.
- Persistent connections
PolarDB adds the persistent connection feature to prevent temporary service interruptions or connection failures. These issues may be caused by O&M operations, such as specification upgrades, switchovers, and minor version updates. The issues may also be caused by anomalies such as server malfunctions. Persistent connections can improve the availability of PolarDB. For more information, see Persistent connections.
- Hints have the highest routing priority and are not limited by consistency levels or transaction splitting. Before you use hints, perform an evaluation.
- You can use hints only after you set the read/write mode to Read and Write (Automatic Read-write Splitting) for a cluster endpoint. Hints are not supported when the read/write mode of a cluster endpoint or a primary endpoint is set to Read Only. For more information about the read/write mode of a cluster endpoint, see Read/write modes for cluster endpoints.
- You can add
/*FORCE_SLAVE*/to an SQL statement to forcibly specify the routing direction for the SQL statement.
For example, assume that
SELECT * FROM testis routed to a read-only node. If the SQL statement is changed to
/*FORCE_MASTER*/ SELECT * FROM test, the statement is routed to the primary node.
- You can add
/*force_node='<Node ID>'*/to an SQL statement to forcibly specify a node to execute the SQL statement.
/*force_node='pi-bpxxxxxxxx'*/ show processlistspecifies that the
show processliststatement is executed on a node named
pi-bpxxxxxxxx. If the node is unavailable, the error message
force hint server node is not found, please check.is returned.
- You can add
/*force_proxy_internal*/set force_node = '<Node ID>'to an SQL statement to forcibly specify a node to execute all SQL statements.
For example, if you execute the
/*force_proxy_internal*/set force_node = 'pi-bpxxxxxxxx'statement, all read requests are routed to a node named
pi-bpxxxxxxxx. If the node fails, the error message
set force node 'rr-bpxxxxx' is not found, please check.is returned.Note
- If you want to execute the preceding statement that contains the hint on the official command line of MySQL, add the -c parameter in the statement. Otherwise, the hint becomes invalid because the official command line of MySQL filters out the hint. For more information, see mysql Client Options.
- We recommend that you do not use
/*force_proxy_internal*/in SQL statements. Otherwise, all subsequent SQL statements are routed to the specified node and the read/write splitting feature becomes invalid.
- Hints cannot contain statements that change environment variables. For example, if
/*FORCE_SLAVE*/ set names utf8;, errors may occur.