Read/write splitting

OceanBase Database supports read/write splitting, which requires the client to forward write requests to the ReadWrite zone, and weak consistency read requests to the ReadOnly zone. Read/write splitting depends on the logical data center (LDC) feature of OBProxy.

To configure read/write splitting, perform the following four steps:

1. Complete LDC settings for the OceanBase cluster.

2. Complete LDC settings for OBProxy.

3. Complete cluster settings for OBProxy.

4. Configure weak consistency read for applications.

Complete LDC settings for the OceanBase cluster.

1. Log on to the sys tenant as the root user from a client.

2. Then, execute the following statements to configure LDC. As a general rule, set region to the city name. This parameter is case-sensitive. The idc parameter represents the physical data center of the zone. As a general convention, set this parameter to the lower-case data center name. The zone parameter indicates the zone name. An OceanBase cluster has several regions. A region has several zones. A zone has a zone type attribute and an IDC attribute.

   obclient> ALTER SYSTEM MODIFY zone "z1" set region = "SHANGHAI"; 
   obclient> ALTER SYSTEM MODIFY zone "z1" set zone_type = "ReadWrite";
   obclient> ALTER SYSTEM MODIFY "z1" set idc = "zue";
   

3. You can execute the following statement to verify whether the LDC settings have taken effect for the OBServer. Verify if the value of Region Zone Type and IDC in the returned result are correct.

   obclient> SELECT * FROM __all_zone;
   

Configure LDC settings for OBProxy

When you configure read/writing splitting, you only need to specify the correct LDC in OBProxy. You can configure an LDC for OBProxy in one of the two following ways. In this example, "zue" is the data center name.

1. Set the LDC parameters when the OBProxy process is started. Sample statement:

   cd /opt/taobao/install/obproxy-1.5.5
   ./bin/obproxy -o proxy_idc_name=zue
   

2. Execute the ALTER statement in OceanBase Client (OBClient). Sample statement:

   obclient> ALTER PROXYCONFIG SET proxy_idc_name='zue';
   

   In this example, after you configure LDC for OBProxy, if an application is deployed in the zue IDC in Shanghai, after an application sends a weak consistency read request, the request is sent to the IDC in Shanghai.

   You can execute the following statement to verify whether the LDC settings have taken effect for the OBProxy.

   obclient> SHOW PROXYINFO IDC;
   

   Sample response:

   

Configure cluster settings

Log on to the sys tenant as the root user from a client tool that OceanBase supports. Execute the following statement to set the ob_route_policy the global variable. The default value of this variable is readonly_zone_first.

obclient> SET @@global.ob_route_policy=readonly_zone_first;

Configure weak consistency read for applications

Read/write splitting only applies to weak consistency read scenarios. You can configure weak consistency read for applications in one of the following three ways:

• Use hints in the SQL statement to configure it. Sample statement:

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

• Specify a session variable to configure it. A session variable only applies to the current session. Sample statement:

  obclient> SET @@ob_read_consistency='weak';
  

• Specify a global variable to configure it. A global variable applies to all the connections to the tenant. Sample statement:

  obclient> SET @@global.ob_read_consistency='weak';