Primary zone-based routing for strong-consistency reads

An OceanBase cluster has multiple zones. The primary zone contains multiple zones in the cluster. OceanBase Database Proxy (ODP) detects the locations of replicas contained in the primary zone and routes strong-consistency read requests for which partition locations cannot be calculated to these replicas.

ODP allows you to configure the primary zone in the following ways:

• You can configure the primary zone by specifying the proxy_primary_zone_name parameter in ODP. ODP will add the OBServer nodes specified in the proxy_primary_zone_name parameter to the candidate list. When a read request is initiated, ODP selects an OBServer node in the candidate list based on the priorities indicated in the routing strategy.

• Configure the primary zone for an OceanBase cluster and set the ODP parameter enable_primary_zone to True. ODP will add the OBServer nodes in the primary zone to a candidate list named PZ. When a read request is initiated, ODP selects an OBServer node in the PZ list and routes the request to the selected node. If no OBServer node is available in the list, primary zone-based routing fails.

Example 1: Configure the primary zone by using the proxy_primary_zone_name parameter

This example takes the following OBServer nodes and zones as an example.

1. Log on to ODP as the root@proxysys user and modify the proxy_primary_zone_name parameter.

   obclient [(none)]> ALTER PROXYCONFIG SET PROXY_PRIMARY_ZONE_NAME = 'z2';
   

2. Initiate a strong-consistency read in a user tenant and run the EXPLAIN ROUTE command to view the routing process in ODP.

   obclient [test]> EXPLAIN ROUTE SELECT /* +READ_CONSISTENCY(STRONG) */ 888\G
   

   The following return result shows that ODP routes the request to the z2 replica.

   *************************** 1. row ***************************
   Route Plan:
   Trans Current Query:"EXPLAIN ROUTE SELECT /* +READ_CONSISTENCY(STRONG) */ 888"
   
   Route Prompts
   -----------------
   > SQL_PARSE
     [INFO] Maybe counldn't get location cache of the query table since table name is empty.
   > ROUTE_INFO
     [INFO] Will route to partition server or routed by route policy
   > ROUTE_POLICY
     [INFO] Will route to the proxy primary zone(z2)
     [INFO] Use default route policy(MERGE_IDC_ORDER) for strong read
   
   
   Route Plan
   -----------------
   > SQL_PARSE:{cmd:"COM_QUERY", table:""}
   > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"}
   > ROUTE_POLICY:{replica:"10.10.10.2:50110", idc_type:"SAME_IDC", zone_type:"ReadWrite", role:"FOLLOWER", type:"FULL", chosen_route_type:"ROUTE_TYPE_PRIMARY_ZONE", proxy_primary_zone:"z2"}
   > CONGESTION_CONTROL:{svr_addr:"10.10.10.2:50110"}
   

Example 2: Use the primary zone of a cluster

1. Query the primary zone of an OceanBase cluster.

   obclient [test]> SELECT TENANT_NAME, PRIMARY_ZONE FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME='mysql'\G
   

   The following return result shows that z3 has a higher priority than z1, and z2 in the OceanBase cluster.

   *************************** 1. row ***************************
   TENANT_NAME: mysql
   PRIMARY_ZONE: z3;z1,z2
   

2. Initiate a strong-consistency read and run the EXPLAIN ROUTE command to view the routing process in ODP.

   obclient [test]> EXPLAIN ROUTE SELECT /* +READ_CONSISTENCY(STRONG) */ 888\G
   

   The following return result shows that ODP routes the request to the z3 replica.

   *************************** 1. row ***************************
   Route Plan:
   Trans Current Query:"EXPLAIN ROUTE SELECT /* +READ_CONSISTENCY(STRONG) */ 888"
   
   Route Prompts
   -----------------
   > SQL_PARSE
     [INFO] Maybe counldn't get location cache of the query table since table name is empty.
   > ROUTE_INFO
     [INFO] Will route to partition server or routed by route policy
   > ROUTE_POLICY
     [INFO] Will route to the cluster primary zone(z3;z1,z2)
     [INFO] Use default route policy(MERGE_IDC_ORDER) for strong read
   
   
   Route Plan
   -----------------
   > SQL_PARSE:{cmd:"COM_QUERY", table:""}
   > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"}
   > ROUTE_POLICY:{replica:"10.10.10.3:50111", idc_type:"SAME_IDC", zone_type:"ReadWrite", role:"FOLLOWER", type:"FULL", chosen_route_type:"ROUTE_TYPE_PRIMARY_ZONE", cluster_primary_zone:"z3;z1,z2"}
   > CONGESTION_CONTROL:{svr_addr:"10.10.10.3:50111"}