This topic introduces the read/write splitting feature of ApsaraDB RDS for MySQL. This feature allows ApsaraDB RDS to route read and write requests to a dedicated proxy endpoint. Then, the dedicated proxy endpoint routes the read and write requests to the primary RDS instance and read-only RDS instances of your database system.

Background information

If your database system receives a large number of read requests and a small number of write requests, the primary RDS instance may fail to process read requests and your workloads may be interrupted. After read-only RDS instances are created, you can enable the read/write splitting feature. Then, you can use a dedicated proxy endpoint to perform read/write splitting. You need only to add the dedicated proxy endpoint to your application. After your application is connected to the dedicated proxy endpoint, ApsaraDB RDS routes write requests to the primary RDS instance and read requests to the read-only RDS instances based on the read weights of these instances. For more information, see Introduction to database proxies.

Benefits

  • Unified endpoint to facilitate maintenance

    If you do not enable the read/write splitting feature, 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 the read/write splitting feature, you can use a dedicated proxy endpoint to perform read/write splitting. You need only to add the dedicated proxy endpoint to your application. After your application is connected to the dedicated proxy endpoint, your database system routes read and write requests to the primary RDS instance and read-only RDS instances based on the read weights of these instances. This reduces 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 increase performance and reduce maintenance costs

    You can build your own proxy layer on the cloud to implement read/write splitting. In this case, data needs to 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 checks to ensure high availability

    The read/write splitting feature enables ApsaraDB RDS to actively check the health statuses of the primary RDS instance and read-only RDS instances. If a read-only RDS instance unexpectedly exits or its data replication latency exceeds the specified threshold, ApsaraDB RDS stops routing read requests to the instance. ApsaraDB RDS redirects the read requests that are destined for the faulty read-only RDS instance to other 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 We recommend that you create at least two read-only RDS instances to mitigate the impacts of single points of failure (SPOFs).

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 from 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.

Perform read/write splitting

For more information, see Enable and configure the dedicated proxy feature for an ApsaraDB RDS for MySQL instance.

Usage notes

For more information, see Usage notes for database proxies.

Verify read/write splitting on an RDS instance

For more information, see Verify read/write splitting on an ApsaraDB RDS for MySQL instance.

FAQ

For more information, see FAQ about database proxies.