A Large Number of SELECT Requests Routed to The Primary Instance After Database Proxy Is Enabled

Possible Causes

1. Delay threshold parameter

   This parameter specifies the maximum delay for data to be synchronized from the primary instance to read replicas. It is only applied when there are read replicas. To prevent long-time data inconsistency between the primary instance and read replicas, when the delay of a read replica exceeds the preset threshold, read requests are not forwarded to the read replica regardless of the read weight distributed to it.

   For more information, see Configuring Delay Threshold and Distributing Read Weight.

2. Read weight parameter

   This parameter specifies read weights distributed to the primary instance and read replicas. It takes effect only when there are read replicas.

   For example, if a primary instance has two read replicas and the read weights are set to 1, 2, and 3 for the primary instance and two read replicas, respectively, read requests are distributed to the primary instance and read replicas based on the ratio of 1:2:3. If the read weights are set to 0, 2, and 3, respectively, read requests are distributed to only the read replicas based on the ratio of 2:3.

   For more information, see Configuring Delay Threshold and Distributing Read Weight.

3. Transactions

   SQL statements in a transaction are sent to the primary instance. If set autocommit=0 is configured before a query statement is executed, the query statement is routed to the primary instance as a transaction.

4. Connection binding

   If multi-statements (for example, insert xxx;select xxx) are executed, all subsequent requests will be routed to the primary instance because the SQL statement for creating temporary tables binds the connection to the primary instance. To restore read/write splitting, disconnect your application from the RDS instance and connect to the instance again.

5. Custom variables

   SQL statements containing custom variables will be routed to the primary instance.

6. Read operations with locks (for example, SELECT for UPDATE) will be routed to the primary instance.
7. Using hints to specify whether an SQL statement is routed to the primary instance or read replica

   In addition to weight distribution rules, you can add one of the following hints before an SQL statement for forcible routing:

   /*FORCE_MASTER*/: The SQL statement is forcibly routed to the primary instance.

   /*FORCE_SLAVE*/: The SQL statement is forcibly routed to a read replica.

   Hints are only used as routing suggestions. In non-read-only SQL and transaction scenarios, SQL statements cannot be routed to read replicas.