Best Practices for Database Proxy

Using Hints for Read/Write Splitting

In addition to the weight distribution system of read/write splitting, hints are a useful type of SQL syntax that allows you to specify whether a SQL statement is executed on the primary DB instance or on a read replica.

• Hints are only used as routing suggestions. In non-read-only SQL and non-transaction scenarios, SQL statements cannot be routed to read replicas.
• If you connect to an instance using a MySQL CLI and want to run HINT in the CLI, add the -c option in the statement.

Hints supported by read/write splitting are as follows:

/*FORCE_MASTER*/: A SQL statement is routed to the primary DB instance.

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

For example, 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 DB instance.

/*FORCE_MASTER*/ only works for read/write addresses. If your primary DB instance is read-only, adding /*FORCE_MASTER*/ will not help route the SQL statement 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.