ApsaraDB MyBase for MySQL provides the read/write splitting feature. The feature automatically forwards read requests and write requests by using a Database Proxy endpoint and a Database Proxy terminal that can process read requests and write requests.
Background information
If ApsaraDB MyBase for MySQL receives a large number of read requests and a small number of write requests, your primary ApsaraDB MyBase for MySQL instance may fail to process read requests and your business may be affected in a negative manner. If you enable Database Proxy for ApsaraDB MyBase for MySQL, Database Proxy uses the read/write splitting feature to automatically forward write requests to your primary ApsaraDB MyBase for MySQL instance and read requests to the corresponding read-only ApsaraDB MyBase for MySQL instances. This way, the loads on the primary ApsaraDB MyBase for MySQL instance are reduced.
Benefits
A unified Database Proxy endpoint that ensures easy maintenance
If you enable the read/write splitting feature for ApsaraDB MyBase for MySQL, the feature provides a Database Proxy endpoint. After you connect to the endpoint, ApsaraDB MyBase for MySQL automatically forwards your write requests to your primary ApsaraDB MyBase for MySQL instance and your read requests to the corresponding read-only ApsaraDB MyBase for MySQL instances. This reduces maintenance costs.
To improve the read capabilities of your system, you need to only create more read-only ApsaraDB MyBase for MySQL instances. You do not need to modify your application.
A native high-security link that improves performance and reduces maintenance costs
If you build your proxy layer in the cloud to implement read/write splitting, data is parsed and forwarded by multiple components before the data reaches your database. As a result, the response latency increases. The read/write splitting feature provided by ApsaraDB MyBase for MySQL is integrated into the native ecosystem of ApsaraDB MyBase for MySQL. This helps reduce latency, decrease maintenance costs, and increase processing speed.
- Specify a read weight for each instance to control how read requests are distributed. Set a replication latency threshold so that lagging read-only instances are automatically excluded from the read pool.
Health checks for instances to ensure high availability
The read/write splitting module automatically performs health checks on your primary ApsaraDB MyBase for MySQL instance and the corresponding read-only ApsaraDB MyBase for MySQL instances. If a read-only ApsaraDB MyBase for MySQL instance does not respond or the latency of the instance exceeds the specified threshold value, the system stops forwarding read requests to the instance. In this case, the system forwards read requests and write requests to healthy instances. This helps ensure the service availability when a read-only ApsaraDB MyBase for MySQL instance fails. After the faulty read-only instance is recovered, ApsaraDB MyBase for MySQL resumes forwarding read requests to the instance.
Request routing
Primary RDS instance
Write statements: INSERT, UPDATE, DELETE, SELECT FOR UPDATE
All DDL operations (create or drop databases/tables, schema and permission changes)
All requests inside transactions (unless the transaction splitting feature is enabled)
Non-read-only transactions at Repeatable Read (RR) isolation level and higher
User-defined functions and stored procedures
Explicitly created temporary tables
SELECT last_insert_id()User variable queries and updates
KILLstatements in SQL (these are different from KILL commands),LOCK TABLE,UNLOCK TABLEFLUSHstatements includingFLUSH TABLES WITH READ LOCK(FTWRL)SAVEPOINT
Primary RDS instance or read-only RDS instance
SELECTstatements outside transactionsSTART TRANSACTION READ ONLY
All RDS instances
System variable updates
USEstatementsSHOW PROCESSLIST— the dedicated proxy aggregates and returns processes from all instancesNAMED PREPARECOM_CHANGE_USER,COM_QUIT,COM_SET_OPTION
Enable the read/write splitting feature
For information about how to enable the read /write splitting feature, see Enable dedicated proxy .