Configuring Transaction Splitting

Scenarios

In most cases, an RDS for MySQL proxy instance sends all requests in transactions to the primary DB instance to ensure transaction correctness. However, in some frameworks, all requests are encapsulated into transactions that are not automatically committed using set autocommit=0. This causes heavy loads on the primary DB instance.

How the Feature Works

Database proxies support transaction splitting. With this feature enabled, RDS can route the read requests prior to write operations in a transaction to read replicas, reducing the pressure of the primary DB instance.

Transaction splitting is disabled by default. If it is enabled under the default READ COMMITTED isolation level, RDS for MySQL only starts a transaction for write operations when automatic commit is disabled. Before the transaction starts, read requests are routed to read replicas through load balancers.

• Transaction splitting disabled

  When transaction splitting is disabled, all requests in a transaction are routed to the primary node to guarantee read/write consistency.

  

• Transaction splitting enabled

  After this function is enabled, the database proxy distributes all read requests before the first write in a transaction to read replicas. This reduces the load on the primary node.

  

Precautions

• Enabling transaction splitting affects global consistency of certain workloads. Before enabling this feature, evaluate its impact on your workloads.
• All proxies of your DB instance must be in the Available state.
• Before enabling transaction splitting, you need to upgrade the database proxy to the latest version because the transaction processing logic has been optimized in the latest version.
• After transaction splitting is enabled, read requests of the transactions committed using BEGIN cannot be routed to read replicas.

Configuring Transaction Splitting

1. Click in the upper left corner and select a region.
2. Click in the upper left corner of the page and choose Databases > Relational Database Service.
3. On the Instances page, click the primary instance name.
4. In the navigation pane on the left, choose Database Proxy.
5. On the displayed page, click next to Transaction Splitting.
6. In the displayed dialog box, click OK.