This topic describes the table-based routing mechanism of OceanBase Database Proxy (ODP).
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. Both the local plan and remote plan types refer to routing based on a single partition. ODP is designed to change remote plans into local plans as much as possible. If you have a large number of SQL statements whose table-based routing types are remote plans, the routing of the ODP may be abnormal. You can check the plan_type field in the oceanbase.GV$OB_SQL_AUDIT view for verification.
Routing based on non-partitioned tables
ODP saves the mappings between partitions and IP addresses of OBServer nodes. Given a partition, ODP 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, ODP pulls the IP address from the
__all_virtual_proxy_schematable of the OBServer node.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.
Routing based on partitioned tables
During routing based on partitioned tables, ODP saves the mappings between partitions and IP addresses of OBServer nodes. ODP obtains the corresponding table name for a given partition 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. Here is an example:
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 obtain accurate routing information. You can search for "calculate partition id" in the obproxy.log file to check whether the partition ID is successfully calculated. Here is an example:
[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.
OceanBase Database internal tables that ODP depends on
The routing accuracy is ensured based on both ODP capabilities and the information obtained from internal tables of OceanBase Database. The following list describes the internal tables of OceanBase Database 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.