Set read/write splitting attributes and read weights for an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

Prerequisites

Before you begin, make sure that you have:

Enabled the database proxy for your RDS instance
An instance running the High-availability Edition or Cluster Edition
- High-availability Edition: Create at least one read-only instance to enable read/write splitting
- Cluster Edition: The primary and secondary nodes handle read/write splitting natively

Read/write attributes

Choose a mode

Mode	Best for	Write requests	Read requests
Read/Write	Scaling read throughput across primary and read-only instances	Always routed to the primary instance	Distributed across configured nodes based on weight
Read-only	Isolating read-only workloads such as reports	Not accepted — clients receive a connection error	Distributed across read-only instances in round-robin

Read/Write mode requires at least one primary instance and one read-only instance in the proxy access policy. It supports transaction splitting and connection pools.

Read-only mode requires at least one read-only instance. The primary instance is not involved in routing, and transaction splitting is not supported. Each client connection maps to a single connection on one read-only instance. The total connection capacity equals the sum of connections across all configured read-only instances.

For Cluster Edition instances, Read/Write mode sends writes only to the primary node, while Read-only mode routes reads to secondary nodes in round-robin. The IP whitelist for the database proxy stays in sync with the primary instance's whitelist automatically.

Routing behavior in edge cases

The table below shows what happens in Read/Write mode when read-only instances are unavailable, and in Read-only mode under all weight scenarios.

Mode	Weight method	Primary weight	Normal operation	Last read-only instance deleted	All read-only instances fail
Read-only	System-assigned or Custom	Cannot be set	Primary: no reads or writes (no forwarding). Proxy: readable, not writable	Primary: no reads or writes. Proxy: connection error	Primary: no reads or writes. Proxy: connection error
Read/Write	System-assigned	0 (default)	Primary: writes only, no reads. Proxy: reads and writes	Primary: reads and writes. Proxy: reads and writes	Primary: reads and writes. Proxy: reads and writes
Read/Write	Custom	Greater than 0	Primary: reads and writes. Proxy: reads and writes	Primary: reads and writes. Proxy: reads and writes	Primary: reads and writes. Proxy: reads and writes
Read/Write	Custom	0	Primary: writes only, no reads. Proxy: reads and writes	Primary: reads and writes. Proxy: reads and writes	Primary: reads and writes. Proxy: reads and writes

In Read-only mode, "no forwarding" means the primary instance does not accept read requests. If the proxy has no readable instances, clients receive a connection error.

In Read/Write mode with the primary weight set to 0, reads are not sent to the primary instance by default. Reads are sent to the primary instance only if a read-only node is unhealthy, a hint forces routing to it, or transaction splitting is enabled.

Load balancing algorithms

The proxy supports two algorithms for distributing read requests across nodes.

Versions earlier than 2.25.4: Only weight-based load balancing is available.

Versions 2.25.4 and later: Both algorithms are available. Use load balancing based on active requests unless you have a specific reason to prefer strict weight-based distribution — it delivers better peak performance for clusters and reduces the impact of a single node failure.

Load balancing based on active requests (recommended)

Routes each request to the node with the lowest (Active Requests / Node Weight) ratio. This adapts in real time to uneven load and is less affected by slow nodes.

Example with primary weight 100, read-only node 1 weight 200, and read-only node 2 weight 200:

Round	Primary active requests	Read-only node 1 active requests	Read-only node 2 active requests	Routed to
1	1 (ratio: 0.01)	5 (ratio: 0.025)	6 (ratio: 0.03)	Primary node
2	2	4	6	Read-only node 1
3	2	5	3	Read-only node 2
4	1	2	3	Read-only node 1
5	1	2	1	Read-only node 2
6	0	3	3	Primary node

Weight-based load balancing

Distributes read requests strictly by weight ratio using a round-robin algorithm. Each scheduling round selects the node with the highest current_weight value, then adjusts weights as follows:

Selection: The node with the highest current_weight is selected. Ties are broken by position in the configuration list.
Accumulation: After each round, every node's current_weight increases by its own weight.
Reset: The selected node's current_weight is reduced by the sum of all node weights.

Example with primary weight 100, read-only node 1 weight 200, and read-only node 2 weight 200:

Round	Primary `current_weight`	Read-only node 1 `current_weight`	Read-only node 2 `current_weight`	Routed to
1	0	0	0	Primary node
2	−400	200	200	Read-only node 1
3	−300	−100	400	Read-only node 2
4	−200	100	100	Read-only node 1
5	−100	−200	300	Read-only node 2
6	0	0	0	Primary node

Configure read/write attributes and read weights

Go to the RDS Instances page. In the top navigation bar, select a region, then click the ID of the target instance.
In the left navigation pane, click Database Proxy.
In the Connection Information section, find the target proxy endpoint and click Modify Configuration in the Actions column.
In the dialog, set Read/Write Attributes to one of the following:
- Read/Write (Read/Write Splitting)
- Read-only (Primary Instance Not Connected to Receive Write Requests)

In the Read Weight Allocation section, select a weight method:

Method	Behavior	When to use
Automatic Distribution	The system assigns weights based on instance type. New read-only instances are automatically included with system-assigned weights — no manual update needed.	Homogeneous clusters where instance capacity is similar
Custom	Set an explicit weight (0–10,000) for each instance. New read-only instances default to 0 and must be configured manually.	Heterogeneous clusters where you want precise control over traffic distribution

How weights work: A higher weight means more read requests. For example, if the primary has weight 0 and three read-only instances have weights 100, 200, and 200, reads are distributed in a 1:2:2 ratio. The primary still receives all writes.

The read weight controls how the proxy distributes reads to backend databases and is independent of the nearest access feature. Use both together to minimize latency.

Instances with a replication delay configured cannot have their weight set.

Deleting a read-only instance removes its weight automatically. Other instances' weights are unchanged.

Weight changes take effect immediately without service interruption. Existing connections are not disconnected; both new and existing connections are routed based on the updated weights.

Before removing a read-only instance from the proxy: Set its weight to 0 first, wait for active sessions to drain, then remove it. See Take a read-only instance offline without service interruption for the full procedure.

Effect of weight changes on existing sessions

The behavior of weight changes on active sessions depends on the database proxy engine version. To check or upgrade your version, see View the minor engine version of a database proxy and Upgrade the minor engine version of a database proxy.

Scenario	Engine version 2.8.41 and later	Earlier than 2.8.41
New session connects to a node with weight 0?	No	No
Weight changed from non-zero to 0: node removed from existing sessions?	No	No
Weight changed from non-zero to 0: existing sessions routed by new weight?	No	PS (Prepared Statement) protocol: No. Text-based protocol: Yes
Weight changed from 0 to non-zero: node added to existing sessions?	No	Yes
Weight changed from 0 to non-zero: existing sessions routed by new weight?	No	PS protocol: No. Text-based protocol: Yes
Removing a read-only node with non-zero weight causes transient connections?	No. The proxy has a `retry_failed_reads` mechanism, but returning a result set at the moment of removal may still cause a transient connection.	Yes
Removing a read-only node with weight 0 causes transient connections?	No	Yes if requests are executing on that node at removal time; otherwise No
Killing a connection on a read-only node with weight 0: is the connection terminated?	Yes	1.x proxy: terminated only if active sessions exist; otherwise the connection is not terminated and is automatically re-established
Killing a connection on a read-only node with non-zero weight: is the connection terminated?	—	—

Best practices

Take a read-only instance offline without service interruption

To remove a read-only instance (for example, instance C in a cluster with primary A and read-only instances B and C) without disrupting traffic:

Go to the RDS Instances page, select the region, and click the ID of primary instance A.
In the left navigation pane, click Database Proxy. In the Connection Topology Management section, click Modify Configuration.
In the Modify Proxy Endpoint (Terminal) Configuration dialog, set the read weight for read-only node C to 0.
Go to the Monitoring and Alerts page for read-only instance C. In the Session Connection section, monitor the active_session metric and wait for it to drop to 0.

If active_session does not reach 0 after a reasonable wait, kill the remaining sessions manually.
On the Database Proxy tab for primary instance A, remove read-only instance C from the proxy endpoint.

Route specific SQL statements with hints

Use SQL hints to force a statement to run on a specific node — useful for writes inside a SELECT or queries that must always use a read-only instance.

High-availability Edition instances:

/*FORCE_MASTER*/ — routes the statement to the primary instance, even if its read weight is 0
/*FORCE_SLAVE*/ — routes the statement to a read-only instance

Cluster Edition instances:

/*FORCE_MASTER*/ — routes the statement to the primary node, even if its read weight is 0
/*FORCE_SLAVE*/ — routes the statement to a secondary node

Add the hint at the beginning of the statement:

/*FORCE_MASTER*/ SELECT * FROM table_name;

Reduce cross-zone latency

To avoid a single point of failure, create at least two read-only instances for a primary instance and deploy them across different zones. Enable the nearest access feature to minimize network latency from cross-zone traffic. The nearest access feature controls how your application connects to the proxy; read weights control how the proxy routes requests to backend databases. Configure both together for optimal performance.

FAQ

Is the primary instance still readable after I add read-only instances?

After read-only instances are added, the primary instance remains readable and writable at all times. Adding read-only instances does not affect the readability or writability of the primary instance.

The primary instance has its own direct connection endpoint (for example, rm-xxxxx.mysql.rds.aliyuncs.com). This endpoint connects directly to the primary instance without going through the database proxy, so the primary instance is always readable and writable through this endpoint regardless of read weight configuration.

The database proxy endpoint (for example, mr-xxxxx.rwlb.rds.aliyuncs.com) is used for read/write splitting. When connected through the proxy endpoint, read and write requests are routed based on the configured read weights. When the primary instance read weight is set to 0, the proxy does not forward read requests to the primary instance by default. However, the primary instance itself remains readable and writable through its direct connection endpoint. When all read-only instances experience latency exceeding the threshold or become unavailable, read requests are automatically routed back to the primary instance to ensure business continuity.

To control read/write routing through the proxy endpoint, adjust the read weights in the Database Proxy configuration.

API reference

API operation	Description
DescribeDBProxy	Query the details of the database proxy for an RDS instance
DescribeDBProxyEndpoint	Query the access policy of a database proxy endpoint
ModifyDBProxyEndpoint	Modify the access policy of a database proxy endpoint

What's next

Configure nearest access — reduce latency by routing application connections to the nearest proxy node
Use the transaction splitting feature — improve read throughput for mixed read/write transactions
Set the connection pool type — reduce connection overhead for high-concurrency workloads
Default read weight allocation rules — understand how the system assigns weights in Automatic Distribution mode