Configure the read and write attributes and the read weight of a database proxy endpoint in ApsaraDB RDS for MySQL - 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. For more information, see Enable the database proxy feature.
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. For more information, see Create a read-only ApsaraDB RDS for MySQL instance.
- 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.
- To avoid single points of failure, we recommend that you create at least two read-only instances in different zones for a primary instance.

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. For more information, see What is read/write splitting?
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. For more information, see Use the transaction splitting feature and Set the connection pool type.
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. For more information, see Use the transaction splitting feature.
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.

Processing logic based on the read and write attributes

Read and write attribute	Method to specify read weight	Weight of a primary RDS instance	Normal case	After the last read-only RDS instance is deleted	After 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. Connection settings of a 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. Connection settings of a 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. Connection settings of a 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. Connection settings of a database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Connection settings of a database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Connection settings of a database proxy endpoint: processes read and write requests.
	Custom	A weight that is greater than 0	Primary RDS instance: processes read and write requests. Connection settings of a database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Connection settings of a database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Connection settings of a database proxy endpoint: processes read and write requests.
	Custom	A weight that is equal to 0	Primary RDS instance: processes only write requests. Connection settings of a database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Connection settings of a database proxy endpoint: processes read and write requests.	Primary RDS instance: processes read and write requests. Connection settings of a database proxy endpoint: processes read and write requests.

Note

No request forwarding: indicates that the primary RDS instance is not involved in read-only request forwarding.
Connection error: indicates that an error is reported if read and write requests cannot be processed in the connection settings of 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.

Procedure

Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which your RDS instance resides, find the RDS instance, and then click the instance ID.
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 Read Weight Allocation 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 the read weight of 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 Read Weight Allocation parameter specifies 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. For more information, see Configure a data replication latency for a read-only ApsaraDB RDS for MySQL instance.
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: 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;