This topic describes how to enable the read/write splitting function in the shared proxy of an ApsaraDB RDS for MySQL instance. This function allows your application to connect to your database system by using a read/write splitting endpoint. Your database system distributes write requests to the primary instance and read requests to the read-only instances attached to the primary instance based on the specified read weights.
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.
- The RDS instance is running one of the following MySQL versions and RDS editions:
- MySQL 5.7 in the High-availability Edition (with local SSDs)
- MySQL 5.6
- The RDS instance is attached with at least one read-only instance. For more information, see Create an ApsaraDB RDS for MySQL read-only instance.
- No replication latencies are configured for the read-only instances that are attached to the primary instance. Otherwise, read/write splitting cannot take effect. For more information, see Set a replication delay for an RDS MySQL read-only instance.
The read/write splitting function is free of charge. However, you still need to pay for the read-only instances that you create.
Limits (in shared proxy)
- If the length of an SQL statement exceeds 64 KB, the read/write splitting link does not parse the SQL statement and directly routes it to the primary instance. This applies even if you use a hint to specify to send the SQL statement to the primary instance. If you want to execute an SQL statement to query data from a read-only instance, make sure that the length of the SQL statement does not exceed 64 KB.
- Statements that are prepared by using the PREPARE statement in the SQL command line tool are executed on the primary instance.
- Statements that are prepared by using the PREPARE statement in the MySQL command line tool are forwarded to the read-only instances until the PREPARE statement is terminated.
SET USER, and
SET ONCEstatements that are used to configure environment variables are executed on the primary instance.
- The following statements and functions are not supported:
- SSL encryption
- com_dump_table and com_change_user
KILL CONNECTION [QUERY]
- The execution result is random for the following statements:
SHOW MASTER STATUS, and
COM_PROCESS_INFOstatements return results based on the instance to which they are routed.
- All requests in transactions are routed to the primary instance.
Note If an SQL statement in a transaction fails to be executed, the transaction may be rolled back. If the transaction is rolled back and the subsequent SELECT statement in it is routed to a read-only instance, the query result may be abnormal. We recommend that you resolve the issue in the failed SQL statement before you continue to execute the transaction.
- Read/write splitting does not ensure the read consistency of requests that are not encapsulated in transactions. If you require the read consistency, add hints that specify to route the requests to the primary instance or encapsulate the requests in transactions.
LAST_INSERT_ID()function is not supported. If you want to use this function in a request, add the following hint to the request:
hint: /*FORCE_MASTER*/, eg:/*FORCE_MASTER*/ SELECT LAST_INSERT_ID();.
- If it is the first time that you enable the read/write splitting function, your database system automatically upgrades the backend administration systems of the primary and read-only instances to the latest version. This ensures service availability. While the read/write splitting function is being enabled, the primary instance is disconnected for 30 seconds or less. All of the read-only instances are inaccessible during the brief disconnection. We recommend that you enable the read/write splitting function during off-peak hours and make sure that your application is configured to automatically reconnect to the primary instance.
- If you have restarted or changed the specifications of the primary and read-only instances at least once after March 8, 2017, your database system has upgraded. This includes the upgrade of the backend administration systems of these instances to the latest version. In this situation, when you enable the read/write splitting function, your database system does not restart these instances and no brief disconnection occurs.
- The endpoint that is generated for read/write splitting is fixed. If you enable and
disable the read/write splitting function multiple times, the endpoint remains unchanged.
You do not need to perform a regular update of the configuration data on your application.
This reduces maintenance costs.
Note The read/write splitting endpoint cannot be manually changed.
- Log on to the ApsaraDB for RDS console.
- In the top navigation bar, select the region where the target primary instance resides.
- Find the target primary instance and click its ID.
- In the left-side navigation pane, click Database Connection or Database Proxy.
- On the Read/Write Splitting tab, click Enable now.
- If the Enable now button does not appear, you must enable the database proxy feature and create at least one read-only instance.
- When you enable the read/write splitting function, it requires a restart of the primary and read-only instances. This applies when the instances were created before March 8, 2017 and you have not restarted them or changed their specifications. In such cases, you must click OK in the displayed dialog box to enable the read/write splitting function.
- Configure the following parameters.
Parameter Description Network Type The type of the read/write splitting endpoint.
- Intranet Address:
- If the network type of the primary instance is VPC, the network type of the read/write splitting endpoint is VPC.
- If the network type of the primary instance is classic network, the network type of the read/write splitting endpoint is classic network.
- Internet Address: used to connect to the primary instance over the Internet. The Internet may be prone to fluctuations in connectivity. We recommend that you connect to the primary instance by using an internal endpoint.
Latency Threshold The maximum latency that is allowed for the data replication from the primary instance to the read-only instances. If the replication latency of a read-only instance exceeds this threshold, the read/write splitting link no longer distributes read requests to the read-only instance. For example, it is not based on a high read weight of the read-only instance.
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 read-only instances process all of the read requests at the 1:2:2 ratio.
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.
- 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, the default value of the read weight is 0. You must manually specify the read weight of the read-only instance.
- Intranet Address:
- Click OK.
Note The status of the primary instance changes to Creating Network Connection. Wait until the status changes back to Running.