Read/Write splitting

In read/write splitting, after data is modified on the primary server, the modification will be synchronized to the standby server. The standby server provides only data reads and no data writes. This optimizes the database performance and improves server security while backing up the database. In read/write splitting for a standalone database such as a MySQL database, write requests are sent to the primary server and read requests are sent to the standby server. OceanBase Database uniquely refines read/write splitting from the server level to the partition level.

With the read/write splitting feature, read requests can be sent to followers to reduce the stress on leaders.

Configure read/write splitting

To use read/write splitting, you must:

1. 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.
2. 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 followers.

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 parameter obproxy_read_consistency to configure weak consistency read.

1. Log on to the database by using the administrator account of ODP.

2. Change the value of the obproxy_read_consistency parameter.

   alter proxyconfig set obproxy_read_consistency = 1;
   

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 followers.

• follower_first: Read requests are preferentially sent to followers. If all the followers are unavailable, the requests are sent to the leader.
• follower_only: Read requests are sent only to the followers. If all the followers are unavailable, the connection to the client is terminated. Select a routing strategy based on the business scenario.