Read/Write splitting

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

Note

All zones of an OceanBase database are not consistent in real time. Therefore, weak consistency read is acceptable in read-only operations.

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

1. Configure LDC settings for your OceanBase cluster.

2. Configure LDC settings for ODP.

3. Configure cluster settings for ODP.

4. Configure weak consistency read for the application.

Step 1: Configure LDC settings for your OceanBase cluster

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

2. Execute the following statements to configure LDC settings. Generally, you need to set the region parameter to the city name, which is case-sensitive. The idc parameter specifies the physical IDC in the zone. Generally, you need to set this parameter to the IDC name in lowercase. The z1 parameter specifies 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 idc = 'zue';
   

3. Execute the following statement to check whether the LDC settings have taken effect for the OBServer node. To be specific, check whether the region and IDC of the corresponding zone in the returned result are as expected.

   obclient> SELECT * FROM oceanbase.__all_zone;
   

Step 2: Configure LDC settings for ODP

When you configure read/writing splitting, you need to configure LDC settings for ODP. You can configure LDC settings for ODP in one of the following ways. In this example, the sample zue IDC is used.

1. Set the LDC parameters when you start the obproxy process. This is the recommended method. Sample code:

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

2. Execute the ALTER statement on a support client tool to modify the ODP settings. Sample statement:

   obclient> ALTER PROXYCONFIG SET proxy_idc_name='zue';
   

   Assume that an application is deployed in the zue IDC in Shanghai. After the application sends a weak consistency read request, the request is sent to the zue IDC in Shanghai.

   You can execute the following statement to check whether the LDC settings have taken effect for ODP:

   obclient> SHOW PROXYINFO IDC;
   

   Sample response:

   

Step 3: Configure OceanBase cluster settings

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

obclient> SET @@global.ob_route_policy=readonly_zone_first;

Step 4: Configure weak consistency read for applications

When you configure read/writing splitting, applications decide whether to perform read-only operations with weak consistency. You can use one of the following three methods to specify weak consistency for read-only operations:

• Use a hint in an SQL statement. The hint takes effect only on this SQL statement. Sample statement:

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

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

  obclient> SET @@ob_read_consistency='weak';
  

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

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