This topic describes how to use a dedicated proxy endpoint of an ApsaraDB RDS for MySQL instance to implement read/write splitting. You must set the Read/Write Attribute parameter to Read/Write for the proxy terminal under which the used dedicated proxy endpoint is created.

Background information

If your database system processes a large number of read requests and a small number of write requests, a single primary RDS instance may fail to efficiently process the read requests. This may interrupt your workloads. In this case, you can create one or more read-only RDS instances to offload read requests from the primary RDS instance and increase the read capability of your database system. For more information, see Overview of read-only ApsaraDB RDS for MySQL instances.

After read-only RDS instances are created, you can enable the read/write splitting feature. Then, you can use a dedicated proxy endpoint to implement read/write splitting. After your application is connected to this 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.

If the internal or public endpoint of the primary RDS instance is added to your application, all requests are routed to the primary RDS instance. If you want to implement read/write splitting, you must add the endpoints and read weights of the primary and read-only RDS instances to your application.

Benefits

  • Easier maintenance with a unified endpoint

    If you do not enable the read/write splitting feature, you must add the endpoints of the primary and read-only RDS instances to your application. This way, your database system can route write requests to the primary RDS instance and read requests to the read-only RDS instances.

    If you enable the read/write splitting feature, you can use a dedicated proxy endpoint to implement read/write splitting. After your application is connected to this endpoint, your database system routes read and write requests to the primary and read-only RDS instances based on the read weights of these instances. This reduces maintenance costs.

    You can also increase the read capability of your database system by creating read-only RDS instances. You do not need to modify the configuration data on your application.

  • Higher performance and lower maintenance costs with a native link

    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 built in the ApsaraDB RDS ecosystem and can efficiently reduce response latencies, increase processing speeds, and reduce maintenance costs.

  • Ideal in various use scenarios with configurable read weights and thresholds

    You can specify the read weights of the primary and read-only RDS instances. You can also specify the latency threshold for data replication to the read-only RDS instances.

  • High availability with instance-level health checks

    The read/write splitting feature enables ApsaraDB RDS to actively check the health statuses of the primary 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 in the event of faults on individual read-only RDS instances. 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 data definition language (DDL) operations, such as the DDL operations 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 invoke 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 not the KILL commands in Linux.)
  • The following requests are routed to the primary 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 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 the processes that run on the primary 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

Precautions

For more information, see Usage notes for read/write splitting.

FAQ

For more information, see FAQ about proxy terminals.