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

If your primary instance needs to process a large number of read requests but only a small number of write requests, you can create one or more read-only instances to offload read requests from your primary instance. This allows you to ensure service stability. For more information, see Overview of ApsaraDB RDS for MySQL read-only instances.

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

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

After you enable read/write splitting and add the endpoint of the dedicated proxy to your application, all requests are routed to this endpoint, and then to the primary and read-only instances based on the request types and the read weights of these instances.

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

Logic to route requests

  • The following requests are routed only to the primary instance:
    • Data manipulation language (DML) statements, which are INSERT, UPDATE, DELETE, and SELECT FOR UPDATE
    • All data definition language (DDL) statements 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 environment 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 instance or its read-only 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 instances:
    • All requests to modify system environment 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 read/write splitting, you must add the endpoints of the primary and read-only instances to your application. This makes write requests routed to the primary instance and read requests routed to the read-only instances.

    If you enable read/write splitting, the endpoint of the dedicated proxy is used for read/write splitting. After your application is connected to this endpoint, requests are routed to the primary and read-only 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 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 separate proxy layer on the cloud to implement read/write splitting, statements need to be parsed and forwarded by a number of components before they reach your database system. This increases response latency. The read/write splitting function provided with ApsaraDB for RDS shortens response latency, increases processing speed, and reduces 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 instances. You can also specify a latency threshold for each read-only instance.

  • Highly available with instance-level health check

    The read/write splitting module actively performs health checks on the primary and read-only instances. If an instance breaks down or its latency exceeds the specified threshold, the read/write splitting module stops routing 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 instance. After the instance is recovered, the read/write splitting module resumes routing read requests to it.

    Note To avoid single points of failure (SPOFs), we recommend that you create at least two read-only instances.

Precautions

  • A brief disconnection may occur while you change the specifications of the primary instance or its read-only instances.
  • After you create a read-only instance, only the requests over new connections can be routed to the read-only instance.
  • The endpoint of the dedicated proxy does not support SSL encryption.
  • The endpoint of the dedicated proxy does not support compression.
  • If the endpoint of the dedicated proxy is used for connection, all of the requests encapsulated in transactions are routed to the primary instance.
  • If the endpoint of the dedicated proxy 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, encapsulate the requests in transactions.
  • If the endpoint of the dedicated proxy is used for connection, the SHOW PROCESSLIST statement combines the results from the primary and read-only instances and returns a result set.
  • If short-lived connection optimization is enabled, the SHOW PROCESSLIST statement may return idle connections.
  • If you execute multi-statements or stored procedures, read/write splitting is disabled and all of the subsequent requests over the current connection are routed to the primary instance. To enable read/write splitting again, you must terminate the current connection and establish a new one.
  • The /*FORCE_MASTER*/ and /*FORCE_SLAVE*/ hints are supported. However, requests that contain hints have higher route priorities. These requests are not constrained by consistency or transaction limits. You must check whether these hints are suitable for your business before you use them. A hint cannot contain statements that change environment variables. An example is /*FORCE_SLAVE*/ set names utf8; . Otherwise, an error may occur in the subsequent procedure.

Prerequisites

Before you enable read/write splitting for an RDS instance, make sure that the following requirements are met:

  • The RDS instance is a primary instance. You cannot enable read/write splitting for a read-only or disaster recovery instance.
  • The dedicated proxy feature is enabled for the RDS instance.
  • The RDS instance is attached with read-only instances.

Enable read/write splitting

  1. Log on to the ApsaraDB for RDS console.
  2. In the top navigation bar, select the region where the target RDS instance resides.Select a region
  3. Find the target RDS instance and click its 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.
    Parameter Description
    Latency Threshold The maximum latency that is allowed for data replication from the primary instance to its read-only instances. If the replication latency for a read-only instance exceeds the specified threshold, the read/write splitting module stops routing read requests to the read-only instance. This applies even if the read-only instance has a high read weight.

    Valid values: 0 to 7200. Unit: seconds. The read-only instances may replicate data from the primary instance at a certain 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 instance in your database system. A higher read weight indicates more read requests to process. For example, the primary instance is attached with three read-only instances, and the read weights of the primary and read-only instances are 0, 100, 200, and 200, respectively. In this situation, the primary instance only processes write requests, and the three read-only instances process all of the read requests at the 1:2:2 ratio.
    • Automatic Distribution: Your database system assigns a read weight to each instance based on the instance specifications. After you create a read-only instance, your database system assigns a read weight to the read-only instance and adds the read-only instance to the read/write splitting link. For more information, see Rules of weight allocation by the system.
    • Customized Distribution: You must manually specify the read weight of each instance. Valid values: 0 to 10000. After you create a read-only instance, its read weight defaults to 0. You must manually specify the read weight of the read-only instance.
    Note You cannot specify the read weight of a read-only instance for which you have specified a replication latency. For more information, see Set a replication delay for an RDS MySQL read-only instance.
    Configure read weights
  7. Click OK.

After opening, you need to configure the read/write splitting endpoint (proxy endpoint) in the application program to automatically forward the write request to the primary instance and the read request to each read-only instance.

FAQ

Does read/write splitting support hints?

Yes, you can use hints to specify to forward requests to your primary 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 for read/write splitting