In read/write splitting, after data is modified on the primary server, the modification is synchronized to the standby server. The standby server provides only data reads and no data writes. This optimizes database performance and improves server security while backing up the database. In read/write splitting for a standalone database such as MySQL, write requests are sent to the primary server and read requests are sent to the standby server. For OceanBase AI Database, we have implemented read/write splitting from the server level down to the table partition level, which is a unique advantage of OceanBase's distributed database.
With the read/write splitting feature, read requests can be sent to standby replicas to reduce the stress on the primary replica.
Notice
After read/write splitting is enabled, read requests may not read the latest data, and the latency is about hundreds of milliseconds.
Configure read/write splitting
To use read/write splitting, you must:
- Configure weak consistency read for SQL statements. A weak consistency read request does not need to read the latest data. Strong consistency read is enabled for general SQL statements, and weak consistency read requires additional configurations.
- Modify the routing strategy. For example, for partition1 in the following figure, the three replicas, including the leader, can provide weak consistency read services. You can modify the routing strategy so that read requests are preferentially routed to the standby replicas.
Configure weak-consistency read
Configure weak-consistency read by using a hint
You can add the /*+READ_CONSISTENCY(WEAK)*/ hint to SQL statements to enable weak consistency read for the statements.
select /*+READ_CONSISTENCY(WEAK)*/ * from t1;
Configure weak-consistency read by using a parameter
To configure weak consistency read for SQL statements by using a hint, you must modify the SQL statements, which can be complex sometimes. You can change the value of the obproxy_read_consistency parameter to configure weak consistency read.
Log in to the database by using the OBProxy administrator account.
Change the value of the
obproxy_read_consistencyparameter.alter proxyconfig set obproxy_read_consistency = 1;
Note
The valid values of this parameter are 0 and 1. The default value is 0, which specifies to enable strong consistency read. A strong consistency read request reads the latest data. The value 1 specifies to enable weak consistency read.
Modify the routing strategy
ODP allows you to modify the routing strategy by using the proxy_route_policy parameter. You can set the value to follower_first or follower_only so that weak consistency read requests are preferentially sent to standby replicas. The meanings of these two settings are as follows:
follower_first: Read requests are preferentially sent to standby replicas. If no standby replicas are available, the requests are sent to the primary replica.follower_only: Read requests are sent only to standby replicas. If no standby replicas are available, the connection to the client is terminated.
You must select one of the preceding two strategies based on your business scenario.