This topic describes the read/write splitting function of ApsaraDB RDS for MySQL in the dedicated proxy feature. This topic also describes how to enable this function.

Prerequisites

  • The RDS instance is a primary instance. It cannot be a read-only or disaster recovery instance.
  • The dedicated proxy feature is enabled for the RDS instance. For more information, see Dedicated proxy.
  • The RDS instance is attached with read-only instances. For more information, see Create a read-only ApsaraDB RDS for MySQL instance.

Background information

If the primary RDS instance needs to process a large number of read requests but only a small number of write requests, it may be overwhelmed. This may even 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. This allows you to scale the read capability of your database system. For more information, see Overview of ApsaraDB RDS for MySQL read-only instances.

After you create read-only RDS instances, you can enable the read/write splitting function. In this case, the endpoint that is generated to connect to the dedicated proxies of your database system is used to implement read/write splitting. After you add this endpoint to your application, write requests are routed to the primary RDS instance and read requests are routed to the read-only RDS instances.

Differences between the read/write splitting endpoint and the internal and public endpoints

After you enable the read/write splitting function and add the generated proxy endpoint to your application, all requests are routed to this endpoint. Then, the requests are routed from this endpoint to the primary and read-only RDS instances based on the request types and 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. To implement read/write splitting, you must add the endpoints and read weights of the primary and read-only RDS instances to your application.

Logic to route requests

  • The following requests are routed only to the primary RDS instance:
    • Data manipulation language (DML) statements, which are INSERT, UPDATE, DELETE, and SELECT FOR UPDATE
    • All data definition language (DDL) statements that are used to perform operations such as creating databases or tables, deleting databases or tables, and changing schemas or permissions
    • All requests that are encapsulated in transactions
    • Requests for user-defined functions
    • Requests for stored procedures
    • Requests for EXECUTE statements
    • Requests for multi-statements
    • Requests that involve temporary tables
    • Requests for SELECT last_insert_id() statements
    • All requests to query or modify user variables
    • Requests for SHOW PROCESSLIST statements
    • All requests for KILL statements in SQL (Note that these are not KILL commands in Linux.)
  • The following requests are routed to the primary RDS instance or its read-only RDS instances:
    • Read requests that are not encapsulated in transactions
    • Requests for COM_STMT_EXECUTE statements
  • The following requests are routed to all of the primary and read-only RDS instances:
    • All requests to modify system variables
    • Requests for USE statements
    • Requests for COM_STMT_PREPARE statements
    • Requests for COM_CHANGE_USER, COM_QUIT, and COM_SET_OPTION statements

Benefits

  • Easier maintenance with a unified endpoint

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

    If you enable the read/write splitting function, the generated proxy endpoint is used 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.

    In addition, you can scale up the read capability of your database system by creating read-only RDS instances. This relieves you from the need to modify the configuration data on your application.

  • Higher performance and lower maintenance cost with a native RDS link

    If you build a proxy layer on the cloud, data needs to be parsed and forwarded by a number of components before it reaches your database system. This increases the response latency. The read/write splitting function provided with ApsaraDB for RDS shortens the response latency, increases the processing speed, and reduces the 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 a latency threshold for data replication to the read-only RDS instances.

  • Highly available with instance-level health checks

    The read/write splitting function actively performs health checks on the primary and read-only RDS instances. If a read-only RDS instance unexpectedly exits or its latency exceeds the specified threshold, your database system stops routing read requests to the instance and redirects the requests that were destined for the instance to other healthy instances. This allows you to ensure service availability in the event of faults on a single read-only RDS instance. After the faulty read-only RDS instance is recovered, your database system resumes routing read requests to the instance.

    Note We recommend that you create at least two read-only RDS instances to avoid single points of failure (SPOFs).

Precautions

  • While you change the specifications of the primary RDS instance or its read-only RDS instances, a transient connection error may occur.
  • After you create a read-only RDS instance, only the requests over new connections can be routed to the new instance.
  • The proxy endpoint does not support SSL encryption.
  • The proxy endpoint does not support compression.
  • If the proxy endpoint is used for connection, all of the requests that are encapsulated in transactions are routed to the primary RDS instance.
  • If the proxy endpoint is used for read/write splitting, the read consistency of the requests that are not encapsulated in transactions cannot be ensured. If you require the read consistency, you can encapsulate the requests in transactions.
  • If the proxy endpoint is used for connection, the SHOW PROCESSLIST statement combines the results from the primary RDS instance and all of its read-only RDS instances. This allows the statement to return a result set.
  • If the short-lived connection optimization function is enabled, the SHOW PROCESSLIST statement may return idle connections.
  • If you execute multi-statements or stored procedures, the read/write splitting function is disabled and all of the subsequent requests over the current connection are routed to the primary RDS instance. To enable the read/write splitting function again, you must close the current connection and establish a new one.
  • The dedicated proxy feature supports the /*FORCE_MASTER*/ and /*FORCE_SLAVE*/ hints. However, requests that contain hints have the highest route priorities. Therefore, these requests are not constrained by consistency or transaction limits. Before you use the hints, you must check whether they are suitable for your workloads. In addition, the hints cannot contain statements such as /*FORCE_SLAVE*/ set names utf8; . Such statements can change environment variables. If you include such statements in the hints, errors may occur in your subsequent workloads.

Enable the read/write splitting function

  1. Log on to the ApsaraDB for RDS console.
  2. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where the target RDS instance resides.
    Select a region
  3. Find the target instance and click the instance ID.
  4. In the left-side navigation pane, click Database Proxy.
  5. On the Read/Write Splitting tab, click Enable now.
  6. Configure the following parameters
    Configure read weights
    Parameter Description
    Latency Threshold The maximum latency that is allowed for data replication from the RDS instance to its read-only RDS instances. If the latency of data replication to a read-only RDS instance exceeds the specified threshold, your database system stops routing read requests to the read-only RDS instance. This applies even if the read-only RDS instance has a high read weight.

    Valid values: 0 to 7200. Unit: seconds. A read-only RDS instance may replicate data at a latency due to SQL statement execution limits. We recommend that you set this parameter to a value that is greater than or equal to 30.

    Read Weight Distribution The read weight of each RDS instance in your database system. A higher read weight indicates more read requests to process. For example, the RDS instance is attached with three read-only RDS instances, and the read weights of these instances are 0, 100, 200, and 200. In this case, the RDS instance only processes write requests, and the three read-only RDS instances process all of the read requests at the 1:2:2 ratio.
    • Automatic Distribution: Your database system assigns a read weight to each RDS instance based on the specifications of the RDS instance. After you create a read-only RDS instance, your database system assigns a read weight to the instance and adds the instance to the read/write splitting link. For more information, see Rules of weight allocation by the system.
    • Customized Distribution: You must manually specify a read weight for each RDS instance. Valid values: 0 to 10000. After you create a read-only RDS instance, the read weight of the instance defaults to 0. You must manually specify a new read weight for the instance.
    Note If you have specified a replication latency for a read-only RDS instance, you cannot specify a read weight for the instance. For more information, see Set a replication delay for an RDS MySQL read-only instance.
  7. Click OK.

After the read/write splitting function is enabled, you must add the proxy endpoint to your application. This allows your database system to route write requests to the RDS instance and read requests to its read-only RDS instances.

Endpoint for read/write splitting

FAQ

Does the read/write splitting function support statements that contain hints?

Yes, the read/write splitting function supports statements that contain hints. You can use such statements to forcibly route requests to the primary RDS instance. For more information about the hint formats that are supported by ApsaraDB for RDS, see Rules of weight allocation by the system.

References

FAQ on read/write splitting