Configure read/write attributes and read weights

Prerequisites

The database proxy is enabled.
Your instance must be of the High-availability Edition or Cluster Edition.
Note
- High-availability Edition: You can create ApsaraDB RDS for MySQL read-only instances to implement read/write splitting.
- Cluster Edition: You can directly use the primary and secondary nodes of the instance to implement read/write splitting.

Read/write attributes

The read/write attribute can be set to Read/Write or Read-only.

Read/Write: Use this mode to enable read/write splitting and linearly scale your services.

In this mode, the access policy for the database proxy endpoint (formerly known as a proxy terminal) must include at least one primary instance and one read-only instance. All write requests are sent to the primary instance. This mode supports read/write splitting features such as transaction splitting and connection pool.
Read-only: Use this mode for read-only workloads, such as reporting.

In this mode, the access policy for the database proxy endpoint must include at least one read-only instance. The primary instance does not participate in request routing, and transaction splitting is not supported.

When the read/write attribute for a database proxy endpoint access policy is set to Read-only, ApsaraDB RDS allocates connections round-robin among the read-only instances specified in the endpoint's access policy. Each client connection is mapped to a connection on a single read-only instance. The primary instance does not participate in this allocation. The total number of available connections is the sum of connections across all configured read-only instances.

Note

For an ApsaraDB RDS for MySQL Cluster Edition instance, if the read/write attribute is set to Read/Write, write requests are sent only to the primary node. If the read/write attribute is set to Read-only, the primary node does not participate in routing, and ApsaraDB RDS allocates connections round-robin to the secondary nodes specified in the endpoint's access policy.
The database proxy's IP whitelist is synchronized with the primary instance's IP whitelist.
To prevent a single point of failure, create at least two read-only instances for a primary instance and deploy them across different availability zones. To reduce network latency from cross-zone access, you can enable the nearest access feature. For more information, see Configure nearest access.

Read/write attribute processing logic

Read/write attribute	Weight allocation method	Primary instance weight	Normal operation	Last read-only instance deleted	All read-only instances fail
Read-only	automatic distribution or custom	The read weight of the primary instance cannot be set.	Primary instance: Not readable or writable (no forwarding). Proxy access policy: Readable, not writable.	Primary instance: Not readable or writable (no forwarding). Proxy access policy: Not readable or writable (connection error).	Primary instance: Not readable or writable (no forwarding). Proxy access policy: Not readable or writable (connection error).
Read/Write	automatic distribution	Equals 0 For more information, see Default read weight allocation rules.	Primary instance: Not readable, but writable. Proxy access policy: Readable and writable.	Primary instance: Readable and writable. Proxy access policy: Readable and writable.	Primary instance: Readable and writable. Proxy access policy: Readable and writable.
	custom	Greater than 0	Primary instance: Readable and writable. Proxy access policy: Readable and writable.	Primary instance: Readable and writable. Proxy access policy: Readable and writable.	Primary instance: Readable and writable. Proxy access policy: Readable and writable.
	custom	Equals 0	Primary instance: Not readable, but writable. Proxy access policy: Readable and writable.	Primary instance: Readable and writable. Proxy access policy: Readable and writable.	Primary instance: Readable and writable. Proxy access policy: Readable and writable.

Note

No forwarding: In Read-only mode, the primary instance does not participate in forwarding read requests.
Connection error: In Read-only mode, if the proxy access policy is not readable or writable, connections fail with an error.
In Read/Write mode, when the read weight of the primary instance is set to 0, read requests are not forwarded to the primary instance by default. However, read requests are forwarded to the primary instance if all read-only instances become unhealthy, a hint is used to force routing, or transaction splitting is enabled.

Impact of weight changes on operations

Note

To view the minor engine version of a database proxy, see View the minor engine version of a database proxy. To upgrade the minor engine version, see Upgrade the minor engine version of a database proxy.

Action	Engine version 2.8.41 or later	Engine version earlier than 2.8.41
When a new session starts, does it connect to a node with a read weight of 0?	No	No
If the read weight of a node is changed from a non-zero value to 0, is the node removed from existing sessions?	No	No
If the read weight of a node is changed from a non-zero value to 0, are requests in existing sessions routed based on the new weight?	No	PS protocol: No Text protocol: Yes
If the read weight of a node is changed from 0 to a non-zero value, is the node added to existing sessions?	No	Yes
If the read weight of a node is changed from 0 to a non-zero value, are requests in existing sessions routed based on the new weight?	No	PS protocol: No Text protocol: Yes
Does removing a read-only node with a non-zero read weight cause a transient connection for existing sessions?	No Note Although database proxy versions in the 2.x series provide the `retry_failed_reads` mechanism, removing a read-only node while a result set is being returned may still cause a transient connection.	Yes
Does removing a read-only node with a read weight of 0 cause a transient connection for existing sessions?	No	A transient connection occurs only if the node is processing active requests.
If you kill a connection on a read-only node with a read weight of 0, is the connection terminated?	Yes	For database proxy versions in the 1.x series, the connection is terminated if the number of active sessions (active_session) does not drop to 0. Otherwise, the connection is not terminated, and the killed connection is automatically re-established.
If you kill a connection on a read-only node with a non-zero read weight, is the connection terminated?	Yes

Load balancing algorithms

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

Versions 2.25.4 and later add support for load balancing based on the number of active requests. The following two routing policies are supported:

Load balancing based on the number of active requests (version 2.25.4 and later)
Weight-based load balancing (all versions)

We recommend that you use load balancing based on the number of active requests because it delivers better peak performance and reduces the impact of a single node failure on the entire cluster.

Weight-based load balancing

Read requests are distributed based on the weight ratio of the nodes. This method uses a smooth weighted round-robin algorithm and the current_weight value of each node to distribute read requests based on their weights. The process is as follows:

Selection rule: In each scheduling round, the node with the highest current_weight value is selected. If multiple nodes have the same highest weight, the node that appears first in the configuration list is prioritized.
Weight accumulation: After each scheduling round, the current_weight value of each node increases by its own assigned weight.
Weight reset: If a node is selected, its current_weight is reduced by the sum of the weights of all nodes.

Example:

The read weight of the primary node is 100, the read weight of read-only node 1 is 200, and the read weight of read-only node 2 is 200.

Round	Primary node current_weight	Read-only node 1 current_weight	Read-only node 2 current_weight	Routed node
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

Load balancing based on the number of active requests

Requests are prioritized to nodes with lower loads. The rule is to route requests to the node that has the lowest (Number of active requests / Node weight) value.

Example:

The read weight of the primary node is 100, the read weight of read-only node 1 is 200, and the read weight of read-only node 2 is 200.

Round	Primary node active requests	Read-only node 1 active requests	Read-only node 2 active requests	Routed node
1	1 `(Number of active requests / Node weight)` = 1/100 = 0.01	5 `(Number of active requests / Node weight)` = 5/200 = 0.025	6 `(Number of active requests / Node weight)` = 6/200 = 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

Procedure

Go to the RDS Instances page, select a region in the top navigation bar, and then click the ID of the target instance.
In the left-side navigation pane, click Database Proxy.
In the Connection Information section, find the target database proxy endpoint, and click Modify Configuration in the Actions column.
In the dialog box that appears, next to Read/Write Attributes, select Read/Write (Read/Write Splitting) or Read-only (Primary Instance Not Connected to Receive Write Requests).
In the Read Weight Allocation section, select Automatic or Custom.
- Automatic: The system automatically allocates a read weight to each instance based on its specifications. New read-only instances that are added to the primary instance are automatically assigned a read weight based on the system allocation rule and included in the read/write splitting configuration.
- Custom: Manually set a read weight for each instance. The value must be in the range of 0 to 10,000. New read-only instances default to a read weight of 0 and must be configured manually.

Note

The nearest access feature ensures that requests are forwarded from your application to the database proxy. The read weight configuration ensures that requests are forwarded from the database proxy to the backend databases, which is not affected by the nearest access feature. You must use the two features together to minimize access latency.
The higher the read weight of an instance, the more read requests it processes. For example, assume that a primary instance has three read-only instances and their read weights are 0, 100, 200, and 200, respectively. This indicates that the primary instance does not process read requests (write requests are still automatically sent to the primary instance). The three read-only instances process read requests at a ratio of 1:2:2.
When you delete a read-only instance, its read weight is automatically removed, while the weights of the other instances remain unchanged.
You cannot set a read weight for an instance that has a configured replication delay.
The change takes effect immediately and does not cause service interruptions. However, how new weights affect existing connections depends on the database proxy's engine version. For details, see the "Impact of weight changes on operations" section.

Related APIs

API	Description
DescribeDBProxy	Queries the details of the database proxy for an RDS instance.
DescribeDBProxyEndpoint	Queries the configuration of a database proxy endpoint for an ApsaraDB RDS instance.
ModifyDBProxyEndpoint	Modifies the configuration of a database proxy endpoint for an ApsaraDB RDS instance.

Appendix 1: Route SQL statements with hints

In addition to using the read/write splitting weight allocation system, you can use SQL hints to route specific SQL statements to the primary and read-only instances of a High-availability Edition instance or to the primary and secondary nodes of a Cluster Edition instance.

The following hint formats are supported by ApsaraDB RDS read/write splitting:

For ApsaraDB RDS for MySQL High-availability Edition instances:

/*FORCE_MASTER*/: Routes the subsequent SQL statement to the primary instance.
/*FORCE_SLAVE*/: Routes the subsequent SQL statement to a read-only instance.

For ApsaraDB RDS for MySQL Cluster Edition instances:

/*FORCE_MASTER*/: Routes the subsequent SQL statement to the primary node.
/*FORCE_SLAVE*/: Routes the subsequent SQL statement to a secondary node.

Note

For an ApsaraDB RDS for MySQL High-availability Edition instance, if you use the /*FORCE_MASTER*/ hint, the SQL statement is routed to the primary instance even if the read weight of the primary instance is 0.
For an ApsaraDB RDS for MySQL Cluster Edition instance, if you use the /*FORCE_MASTER*/ hint, the SQL statement is routed to the primary node even if the read weight of the primary node is 0.

For example, for an ApsaraDB RDS for MySQL High-availability Edition instance, if you add the hint to the beginning of the following statement, the statement is routed to the primary instance regardless of the read weight settings.

/*FORCE_MASTER*/ SELECT * FROM table_name;

Appendix 2: Take a read-only instance offline without interruption

Assume that you have a read/write splitting environment that consists of a primary instance A and two read-only instances B and C. To take read-only instance C offline without service interruptions, follow these steps:

Go to the RDS Instances page, select a region, and then click the ID of instance A.
In the left-side navigation pane, click Database Proxy. In the Connection Topology Management section, click Modify Configuration.
In the Modify Proxy Endpoint (Terminal) Configuration dialog box, set the read weight of read-only instance C to 0.

Select Custom for the read weight allocation method and set the read weight of read-only instance C to 0.
On the Monitoring and Alerts page for read-only instance C, monitor the Session Connection chart and wait for the active_session metric to drop to 0.

Note
You only need to check whether active_session is 0. If the value does not drop to 0 after a long period, you can kill the sessions.
On the Database Proxy tab for primary instance A, remove read-only instance C from the database proxy endpoint.