Introduction to the read/write splitting feature of ApsaraDB RDS for MySQL - ApsaraDB RDS

If your database system receives a large number of read requests but a small number of write requests, the performance of the primary ApsaraDB RDS for MySQL instance is affected. You can use read/write splitting of the database proxy feature for the database system. The database system can automatically forward the read and write requests by using database proxy endpoints. The database proxy endpoint forwards the read requests to the read-only RDS instances. This helps reduce the loads on the primary RDS instance. A database proxy endpoint is formerly named as a proxy terminal.

Introduction

After read-only RDS instances are created, you can enable read/write splitting to allow the system to forward write requests to the primary RDS instance and read requests to the read-only RDS instances based on the read weights of these instances. This helps reduce the loads on the primary RDS instance. For more information, see What are database proxies?

Benefits

Unified read/write splitting endpoint to facilitate maintenance Unified endpoint to facilitate maintenance
If you disable read/write splitting, you can perform read/write splitting only after you add the endpoints of the primary RDS instance and read-only RDS instances to your application.
If you enable read/write splitting, you need to only connect to a database proxy endpoint to process write requests and read requests that are automatically forwarded to the primary RDS instance and read-only RDS instances. This helps reduce maintenance costs.
You can also create read-only RDS instances to increase the read capability of your database system without the need to modify the configuration data on your application.
Native link to improve performance and reduce maintenance costs
If you build your own proxy layer in the cloud to implement read/write splitting, data must be parsed and forwarded by multiple components before the data reaches your database system. As a result, response latencies increase. The read/write splitting feature is embedded in the ApsaraDB RDS ecosystem to reduce response latencies, increase processing speeds, and reduce maintenance costs.
Configurable read weights and thresholds to ensure suitability in various scenarios
You can specify the read weights of the primary RDS instance and read-only RDS instances. You can also specify the latency threshold for data replication to the read-only RDS instances.
Instance-level health check to improve service availability
Read/write splitting automatically checks the health status of the primary RDS instance and its read-only RDS instances. If a read-only RDS instance unexpectedly goes down or its data replication latency exceeds the specified threshold, the system stops forwarding read requests to the read-only instance. The system redirects read requests that are destined for the faulty read-only RDS instance to healthy RDS instances in your database system. This ensures service availability even if an individual read-only RDS instance fails. After the faulty read-only RDS instance is recovered, ApsaraDB RDS resumes routing read requests to the instance.
Note
To mitigate the impacts of single points of failure (SPOFs), we recommend that you create at least two read-only RDS instances.

Logic used to route requests

The following requests are routed only to the primary RDS instance:
- Requests that are used to execute INSERT, UPDATE, DELETE, and SELECT FOR UPDATE statements.
- All requests that are used to perform DDL operations, such as the DDL operations that are performed to create databases or tables, delete databases or tables, and change schemas or permissions.
- All requests that are encapsulated in transactions
- Requests that are used to invoke user-defined functions.
- Requests that are used to run stored procedures.
- Requests that are used to execute EXECUTE statements
- Requests that are used to run multi-statement queries (For more information, see Multi-Statement.)
- Requests that involve temporary tables.
- Requests that are used to execute SELECT last_insert_id() statements
- All requests that are used to query or reconfigure user variables
- Requests that are used to execute KILL statements in SQL (These statements are different the KILL commands in Linux.)
The following requests are routed to the primary RDS instance or read-only RDS instances:
- Requests that are used to execute SELECT statements that are not encapsulated in transactions.
- Requests that are used to execute COM_STMT_EXECUTE statements
The following requests are routed to the primary RDS instance and read-only RDS instances:
- All requests that are used to reconfigure system variables.
- Requests that are used to execute USE statements
- Requests that are used to execute SHOW PROCESSLIST statements
  Note
  After a SHOW PROCESSLIST statement is executed, the dedicated proxy returns all processes that run on the primary RDS instance and read-only RDS instances in your database system.
- Requests that are used to execute COM_STMT_PREPARE statements
- Requests that are used to execute COM_CHANGE_USER, COM_QUIT, and COM_SET_OPTION statements

Processing logic based on the read and write attributes

Read and write attributes	Method to specify read weights	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. 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 and write	Automatic	A weight 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 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 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: indicates that the primary RDS instance is not involved in read-only request forwarding.
Connection error: indicates that a connection error is reported when the proxy terminal does not process read or write requests.
In read/write mode, when the weight of the primary RDS instance is set to 0, read requests are not forwarded to the primary RDS instance by default. However, if 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.
For more information, see Configure the read/write attributes and the read weight of the database proxy.