Each database proxy endpoint for ApsaraDB RDS for MySQL has two settings that control how SQL traffic is routed: a read/write attribute (which nodes handle writes vs. reads) and a read weight (how read traffic is distributed across those nodes). Configuring these correctly lets you scale read throughput, isolate reporting workloads, and safely perform maintenance without service interruption.
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_weightis selected. Ties are broken by position in the configuration list.Accumulation: After each round, every node's
current_weightincreases by its own weight.Reset: The selected node's
current_weightis 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_sessionmetric and wait for it to drop to 0.If
active_sessiondoes 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.
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