A Large Number of SELECT Requests Routed to The Primary Node 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 node. If set autocommit=0 is added before a query statement, 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. Adding a hint to specify the direction that a SQL statement will be routed

   In addition to the weight distribution of read/write splitting, you can add a hint at the beginning of a SQL statement for forcible routing.

   • If you add /*FORCE_MASTER*/, the statement will be forcibly routed to the primary node.
   • If you add /*FORCE_SLAVE*/, the statement will be forcibly routed to a read replica.
7. Session consistency

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

   For more information, see Consistency Levels.