Read/Write separation

Introduction

In read/write separation, after data is modified on the primary server, the modification will be synchronized to the standby server. The standby server can only provide data read but not data write. Read/write separation improves database performance and server security while achieving backup.

For a standalone database such as MySQL, read/write separation refers to sending write requests to the primary server and read requests to the standby server. For OceanBase Database, we refine read/write separation from the server level to the partition level, which is a unique advantage of OceanBase Database.

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

Configure read/write separation

To use read/write separation, perform the following steps:

• 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 configuration.

• Configure the routing strategy. For example, for Partition 1 in the following figure, the three replicas, including the leader, can provide weak-consistency read services. You can configure 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. Example:

select /*+READ_CONSISTENCY(WEAK)*/ * from t1;

Configure weak-consistency read by using a parameter

To configure weak-consistency read by using a hint, you must modify the SQL statements, which can be complex sometimes. Alternatively, you can change the value of the obproxy_read_consistency parameter to configure weak-consistency read. To do this, perform the following steps:

1. Log on to the database by using the administrator account (root@proxysys) of OceanBase Database Proxy (ODP).

2. Run the following statement:

   alter proxyconfig set obproxy_read_consistency = 1;
   

   The values of the obproxy_read_consistency parameter are as follows:

   • 0 (default value): to enable strong-consistency read. A strong-consistency read request reads the latest data.

   • 1: to enable weak-consistency read. A weak-consistency read request does not need to read the latest data.

Configure the routing strategy

Configure the routing strategy through the proxy_route_policy parameter. The values of this parameter are as follows:

• follower_first: Weak-consistency read requests are preferentially routed to followers. If all the followers are unavailable, the requests are routed to the leader.

• follower_only: Weak-consistency read requests are routed to the followers. If all the followers are unavailable, the connection to the client is terminated.