ODP(OBProxy)路由初探

OBProxy路由策略

Primary Zone 路由

官方声明默认情况，会将租户请求发送到租户的 primary zone 所在的机器上，通过 Primary Zone 路由可以尽量发往主副本，方便快速寻找 Leader 副本。另外，设置primary zone 也会在一定成都上减少 分布式执行计划 以及 远程执行计划 生成，对于SQL性能提升有一定的帮助

OBProxy 配置项 enable_primary_zone 控制是否启用Primary zone路由，默认是开启的

[root@server061 ~]# mysql -h10.0.0.61 -P2883 -uroot@sys#obdemo61 -paaAA11__
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7619
Server version: 5.6.25 OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec  4 2024 20:33:18)Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show proxyconfig like 'enable_primary_zone';
+---------------------+-------+------------------------------------------------------------------------------------------------------+-------------+---------------+-------+--------------+
| name                | value | info                                                                                                 | need_reboot | visible_level | range | config_level |
+---------------------+-------+------------------------------------------------------------------------------------------------------+-------------+---------------+-------+--------------+
| enable_primary_zone | True  | enable proxy route according to the tenant primary zone priority while proxy calculate route failed. | false       | USER          |       | LEVEL_GLOBAL |
+---------------------+-------+------------------------------------------------------------------------------------------------------+-------------+---------------+-------+--------------+
1 row in set (0.00 sec)mysql>

设置租户的Primary Zone优先级

命令行方式

obclient [oceanbase]> ALTER TENANT mq_t1 PRIMARY_ZONE='zone1,zone2';
Query OK, 0 rows affected

OCP方式

ODP指定Zone路由

官方声明如下

1.在业务并不关心Leader位置，需要路由到指定节点的场景下，我们可以通过ODP 配置项配置指定zone路由 （proxy_primary_zone_name），ODP会将请求路由至固定的OBServer节点。

2. ODP的配置项proxy_primary_zone_name 优先级高于OceanBase 集群租户的Primary Zone。该配置项是强制性的路由。如果是交易 支付等强读业务，希望路由到Leader的场景，不建议设置该路由策略，避免产生大量远程路由、二次路由等问题

使用sys租户登录ODP设置

[root@server061 ~]# mysql -h10.0.0.61 -P2883 -uroot@sys#obdemo61 -paaAA11__
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 879
Server version: 5.6.25 OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec  4 2024 20:33:18)Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql>
mysql>  ALTER PROXYCONFIG SET PROXY_PRIMARY_ZONE_NAME = 'zone1';
Query OK, 0 rows affected (0.02 sec)mysql>

普通租户创建测试表

[root@server061 ~]# mysql -h10.0.0.61 -P2883 -uroot@obmysql#obdemo61 -paaAA11__
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 892
Server version: 5.6.25 OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec  4 2024 20:33:18)Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql> use tpccdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql>
mysql> CREATE TABLE T(C1 INT,C2 INT,C3 INT) PARTITION BY-> HASH(C1) PARTITIONS 8;
Query OK, 0 rows affected (0.47 sec)

指定分区键进行查询，可以看出ODP路由到zone1上

mysql> EXPLAIN ROUTE SELECT * FROM T WHERE C1=1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Route Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Trans Current Query:"EXPLAIN ROUTE SELECT * FROM T WHERE C1=1"Route Prompts
-----------------
> ROUTE_INFO[INFO] Will do table partition location lookup to decide which OBServer to route toRoute Plan
-----------------
> SQL_PARSE:{cmd:"OB_MYSQL_COM_QUERY", table:"t"}
> ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"}
> LOCATION_CACHE_LOOKUP:{mode:"oceanbase"}> TABLE_ENTRY_LOOKUP_START:{}> FETCH_TABLE_RELATED_DATA:{part_level:1, part_expr:"c1"}> TABLE_ENTRY_LOOKUP_DONE:{table:"t", table_id:"500079", table_type:"USER TABLE", partition_num:8}> PARTITION_ID_CALC_START:{}> EXPR_PARSE:{col_val:"C1=1"}> RESOLVE_EXPR:{part_range:"[1 ; 1]"}> RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:"BIGINT:1", token:"1"}> CALC_PARTITION_ID:{part_description:"partition by hash(INT<binary>) partitions 8"}> PARTITION_ID_CALC_DONE:{partition_id:200057, level:1, partitions:"(p1)"}> PARTITION_ENTRY_LOOKUP_DONE:{leader:"10.0.0.61:2881"}
> ROUTE_POLICY:{route_policy:"PROXY_PRIMARY_ZONE_NAME_ONLY", replica:"10.0.0.61:2881", idc_type:"SAME_IDC", zone_type:"ReadWrite", role:"LEADER", type:"FULL", is_partition_server:true, proxy_primary_zone:"zone1", chosen_route_type:"ROUTE_TYPE_MAX", trans_consistency:"WEAK", session_consistency:"WEAK", proxy_idc_name:"hanzghou1"}
> CONGESTION_CONTROL:{svr_addr:"10.0.0.61:2881"}|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.14 sec)mysql>

LDC路由

官网声明如下

使用sys租户设置OceanBase 集群的LDC配置

[root@server061 ~]# mysql -h10.0.0.61 -P2883 -uroot@sys#obdemo61 -paaAA11__
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7616
Server version: 5.6.25 OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec  4 2024 20:33:18)Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> ALTER SYSTEM MODIFY zone "zone1" SET region = "zhejiang";
Query OK, 0 rows affected (0.08 sec)mysql> ALTER SYSTEM MODIFY zone "zone1" SET idc = "hanzghou1"-> ;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM oceanbase.DBA_OB_ZONES;
+-------+----------------------------+----------------------------+--------+-----------+----------+-----------+
| ZONE  | CREATE_TIME                | MODIFY_TIME                | STATUS | IDC       | REGION   | TYPE      |
+-------+----------------------------+----------------------------+--------+-----------+----------+-----------+
| zone1 | 2024-12-29 14:35:19.466142 | 2025-01-28 23:49:47.159742 | ACTIVE | hanzghou1 | zhejiang | ReadWrite |
+-------+----------------------------+----------------------------+--------+-----------+----------+-----------+
1 row in set (0.01 sec)mysql>

使用sys租户登录设置ODP的LDC配置

mysql> ALTER PROXYCONFIG SET proxy_idc_name= hanzghou1;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql>
mysql> SHOW PROXYINFO IDC;
+-----------------+--------------+----------------+-----------------+--------------+-------------+--------------+
| global_idc_name | cluster_name | match_type     | regions_name    | same_idc     | same_region | other_region |
+-----------------+--------------+----------------+-----------------+--------------+-------------+--------------+
| hanzghou1       | obdemo61     | MATCHED_BY_IDC | [[0]"zhejiang"] | [[0]"zone1"] | []          | []           |
+-----------------+--------------+----------------+-----------------+--------------+-------------+--------------+
1 row in set (0.01 sec)mysql>

官方声明

通过 配置项proxy_idc_name 控制给 OceanBase 集群的 Zone 设置 地区（Region）属性和机房（IDC）属性，并给ODP指定机房（IDC）配置 项，ODP将弱读请求按“同机房>同地区>异地”的优先级顺序进行 OBServer 的选取，主要体现是就近原则

随机路由

如果OB集群为多副本 且未开启 Primary Zone 路由 或者 未设置 LDC 路由，就会直接使用随机路由，如下图中租户Zone优先级为random的租户