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.

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;
   

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