Updated on 2025-08-20 GMT+08:00

Best Practices for Database Proxy

Using Hints for Read/Write Splitting

In addition to the weight assignment rules of read/write splitting, hints serve as a complementary SQL syntax to specify whether an SQL statement is executed on a primary node or read replica.

  • Hints are only suggestions for routing and cannot force non-read-only SQL statements or statements within transactions to be routed to read replicas.
  • If you want to connect to a DB instance through the MySQL CLI and using hints, you must add the -c option to the command. Otherwise, the hint will be filtered out by the MySQL CLI.

Add hints at the beginning of SQL statements to specify where a statement will be routed.

  • If you add /*FORCE_MASTER*/, the statement will be forcibly routed to the primary node. /*FORCE_MASTER*/ works only for read/write addresses. If your primary instance is read-only, adding /*FORCE_MASTER*/ will not help route the statement to the primary node.
  • If you add /*FORCE_SLAVE*/, the statement will be forcibly routed to a read replica.

For example, the statement select * from table1 will be routed to a read replica by default. If you change it to /*FORCE_MASTER*/ select * from table1, it will be forcibly routed to the primary instance.

Connection Pool Configuration

To ensure that your application obtains an available connection from a connection pool, you need to configure how the connection pool will check connection availability. For example, set testOnBorrow to true for a JDBC or Druid connection pool or set connectionTestQuery to SELECT 1 for a HikariCP connection pool.

Read Requests Routed to the Primary DB Instance

  1. If a query statement is placed in a transaction, all transaction requests will be routed to the primary DB instance. If set autocommit=0 is configured before a query statement, the query statement will be treated as a transaction and routed to the primary DB instance.
  2. If no read replica exists, all read replicas are abnormal, or the read weights allocated to the read replicas are 0, queries will be routed to the primary DB instance. You can set read weights allocated to read replicas and the primary DB instance after read/write splitting is enabled. For details, see Configuring the Delay Threshold and Routing Policy.
  3. If multiple statements (for example, insert ***;select ***) are executed, all subsequent requests will be routed to the primary DB instance. To restore read/write splitting, disconnect the connection from your applications and then reconnect.
  4. Read operations with locks (for example, SELECT for UPDATE) will be routed to the primary DB instance.
  5. When the /*FORCE_MASTER*/ hint is used, requests will be routed to the primary DB instance.