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

Possible Causes

1. Read weight parameter

   This parameter specifies read weights distributed to the primary node and read replicas. It is only applied when there are read replicas.

   For example, if a DB instance contains one primary node and two read replicas and the read weights are set, respectively, to 1, 2, and 3 for the primary node and two read replicas, read requests are distributed to the primary node 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 Assigning Read Weights.

2. 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 node as a transaction.

3. Connection binding

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

4. Custom variables

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

5. Read operations with locks (for example, SELECT for UPDATE) will be routed to the primary node.
6. You can use hints to specify whether an SQL statement is routed to the primary node or read replicas
   • /*FORCE_MASTER*/: A SQL statement is executed on the primary node.
   • /*FORCE_SLAVE*/: A SQL statement is executed on read replicas.

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

7. Session consistency

   In a given session, read requests may be sent to the primary node.

   For more information, see Introducing Consistency Levels.