This topic introduces the read/write splitting function. This function enables RDS to distribute read and write requests through a read/write splitting address.
When read/write splitting is enabled, three types of connection addresses are available in the instances:
- The connection address of the master instance: The Internet and intranet IP addresses can coexist.
- The connection address of the read-only instance: The Internet and intranet IP addresses can coexist.
- The read/write splitting address: The Internet and intranet IP addresses cannot coexist. An intranet IP address is generated by default. If you must use the read/write splitting Internet IP address, you can switch the IP address. For more information, see Change the network type of the read/write splitting address for an RDS for MySQL instance.
The master instance and read-only instance require independent connection addresses. Currently, instance connection addresses are configured automatically in applications to split read and write operations.
With this function, an extra read/write splitting address is provided to associate the master instance with all its read-only instances, achieving an automatic read/write splitting link. Applications can perform read and write operations with a single connection address. Write requests are routed automatically to the master instance and read requests are routed to each read-only instance by user-defined weights. You can scale up the processing capacity of the system by adding more read-only instances without any change to applications.
The following figure shows different types of connection addresses.
- Facilitates maintenance with a single read/write splitting address.
The master and read-only instances each have an independent connection address. You must configure each of these addresses in your application so that write requests are sent to the master instance and read requests to the read-only instances.
The read/write splitting function provides an additional address called read/write splitting address. You can connect to this address to perform read and write operations on the master and read-only instances, with read and write requests automatically distributed. Therefore, maintenance costs are reduced.
Additionally, you can increase the processing capability of your DB system by adding read-only instances without making any changes to your application.
- Improves performance with support for the highly secure link.
For users who build a proxy layer to implement read/write splitting on the cloud, data has to go through multiple components for statement parsing and forwarding before it reaches the database, significantly increasing the response latency. RDS read/write splitting can be directly set in the existing highly secure link without time consumption by any other components, which reduces the latency and improves the processing rate.
- Applies to various scenarios with customizable weights and thresholds.
RDS read/write splitting can be used to set read request weights for master and read-only instances and latency thresholds for read-only instances.
- Enhances database availability with instance health checks.
RDS read/write splitting performs health check automatically for all instances in the distribution system. If any instance fails or its latency exceeds the threshold, RDS automatically removes the instance out of the distribution system (while marking it as unavailable and stopping allocating read requests to it) and allocates read and write requests to the remaining healthy instances by the predefined weights. In this way, applications still run properly even if any single-node read-only instance fails. After the instance resumes, RDS automatically reclaims it into the request distribution system.Note To prevent single node failures, we recommend that you create at least two read-only instances for each master instance if you are using read/write splitting.
- Reduces resource and maintenance costs with free services.
RDS provides free read/write splitting function for all users.
- Currently, the following commands or functions cannot be forwarded to a read-only
stmt prepare sqlcommand is automatically executed on the master instance.
stmt prepare commandcommand cannot be forwarded to a read-only instance before execution of
- The environment configuration variables
set user, and
set onceare automatically executed on the master instance.
- The following commands or functions are not supported currently:
- SSL encryption
- Compression protocols
- com_dump_table and com_change_user protocols
kill connection [query]
- The execution result is random for the following commands:
show master status, and
com_process_infocommands return results according to the instance connected during execution.
- All transactions are routed to the master database.
- Read/write splitting does not guarantee consistency of non-transactional reads. If you require such consistency, add hints to route query requests to the master database or encapsulate query requests into transactions.
LAST_INSERT_ID()function is not supported. To use this function, add
hint：/*FORCE_MASTER*/, eg:/*FORCE_MASTER*/ SELECT LAST_INSERT_ID();to the request.