This topic describes the table-based routing mechanism of OceanBase Database Proxy (ODP, also known as OBProxy).
In OceanBase Database, a partition is a basic unit for data storage. After tables are created, mappings between tables and partitions are defined. A non-partitioned table is mapped to a single partition, and a partitioned table may be mapped to multiple partitions. Currently, OceanBase Database does not support the major compaction or splitting of partitions.
OceanBase Database supports three table-based routing types: local plan, remote plan, and distributed plan. The distributed plan type is not supported now. Both the local plan and remote plan types refer to routing based on a single partition. ODP aims to change remote plans into local plans as much as possible. If a large number of SQL statements have execution plans of the remote plan type, the routing of ODP is abnormal. You can view the plan_type field in the gv$sql_audit view to check the information.
Routing based on non-partitioned tables
ODP saves the mappings between partitions and OBServer node IP addresses. A partition obtains the corresponding table name from the SQL parser, and then uses this table name to query the location cache for the IP address of the OBServer node to which the partition is mapped.
You may encounter one of the following situations:
The IP address does not exist in the location cache. In this case, the partition accesses the
__all_virtual_proxy_schematable to obtain the IP address.The IP address exists in the location cache and is available. In this case, the IP address is directly used.
The IP address exists in the location cache but is unavailable. In this case, ODP removes the IP address from the location cache and pulls a new IP address.
For more information about the location cache, see ODP User Guide.
Routing based on partitioned tables
In routing based on partitioned tables, ODP also saves the mappings between partitions and OBServer node IP addresses. A partition obtains the corresponding table name from the SQL parser. A partitioned table provides information about partition IDs. ODP uses the given table name and partition ID to query the location cache for the IP address of the OBServer node to which the partition is mapped.
The partition ID can be obtained from the expression in the WHERE clause or the values keyword in the INSERT statement. Make sure that the partition ID can be obtained in the format of a = xxx, where a is a partition key and xxx is a constant. Sample code:
obclient> CREATE TABLE t1(c1 int, c2 int) PARTITION BY hash(c1) partitions 5;
obclient> UPDATE t1 SET c2 = 3 WHERE c1 = 5;
In this example, c1 = 5 is obtained. Based on this information, the ID of the affected partition can be calculated, and the SQL statement to be executed can be forwarded to the OBServer node where the partition is located.
Without the partition ID, ODP cannot query the corresponding route from the OBServer node. You can search for "calculate partition id" in the obproxy.log file to check whether the partition ID is successfully calculated. Sample code:
[root@ocp-deploy-obnorpm2-bfc89744b-qsfkz log]# grep "calculate partition id" obproxy.log
[2021-10-28 17:18:18.552825] DEBUG [PROXY] ob_mysql_route.cpp:577 [73979][Y0-7FB3C9D07A80] [lt=10] [dc=0] succ to calculate partition id(part_id_=1)
[2021-10-28 17:18:31.045191] INFO [PROXY] ob_mysql_route.cpp:575 [73979][Y0-7FB3C9D07A80] [lt=17] [dc=0] fail to calculate partition id, just use tenant server(tmp_ret=-4002)
For more information about partitioned tables, see Manage partitions.
OBServer internal tables that ODP depends on
The accuracy of routing is ensured based on ODP and the information obtained from OBServer internal tables. The following list describes the OBServer internal tables that ODP depends on:
__all_virtual_proxy_schema: stores the mappings between partitions and OBServer nodes, as well as specific information about partitions.__all_virtual_proxy_partition_info: stores the partition information about partitioned tables.__all_virtual_proxy_partition: stores partition information.__all_virtual_proxy_sub_partition: stores subpartition information.