Best practices for read-write splitting-OceanBase

To alleviate the read and write workloads on enterprise databases and minimize their mutual impact, OceanBase Database offers read-write splitting capabilities. By handling read and write operations separately, OceanBase Database enhances system response times and helps enterprises build efficient and stable database systems. This topic explains how to configure read-write splitting in OceanBase Database using the weak-consistency read strategy.

Best practices for read-write splitting in OceanBase Database V4.X

Weak-consistency read

A distributed database system supports multiple consistency read strategies thanks to its multi-replica architecture. The strong-consistency read strategy ensures the linearity of read operations, allowing the latest data to be read every time. In contrast, the weak-consistency read strategy does not guarantee that the latest data is always read.

OceanBase Database is a distributed database system built on the Multi-Paxos protocol, where data is stored in multiple replicas across different zones or nodes. During data updates, the leader replica executes the modification statements and synchronizes the commit logs (clogs) to follower replicas. A transaction is considered successfully committed once the majority of replicas, including the leader, have persisted the logs to disk. Logs of other replicas are also persisted to ensure disaster recovery. However, the update and replay for followers may not be completed immediately after the majority of replicas have their logs persisted to disk. As a result, some replicas may be out-of-sync with the leader.

OceanBase Database provides two consistency levels: STRONG and WEAK. In a strong-consistency read operation, requests are routed to the leader to access the latest data. In a weak-consistency read operation, requests are preferentially routed to followers, even if the latest data is not required. Write operations in OceanBase Database are always strongly consistent, handled exclusively by the leader. By default, read operations are also strongly consistent and provided by the leader. However, you can specify weak-consistency reads, in which case the followers preferentially provide read services.

In scenarios where the business is insensitive to data timeliness, OceanBase Database writes data to the leader and reads data from a follower, making full use of resources and improving the database read efficiency. In these scenarios, data is read from a follower in weak consistency mode. You can use the tenant-level parameter max_stale_time_for_weak_consistency to specify the maximum lag time for weak-consistency reads, known as bounded staleness consistency reads. By default, this period is set to 5 seconds. If a replica falls behind the latest data by more than this specified time, it becomes unreadable, and the retry mechanism will attempt other valid replicas. If no valid replicas are found, the retry mechanism will keep retrying until the statement times out.

Different weak-consistency read requests are routed to different replicas. Even in bounded staleness consistency mode, the staleness of data read by different requests can vary. To address this, OceanBase Database offers a monotonic read feature. You can use the tenant-level parameter enable_monotonic_weak_read to enable this feature. When enabled, once a process obtains a value of a data object, subsequent accesses will not return values from earlier versions. This ensures the monotonicity of data versions in weak-consistency read mode.

Read-write splitting within a cluster

In an OceanBase cluster, SQL read and write requests are routed to the leader by default. To alleviate the leaders' workload and make full use of resources, you can deploy OceanBase Database Proxy (ODP) in a multi-replica cluster. This setup routes strong-consistency read and write requests to the leader, while weak-consistency read requests are handled by nearby followers.

A business system can initiate both strong-consistency and weak-consistency read requests to the database. To implement read-write splitting within a cluster, you need to configure the system to distinguish between these two types of SQL requests. This means specifying weak-consistency reads at the SQL level. Generally, you can configure the following settings in an application:

Apply the weak-consistency read strategy to read-only SQL read requests.
Apply the weak-consistency read strategy to all SQL read requests in the current session.

Additionally, ODP allows you to specify the destination replicas for weak-consistency read requests based on their physical locations and types.

Configure parameters of weak-consistency reads

Before configuring weak-consistency reads, you need to set the following parameters based on your business environment.

Parameter	Default value	Scope	Description	Suggestion and impact
enable_monotonic_weak_read	False	Tenant	Specifies whether to enable monotonic reads.	Modify this parameter based on your business scenario.
max_stale_time_for_weak_consistency	5s	Tenant	The maximum lag time of weak-consistency reads.	We recommend that you use the default value.
weak_read_version_refresh_interval	50 ms	Cluster	The interval for refreshing the weak-consistency read version.	We recommend that you use the default value. The value `0` specifies to disable monotonic reads. If the value is greater than that of the `max_stale_time_for_weak_consistency` parameter, weak-consistency reads are not supported due to excessively long lag time of the follower.

Select a routing strategy for read-write splitting

You can configure either of the following routing strategies for weak-consistency read requests from applications to an OceanBase cluster:

Load-balanced routing: Weak-consistency read requests are evenly distributed among the leader and followers in a load-balanced manner.
Follower-first read: Weak-consistency read requests from the client are preferentially routed to nearby followers.

Load-balanced routing

This is the default routing strategy, which takes effect automatically when you configure weak-consistency reads for your application. You only need to specify weak-consistency reads in your SQL statements, with no additional configuration required. This strategy is suitable for clusters with standard deployments.

You can specify the weak-consistency read strategy for SQL statements by using one of the following methods:

Use a hint in an SQL statement.

The setting applies only to the SQL statement. Here is an example:
```
obclient> SELECT /*+read_consistency(weak)*/ * FROM t1;
```
Use a session-level variable.

The setting applies only to the current session. Here is an example:
```
obclient> SET ob_read_consistency='weak';
```
Use a global variable.

The setting applies to all connections to the tenant. Here is an example:
```
obclient> SET GLOBAL ob_read_consistency='weak';
```

Note

Since applications often perform both strong-consistency and weak-consistency read requests, it is generally recommended to use the first two methods (hints in SQL statements or session-level variables) to specify weak-consistency reads.

Follower-first read

You can use the ODP parameter proxy_route_policy or the user variable proxy_route_policy to specify this strategy. To do so, follow these steps:

Configure logical data center (LDC) settings, including REGION and IDC, for all zones in the cluster.

If you do not specify REGION or IDC when you deploy an OceanBase cluster, the default value default_region of REGION is used, and IDC is left empty. You can specify REGION and IDC for a zone as follows:
1. Log in to the sys tenant of the cluster as the root user.
2. Execute the ALTER SYSTEM MODIFY ZONE statement to modify REGION and IDC for the zone.
  
  In the following example, REGION specifies the city name (case-sensitive), and IDC specifies the data center of the zone. Generally, set IDC to the data center name in lowercase. z1 specifies the zone name. An OceanBase cluster can have multiple regions, each with multiple zones, and each zone has an IDC attribute.
```
obclient> ALTER SYSTEM MODIFY ZONE z1 SET REGION= 'SHANGHAI';
```
```
obclient> ALTER SYSTEM MODIFY ZONE z1 SET IDC = 'zue';
```
3. Verify the settings.
```
obclient> SELECT * FROM oceanbase.DBA_OB_ZONES;
```
Configure LDC settings for ODP.
1. Use ODP to log in to the sys tenant of the cluster as the root user.
2. Execute the ALTER PROXYCONFIG statement to modify IDC for ODP.
  
  In the following example, proxy_idc_name specifies the name of the IDC where ODP is deployed. It helps determine whether ODP is in the same IDC as the OBServer node. This allows you to select a routing strategy based on the routing rule, directing requests to the ODP node in the same IDC.
```
obclient> ALTER PROXYCONFIG SET proxy_idc_name='zue';
```
3. Verify the settings.
```
obclient> SHOW PROXYINFO IDC;
```
Configure an ODP routing strategy for weak-consistency read requests.
1. Use ODP to log in to the sys tenant of the cluster as the root user.
2. Configure a routing strategy for ODP.
  
  You can configure the routing strategy using the ODP parameter proxy_route_policy or the user variable proxy_route_policy. The routing options are as follows:
  - follower_first: This is the default setting, which prioritizes routing weak-consistency read requests to follower replicas, even if they are undergoing major compactions. If no follower replicas are available, the requests are routed to the leader replica.
  - follower_only: This setting routes weak-consistency read requests exclusively to follower replicas. If no follower replicas are available, the connection to the client will be terminated.
  Here are some examples:
  - Use the ODP parameter proxy_route_policy to prioritize routing weak-consistency read requests to the follower replica in the same IDC.
    
    This setting applies to all sessions.
```
obclient> ALTER PROXYCONFIG SET proxy_route_policy='follower_first';
```
  - Use the user variable proxy_route_policy to prioritize routing weak-consistency read requests to the follower replica in the same IDC.
    
    This setting needs to be configured in each session.
```
obclient> SET @proxy_route_policy='follower_first';
```
3. Verify the settings.
```
obclient> SHOW PROXYSESSION VARIABLES;
```
Configure the weak-consistency read strategy for SQL statements.
1. Use ODP to log in to the cluster from a user tenant.
2. Specify the weak-consistency read strategy for SQL statements.
  - Use a hint in an SQL statement.
    
    The setting applies only to the SQL statement. Here is an example:
```
obclient> SELECT /*+read_consistency(weak)*/ * FROM t1;
```
  - Use a session-level variable.
    
    The setting applies only to the current session. Here is an example:
```
obclient> SET ob_read_consistency='weak';
```
  - Use a global variable.
    
    The setting applies to all connections to the tenant. Here is an example:
```
obclient> SET GLOBAL ob_read_consistency='weak';
```
Note

Since applications often perform both strong-consistency and weak-consistency read requests, it is generally recommended to use the first two methods (hints in SQL statements or session-level variables) to specify weak-consistency reads.

Follower latency threshold

In a read-write splitting scenario, ODP preferentially forwards weak-consistency read requests to a follower. As the data saved by a follower lags behind the latest data of the leader, a weak-consistency read request may not read the latest data from a follower. To address this, ODP and OceanBase Database provide the follower latency threshold feature, allowing you to specify the maximum latency allowed for data read by weak-consistency read requests. For more information, see Follower latency threshold.

Configure the weak-consistency read strategy for drivers

OceanBase Connector/J

OceanBase Connector/J (JDBC) allows you to enable weak-consistency reads by adding sessionVariables=ob_read_consistency=weak to the URL string. This means you can specify weak-consistency reads by setting a session-level variable in JDBC, and this setting will only apply to the current session. If you want all requests made through this JDBC connection to use weak-consistency reads, you can set this parameter. Here is an example:

jdbc:oceanbase://172.xx.xx.xx:2881/test?useSSL=false&useServerPrepStmts=true&sessionVariables=ob_read_consistency=weak

For more information about how to use OceanBase Connector/J to connect to OceanBase Database, see Build a Java application.

OceanBase Connector/ODBC

You can specify the weak-consistency read strategy for OceanBase Connector/ODBC by using SQL statements or database variables.

Verify the execution of weak-consistency reads of SQL statements

You can query the consistency_level column in the GV$OB_SQL_AUDIT view to check whether an SQL statement executed a weak-consistency read. Here is an example:

Create a table named tbl1.
```
obclient> CREATE TABLE tbl1(id int);
```

Insert data into the table and commit the transaction.

obclient> INSERT INTO tbl1 VALUES(100);

obclient> COMMIT;

Execute the following SQL statements.

obclient> SELECT * FROM tbl1;

obclient> SELECT /*+read_consistency(weak)*/ * FROM tbl1;

Query the GV$SQL_AUDIT view.

obclient> SELECT svr_ip,query_sql,consistency_level FROM oceanbase.GV$OB_SQL_AUDIT WHERE query_sql LIKE '%FROM tbl1';

A sample query result is as follows:

+----------------+------------------------------------------------+-------------------+
| svr_ip         | query_sql                                      | consistency_level |
+----------------+------------------------------------------------+-------------------+
| 172.xx.xx.34   | SELECT * FROM tbl1                             |                 3 |
| 172.xx.xx.35   | SELECT /*+read_consistency(weak)*/ * FROM tbl1 |                 2 |
+----------------+------------------------------------------------+-------------------+
2 rows in set

In the query result, the value 3 indicates a strong-consistency read, and the value 2 indicates a weak-consistency read.

Best practices for read-write splitting in OceanBase Database V3.X

Weak-consistency read

In scenarios where the business is insensitive to data timeliness, OceanBase Database writes data to the leader and reads data from a follower, making full use of resources and improving the database read efficiency. In these scenarios, data is read from a follower in weak consistency mode. You can use the tenant-level parameter max_stale_time_for_weak_consistency to specify the maximum lag time for weak-consistency reads, known as bounded staleness consistency reads. By default, this period is set to 5 seconds. If a standby cluster falls behind the latest data by more than this specified time, the read operation will retry and wait until it either times out, the standby cluster catches up with the primary cluster, or another replica (primary cluster) is chosen.

Read-write splitting within a cluster

Apply the weak-consistency read strategy to read-only SQL read requests.
Apply the weak-consistency read strategy to all SQL read requests in the current session.

Additionally, ODP allows you to specify the destination replicas for weak-consistency read requests based on their physical locations and types.

Configure parameters of weak-consistency reads

Before configuring weak-consistency reads, you need to set the following parameters based on your business environment.

Parameter	Default value	Scope	Description	Suggestion and impact
enable_monotonic_weak_read	False	Tenant	Specifies whether to enable monotonic reads.	Modify this parameter based on your business scenario.
max_stale_time_for_weak_consistency	5s	Tenant	The maximum lag time of weak-consistency reads.	We recommend that you use the default value.
weak_read_version_refresh_interval	50 ms	Cluster	The interval for refreshing the weak-consistency read version.	We recommend that you use the default value. The value `0` specifies to disable monotonic reads. If the value is greater than that of the `max_stale_time_for_weak_consistency` parameter, weak-consistency reads are not supported due to excessively long lag time of the follower.

Select a routing strategy for read-write splitting

You can configure either of the following routing strategies for weak-consistency read requests from applications to an OceanBase cluster:

Load-balanced routing: Weak-consistency read requests are evenly distributed among the leader and followers in a load-balanced manner.
Follower-first read: Weak-consistency read requests from the client are preferentially routed to nearby followers.

Load-balanced routing

You can specify the weak-consistency read strategy for SQL statements by using one of the following methods:

Use a hint in an SQL statement.

The setting applies only to the SQL statement. Here is an example:
```
obclient> SELECT /*+read_consistency(weak)*/ * FROM t1;
```
Use a session-level variable.

The setting applies only to the current session. Here is an example:
```
obclient> SET ob_read_consistency='weak';
```
Use a global variable.

The setting applies to all connections to the tenant. Here is an example:
```
obclient> SET GLOBAL ob_read_consistency='weak';
```

Note

Follower-first read

You can use the ODP parameter proxy_route_policy or the user variable proxy_route_policy to specify this strategy. To do so, follow these steps:

Configure LDC settings, including REGION and IDC, for all zones in the cluster.

If you do not specify REGION or IDC when you deploy an OceanBase cluster, the default value default_region of REGION is used, and IDC is left empty. You can specify REGION and IDC for a zone as follows:
1. Log in to the sys tenant of the cluster as the root user.
2. Execute the ALTER SYSTEM MODIFY ZONE statement to modify REGION and IDC for the zone.
  
  In the following example, REGION specifies the city name (case-sensitive), and IDC specifies the data center of the zone. Generally, set IDC to the data center name in lowercase. z1 specifies the zone name. An OceanBase cluster can have multiple regions, each with multiple zones, and each zone has an IDC attribute.
```
obclient> ALTER SYSTEM MODIFY ZONE z1 SET REGION= 'SHANGHAI';
```
```
obclient> ALTER SYSTEM MODIFY ZONE z1 SET IDC = 'zue';
```
3. Verify the settings.
```
obclient> SELECT * FROM oceanbase.__all_zone;
```
Configure LDC settings for ODP.
1. Use ODP to log in to the sys tenant of the cluster as the root user.
2. Execute the ALTER PROXYCONFIG statement to modify IDC for ODP.
  
  In the following example, proxy_idc_name specifies the name of the IDC where ODP is deployed. It helps determine whether ODP is in the same IDC as the OBServer node. This allows you to select a routing strategy based on the routing rule, directing requests to the ODP node in the same IDC.
```
obclient> ALTER PROXYCONFIG SET proxy_idc_name='zue';
```
3. Verify the settings.
```
obclient> SHOW PROXYINFO IDC;
```
Configure an ODP routing strategy for weak-consistency read requests.
1. Use ODP to log in to the sys tenant of the cluster as the root user.
2. Configure a routing strategy for ODP.
  
  You can configure the routing strategy using the ODP parameter proxy_route_policy or the user variable proxy_route_policy to specify the routing strategy. The routing options are as follows:
  - follower_first: This is the default setting, which prioritizes routing weak-consistency read requests to follower replicas, even if they are undergoing major compactions. If no follower replicas are available, the requests are routed to the leader replica.
  - follower_only: This setting routes weak-consistency read requests exclusively to follower replicas. If no follower replicas are available, the connection to the client will be terminated.
  Here are some examples:
  - Use the ODP parameter proxy_route_policy to prioritize routing weak-consistency read requests to the follower replica in the same IDC.
    
    The setting applies to all sessions.
```
obclient> ALTER PROXYCONFIG SET proxy_route_policy='follower_first';
```
  - Use the user variable proxy_route_policy to prioritize routing weak-consistency read requests to the follower replica in the same IDC.
    
    The setting applies only to the current session.
```
obclient> SET @proxy_route_policy='follower_first';
```
3. Verify the settings.
```
obclient> SHOW PROXYSESSION VARIABLES;
```
Configure the weak-consistency read strategy for SQL statements.
1. Use ODP to log in to the cluster from a user tenant.
2. Specify the weak-consistency read strategy for SQL statements.
  - Use a hint in an SQL statement.
    
    The setting applies only to the SQL statement. Here is an example:
```
obclient> SELECT /*+read_consistency(weak)*/ * FROM t1;
```
  - Use a session-level variable.
    
    The setting applies only to the current session. Here is an example:
```
obclient> SET ob_read_consistency='weak';
```
  - Use a global variable.
    
    The setting applies to all connections to the tenant. Here is an example:
```
obclient> SET GLOBAL ob_read_consistency='weak';
```
Note

Since applications often perform both strong-consistency and weak-consistency read requests, it is generally recommended to use the first two methods (hints in SQL statements or session-level variables) to specify weak-consistency reads.

Read-write splitting between primary and standby clusters

In conventional databases, many read-only report applications are insensitive to data timeliness. The database administrator (DBA) can route requests of such applications to a standby database for execution. This reduces the workload of the primary database while meeting the requirements of statistical queries. In the primary/standby architecture of OceanBase Database, you can use standby clusters to achieve the preceding purpose. You can also use ODP to route read requests to nearby replicas in the local IDC.

Configure the weak-consistency read strategy for a standby cluster

A standby cluster provides the weak-consistency read feature like a follower replica in the primary cluster. However, the load-balanced routing strategy for the leader and follower replicas is not applicable to a standby cluster because it contains only followers. You can configure the weak-consistency read strategy for a standby cluster in the following way:

Log in to the sys tenant of the standby cluster as the root user.
Set the tenant-level parameters enable_monotonic_weak_read and max_stale_time_for_weak_consistency to configure weak-consistency reads.
Specify the weak-consistency reads at the SQL level.
- Use a hint in an SQL statement.
  
  The setting applies only to the SQL statement. Here is an example:
```
obclient> SELECT /*+read_consistency(weak)*/ * FROM t1;
```
- Use a session-level variable.
  
  The setting applies only to the current session. Here is an example:
```
obclient> SET ob_read_consistency='weak';
```

Notice

A user tenant of a standby cluster supports only weak-consistency reads. If a strong-consistency read, write, or DDL operation is initiated for a user tenant, an error is returned.

Configure read-write splitting

Two read-write splitting modes are available:

Specify to read data from only a standby cluster: This mode applies to delay-insensitive statistical query applications. It makes full use of resources of a standby cluster to support decision-making based on a large amount of data.
Specify to write data to the primary cluster and read data from a standby cluster:

In this mode, read-write splitting is implemented by applications. ODP and OceanBase Database do not support this mode.

This section describes how to configure read-write splitting in the first mode.

Configuration of a standby cluster

Configure LDC settings, including REGION and IDC, for all zones in the standby cluster.

You can specify REGION and IDC for a zone as follows:
1. Log in to the sys tenant of the standby cluster as the root user.
2. Execute the ALTER SYSTEM MODIFY ZONE statement to modify REGION and IDC for the zone.
  
  In the following example, REGION specifies the city name (case-sensitive), and IDC specifies the data center of the zone. Generally, set IDC to the data center name in lowercase. z1 specifies the zone name. An OceanBase cluster can have multiple regions, each with multiple zones, and each zone has an IDC attribute.
```
obclient> ALTER SYSTEM MODIFY ZONE z1 SET REGION= 'SHANGHAI';
```
```
obclient> ALTER SYSTEM MODIFY ZONE z1 SET IDC = 'zue';
```
3. Verify the settings.
```
obclient> SELECT * FROM oceanbase.__all_zone;
```
Configure LDC settings for ODP.
1. Use ODP to log in to the sys tenant of the standby cluster as the root user.
2. Execute the ALTER PROXYCONFIG statement to modify IDC for ODP.
  
  In the following example, proxy_idc_name specifies the name of the IDC where ODP is deployed. It helps determine whether ODP is in the same IDC as the OBServer node. This allows you to select a routing strategy based on the routing rule, directing requests to the ODP node in the same IDC.
```
obclient> ALTER PROXYCONFIG SET proxy_idc_name='zue';
```
3. Verify the settings.
```
obclient> SHOW PROXYINFO IDC;
```
Set tenant-level parameters to control weak-consistency reads for the standby cluster.
1. Use ODP to log in to the sys tenant of the standby cluster as the root user.
2. Execute the following statements to specify the parameters.
```
obclient> ALTER SYSTEM SET enable_monotonic_weak_read = true TENANT=mysqltenant;
```
```
obclient> ALTER SYSTEM SET max_stale_time_for_weak_consistency ='10s' TENANT=mysqltenant;
```
  Note
  
  You can specify TENANT=all to apply the modification to all tenants in the standby cluster.
Configure the weak-consistency read strategy for applications.

You can specify the weak-consistency read strategy at the SQL level or for JDBC sessions. For more information about how to specify the weak-consistency read attribute in the connection string of a JDBC session, see the Configure the weak-consistency read strategy for drivers section.

To specify the weak-consistency read strategy at the SQL level, perform the following operations:
1. Use ODP to log in to the standby cluster from a user tenant.
2. Specify the weak-consistency read strategy for SQL statements.
  - Use a hint in an SQL statement.
    
    The setting applies only to the SQL statement. Here is an example:
```
obclient> SELECT /*+read_consistency(weak)*/ * FROM t1;
```
  - Use a session-level variable.
    
    The setting applies only to the current session. Here is an example:
```
obclient> SET ob_read_consistency='weak';
```

Configuration of ODP

The primary and standby clusters have the same cluster name. If you specified to start ODP based on ConfigUrl in OceanBase Cloud Platform (OCP), SQL requests will be routed to the primary cluster by default when you use ODP to connect to the database. You can configure ODP to access a standby cluster by using either of the following methods:

Method 1: Specify cluster_id of the standby cluster in the cluster name contained in the username for accessing the database. In other words, specify the username in the format of username@tenant_name#cluster_name:cluster_id. You can obtain cluster_id from the cluster list in the OCP console.

Here is an example:
```
obclient -h10.10.10.1 -u*****@obtenant#obdemo:xxxx -P2883 -p****** -c -A oceanbase
```
Method 2: Use OCP to configure an OBProxy that accesses a standby cluster by default. You can specify to start the OBProxy in RsList mode, and select the standby cluster as the accessible OceanBase cluster.

For more information, see Create an OBProxy cluster.

Configuration of applications

When you connect to the database from an application through a JDBC connection, if you want to specify the weak-consistency read strategy for the application without adding a hint to the application code, you can specify the weak-consistency read attribute in the JDBC connection string. For more information, see the Configure the weak-consistency read strategy for drivers section.