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

The read/write attributes and read weights of a database proxy endpoint for ApsaraDB RDS for MySQL determine how the endpoint processes requests. You can adjust these settings for each database proxy endpoint as needed. This topic describes the read/write attributes, their processing logic, and how to configure them and their read weights in the console or by calling API operations.

Prerequisites

The database proxy is enabled.
The instance runs the High-availability Edition or Cluster Edition.
Note
- High-availability Edition: Create a read-only ApsaraDB RDS for MySQL instance for read/write splitting.
- Cluster Edition: The primary and secondary nodes of the instance are used for read/write splitting.

Read/write attributes

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

Read/Write: Supports the read/write splitting feature to linearly scale your services.
In this mode, you must configure the access policy of the database proxy endpoint with 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 pools.
Read-only: Supports read-only services, such as reports.
In this mode, you must configure the access policy of the database proxy endpoint with at least one read-only instance. The primary instance is not involved in routing. Transaction splitting is not supported.
If the read/write attribute of the database proxy endpoint is set to Read-only, ApsaraDB RDS allocates connections to the configured read-only instances in a round-robin fashion. Each client connection maps to a single connection on one read-only instance. The primary instance is not used for this allocation. The total number of available connections is the sum of connections across all read-only instances.

Note

For ApsaraDB RDS for MySQL Cluster Edition instances, 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 is not involved in routing. ApsaraDB RDS allocates connections to the secondary nodes that are configured in the access policy in a round-robin fashion.
The IP whitelist for the database proxy is kept in sync with the IP whitelist of the primary instance. Updates to the primary instance's whitelist are automatically applied to the database proxy's whitelist.
To avoid a single point of failure, you can create at least two read-only instances for a primary instance and deploy them across different zones. To reduce network latency from cross-zone access, you can enable the nearest access feature. For more information, see Configure nearest access.

Processing logic for read/write attributes

Read/write attribute	Weight allocation method	Primary instance weight	Normal case	Last read-only instance is deleted	All read-only instances fail
Read-only	System-assigned or Custom	The 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	System-assigned	Is 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	Is 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	Is 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 forward read requests.
Connection error: In Read-only mode, a connection error occurs if the proxy access policy is not readable or writable.
In Read/Write mode, if the primary instance's weight 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 a read-only node is abnormal, a hint is used to force routing, or transaction splitting is enabled.

Impact of weights on O&M

Note

To view the minor engine version of the 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.

O&M action	Engine version 2.8.41 or later	Engine version earlier than 2.8.41
Is a connection established to a node with a weight of 0 when a new session starts?	No	No
If a node's weight is changed from a non-zero value to 0, is the node removed from existing sessions?	No	No
If a node's weight is changed from a non-zero value to 0, are requests from existing sessions routed based on the new weight?	No	PS protocol: No Text-based protocol: Yes
If a node's weight is changed from 0 to a non-zero value, is the node added to existing sessions?	No	Yes
If a node's weight is changed from 0 to a non-zero value, are requests from existing sessions routed based on the new weight?	No	PS protocol: No Text-based protocol: Yes
Does removing a read-only node with a non-zero weight cause transient connections for existing sessions?	No Note Although the database proxy of the 2.x version has a `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 weight of 0 cause transient connections for existing sessions?	No	A transient connection occurs if there are existing sessions with requests being executed. Otherwise, no transient connection occurs.
If you kill a connection on a read-only node with a weight of 0, is the connection terminated?	Yes	In database proxy of the 1.x version, the connection is terminated if the number of active sessions 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 weight, is the connection terminated?	Yes

Load balancing algorithms

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

Versions 2.25.4 and later: A load balancing policy based on the number of active requests is added. The following two routing policies are supported:

Load balancing based on the number of active requests (Version 2.25.4 and later)
Load balancing based on weight ratio (All versions)

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

Weight-based load balancing

Read requests are distributed strictly based on the weight ratio of nodes. This method uses a round-robin algorithm and the current_weight value of each node to distribute read requests based on weight ratios. 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 weight, the one that appears earlier in the configuration list is selected.
Weight accumulation: After each scheduling round, the current_weight of each node is increased by its own weight.
Weight reset: If a node is selected, its current_weight is reduced by the sum of all node weights.

Example:

The primary node has a weight of 100, read-only node 1 has a weight of 200, and read-only node 2 has a weight of 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 preferentially sent to nodes with lower loads. The routing rule selects the node with the lowest current (Active Requests / Node Weight) value.

Example:

The primary node has a weight of 100, read-only node 1 has a weight of 200, and read-only node 2 has a weight of 200.

Round	Primary node active requests	Read-only node 1 active requests	Read-only node 2 active requests	Routed node
1	1 `(Active Requests / Node Weight)` = 1/100 = 0.01	5 `(Active Requests / Node Weight)` = 5/200 = 0.025	6 `(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. In the top navigation bar, select a region. Then, find the target instance and click its ID.
In the navigation pane on the left, click Database Proxy.
In the Connection Information section, locate the target database proxy endpoint and click Modify Configuration in the Actions column.
In the dialog box that appears, for 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 Distribution or Custom.
- Automatic Distribution: The system automatically allocates a read weight to each instance based on its instance type. New read-only instances added to the primary instance are automatically included in the read/write splitting configuration with system-assigned weights. No manual configuration is required.
- Custom: Manually set the read weight for each instance. The value can range from 0 to 10,000. New read-only instances added to the primary instance have a default read weight of 0 and must have their weights manually set.

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 a primary instance has a read weight of 0, and its three read-only instances have read weights of 100, 200, and 200. This means the primary instance does not process read requests (write requests are still automatically sent to it), and the three read-only instances process read requests at a ratio of 1:2:2.
If a read-only instance is deleted, its weight is automatically removed. The weights of other instances remain unchanged.
You cannot set a weight for an instance for which a replication delay is configured.
Changes to this parameter take effect in real time and do not cause service interruptions. After the change is applied, existing connections are not disconnected and reconnected. New and existing connections are allocated based on the new weights.

Related API operations

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

Appendix 1: Use hints to route SQL statements to a primary instance, read-only instance, or primary/secondary nodes

In addition to the read/write splitting weight allocation method, you can use hints as a supplementary SQL syntax to specify that SQL statements are executed on the primary and read-only instances of a High-availability Edition instance or on the primary and secondary nodes of a Cluster Edition instance.

ApsaraDB RDS for MySQL read/write splitting supports the following hint formats:

For ApsaraDB RDS for MySQL High-availability Edition instances:

/*FORCE_MASTER*/: Specifies that the subsequent SQL statement is executed on the primary instance.
/*FORCE_SLAVE*/: Specifies that the subsequent SQL statement is executed on a read-only instance.

For ApsaraDB RDS for MySQL Cluster Edition instances:

/*FORCE_MASTER*/: Specifies that the subsequent SQL statement is executed on the primary node.
/*FORCE_SLAVE*/: Specifies that the subsequent SQL statement is executed on a secondary node.

Note

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

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

/*FORCE_MASTER*/ SELECT * FROM table_name;

Appendix 2: Best practices for taking a read-only instance offline without service interruption

Assume that you have a read/write splitting environment with one primary instance A and two read-only instances B and C. To take read-only instance C offline without service interruptions, perform the following steps.

Go to the RDS Instances page, select the region where instance A is located, and then click the ID of instance A.
In the navigation pane on the left, 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 to 0 for read-only node C.
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.
Note
Check whether the value of active_session is 0. If the value does not drop to 0 after a long period, you can kill the session.
On the Database Proxy tab for primary instance A, remove read-only instance C from the database proxy endpoint.