Configure the read and write attributes and the read weight for an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

The read and write attributes and the read weight of a database proxy endpoint that is connected to an ApsaraDB RDS for MySQL instance determine the types of requests that the database proxy endpoint needs to process and the processing logic. You can modify the read and write attributes and the read weight of a database proxy endpoint based on your business requirements. This topic describes the read and write attributes of a database proxy endpoint and the request processing logic based on the read and write attributes. This topic also describes how to configure the read and write attributes and the read weight of a database proxy endpoint in the ApsaraDB RDS console and by calling API operations.

Prerequisites

The database proxy feature is enabled for your RDS instance.
Your RDS instance runs RDS High-availability Edition or RDS Cluster Edition.
Note
- If your RDS instance runs RDS High-availability Edition, you can create read-only RDS instances for your RDS instance to implement read/write splitting.
- If your RDS instance runs RDS Cluster Edition, you can use the primary and secondary nodes in the RDS cluster to implement read/write splitting. An RDS instance that runs RDS Cluster Edition is referred to as an RDS cluster.

Read and write attributes

The read and write attributes can be Read/Write and Read-only.

Read/Write: This attribute is used to support read/write splitting. Read/write splitting helps linearly scale the workload processing capabilities of your database system.
If you select this attribute, make sure that the database proxy endpoint that you use is connected to the primary RDS instance and at least one read-only RDS instance, and the database proxy endpoint forwards all write requests to the primary RDS instance. A database proxy endpoint is formerly known as a proxy terminal. This attribute supports read/write splitting capabilities such as transaction splitting and connection pooling.
Read-only: This attribute is used to process read-only requests, such as reporting.
If you select this attribute, make sure that the database proxy endpoint that you use is connected to at least one read-only RDS instance, and the database proxy endpoint does not forward requests to the primary RDS instance. This attribute does support transaction splitting.
If you select the Read-only attribute for a database proxy endpoint, the system distributes new connections to the read-only RDS instances that are connected to the database proxy endpoint in turn. In this case, each client connection is distributed to one read-only RDS instance, and the primary RDS instance is not involved. The total number of available connections is the sum of connections to all read-only RDS instances that are connected to the database proxy endpoint.

Note

If your RDS instance runs RDS Cluster Edition and you select the Read/Write attribute for a database proxy endpoint, all write requests are sent only to the primary node. If your RDS instance runs RDS Cluster Edition and you select the Read-only attribute for a database proxy endpoint, the system distributes new connections to the secondary nodes that are connected to the database proxy endpoint in turn, and the primary node is not involved.
The IP address whitelist of the database proxy is consistent with the IP address whitelist of the primary RDS instance. If the IP address whitelist of the primary RDS instance is updated, the IP address whitelist of the database proxy is also updated.
To prevent single points of failure (SPOFs), we recommend that you create at least two read-only RDS instances and deploy the read-only RDS instances across zones. If you want to reduce the network latency caused by cross-zone access, you can enable the nearest access feature. For more information, see Configure the nearest access feature.

Processing logic based on the read and write attributes

Read and write attribute	Method to specify a weight	Weight of a primary RDS instance	Normal case	Last read-only RDS instance is deleted	All read-only RDS instances are faulty
Read-only	Automatic or custom	You cannot specify a read weight for your primary RDS instance.	Primary RDS instance: does not process read or write requests. In this case, no request forwarding is performed. Database proxy endpoint: processes only read requests.	Primary RDS instance: does not process read or write requests. In this case, no request forwarding is performed. Database proxy endpoint: does not process read or write requests. In this case, a connection error occurs.	Primary RDS instance: does not process read or write requests. In this case, no request forwarding is performed. Database proxy endpoint: does not process read or write requests. In this case, a connection error occurs.
Read/write	Automatic	A weight that is equal to 0 For more information, see Rules of read weight allocation by the system.	Primary RDS instance: processes only write requests. Database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Database proxy endpoint: processes read and write requests.
	Custom	A weight that is greater than 0	Primary RDS instance: processes read and write requests. Database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Database proxy endpoint: processes read and write requests.
	Custom	A weight that is equal to 0	Primary RDS instance: processes only write requests. Database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Database proxy endpoint: processes read and write requests.

Note

No request forwarding: The primary RDS instance is not involved in read-only request forwarding.
Connection error: An error is reported if read and write requests cannot be processed by a database proxy endpoint with the Read-only attribute.
In read/write mode, if the weight of the primary RDS instance is set to 0, read requests are not forwarded to the primary RDS instance. If the read-only RDS instances of the primary RDS instance are faulty, a forceful hint is specified, or transaction splitting is enabled, the read requests are forwarded to the primary RDS instance.

Impacts of weights on O&M

Note

For information about how to view the database proxy version, see Release notes for the database proxy version. For more information about how to upgrade the database proxy version, see Upgrade the database proxy version.

O&M action	Database proxy version 2.8.41 or later	Database proxy version earlier than 2.8.41
Is a connection established to an RDS instance for which the weight is set to 0 when a new session is created?	No	No
When the weight of an RDS instance is changed from a non-zero value to 0, is the instance removed from the existing sessions?	No	No
When the weight of an RDS instance is changed from a non-zero value to 0, are the requests in the existing sessions forwarded based on the new weight?	No	Prepared statement protocol: No Text protocol: Yes
When the weight of an RDS instance is changed from 0 to a non-zero value, is the instance added to the existing sessions?	No	Yes
When the weight of an RDS instance is changed from 0 to a non-zero value, are the requests in the existing sessions forwarded based on the new weight?	No	Prepared statement protocol: No Text protocol: Yes
If the read-only RDS instance for which the weight is set to a non-zero value is removed, do transient connections occur on existing sessions?	No Note The `retry_failed_reads` parameter is supported for a database proxy that runs a version of 2.x. However, if the read-only RDS instance is removed when the result set is not completely returned, transient connections may occur.	Yes
If the read-only RDS instance for which the weight is set to 0 is removed, do transient connections occur on existing sessions?	No	If an existing session is available and a request is being executed in the session, a transient connection occurs on the session. If no existing sessions are available or no requests are being executed in the sessions, the sessions remain connected.
If a connection on a read-only RDS instance for which the weight is set to 0 is terminated, is the connection disconnected?	Yes	If the database proxy runs a version of 1.x and the value of the active_session metric is not reduced to 0, the connection is disconnected. If the database proxy runs a version of 1.x and the value of the active_session metric is reduced to 0, the connection is not disconnected and can be automatically established.
If a connection on a read-only RDS instance for which the weight is set to a non-zero value is terminated, is the connection disconnected?	Yes

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Database Proxy.
In the Connection Information section, find the database proxy endpoint that you want to modify and click Modify Configuration in the Actions column.
In the dialog box that appears, set Read/Write Attributes to Read/Write (Read/Write Splitting) or Read-only (Primary Instance Not Connected to Receive Write Requests).
Set the Read Weight Allocation parameter to Automatic or Custom.
- Automatic: The system assigns a read weight to each RDS instance in your database system based on the specifications of the RDS instance. After you create a read-only RDS instance, the system automatically assigns a read weight to the read-only RDS instance and adds the read-only RDS instance to the read/write splitting link. For more information, see Rules of read weight allocation by the system.
- Custom: You can specify a read weight for each instance. Valid values: 0 to 10000. If you select this option, each time a read-only RDS instance is created for the primary RDS instance, the system sets the read weight of the read-only RDS instance to 0. You must modify the read weight of the read-only RDS instance.

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 method that is used to assign read weights. A higher read weight indicates that more read requests need to be processed. For example, three read-only RDS instances are attached to the primary RDS instance. The read weight of the primary RDS instance is 0, and the read weights of the three read-only RDS instances are 100, 200, and 200. In this case, the primary RDS instance processes only write requests, and the three read-only RDS instances process all read requests based on the 1:2:2 ratio.
If you delete a read-only RDS instance, the read weight of the read-only RDS instance is also removed. However, the read weights of other RDS instances remain unchanged.
You cannot specify weights for read-only RDS instances whose data replication latencies are specified.
After you modify this parameter, the new read weights immediately take effect. The modification does not cause service unavailability. After the modification, the existing connections remain valid. The requests that are sent over existing and new connections are forwarded based on the new weights.

Related operations

Operation	Description
DescribeDBProxy	Queries the details of a database proxy of an instance.
DescribeDBProxyEndpoint	Queries the connection settings for a database proxy endpoint of an instance. A database proxy endpoint is formerly known as a proxy terminal.
ModifyDBProxyEndpoint	Modifies the connection settings for a database proxy endpoint of an instance.

Appendix 1: Use hints to specify the SQL statements to be executed on the primary and read-only RDS instances that run RDS High-availability Edition or on the primary and secondary nodes in an RDS cluster

In addition to the weight allocation system of read/write splitting, hints serve as complementary SQL syntax to specify the SQL statements to be executed on the primary and read-only RDS instances that run RDS High-availability Edition or on the primary and secondary nodes in an RDS cluster.

The following section describes the hint formats that are supported by read/write splitting:

RDS instances on RDS High-availability Edition

/*FORCE_MASTER*/: specifies that the subsequent SQL statements are executed on the primary instance.
/*FORCE_SLAVE*/: specifies that the subsequent SQL statements are executed on the read-only instances.

RDS instances on RDS Cluster Edition

/*FORCE_MASTER*/: specifies that the subsequent SQL statements are executed on the primary node.
/*FORCE_SLAVE*/: specifies that the subsequent SQL statements are executed on the secondary nodes.

Note

If you use /*FORCE_MASTER*/ for an RDS instance that runs RDS High-availability Edition, the SQL statements are executed on the primary RDS instance even if the read weight of the primary instance is 0.
If you use /*FORCE_MASTER*/ for an RDS instance that runs RDS Cluster Edition, the SQL statements are executed on the primary node in the cluster even if the read weight of the primary node is 0.

For example, if you add a hint before the following statement for an RDS instance that runs RDS High-availability Edition, the statement is executed on the primary RDS instance regardless of the weight of the primary instance:

/*FORCE_MASTER*/ SELECT * FROM table_name;

Appendix 2: Imperceptibly remove a read-only RDS instance

Assume that one primary RDS instance (Primary Instance A) and two read-only RDS instances (Read-only Instances B and C) are provisioned in your database system to implement read-write splitting. If you want to imperceptibly remove Read-only Instance C, perform the following steps:

Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which Primary Instance A resides. Then, find Primary Instance A and click the instance ID.
In the Connection Topology Management section of the Database Proxy page, click Modify Configuration.
In the Modify Proxy Endpoint (Terminal) Configuration dialog box, set the read weight of Read-only Instance C that you want to remove to 0.
On the Monitoring and Alerts page of Read-only Instance C, check the value of the active_session metric for the Session monitoring item and wait the value of the metric to be reduced to 0.
Note
You need only to check whether the value of the active_session metric is 0. If the value is not 0 for a long period of time, you can terminate sessions.
Go to the Database Proxy page of Primary Instance A to delete Read-only Instance C from the database proxy endpoint.