This topic provides several examples to describe how to use the routing diagnostics feature in MySQL mode.
Note
For information about diagnostic points, see Overview.
The examples assume that
route_diagnosis_levelis set to4.Example 1: PS/PL statement call
If it is difficult to find the actually executed statement when a PS call is inaccurately routed, you can view the corresponding diagnostic logs.
[2023-09-19 18:48:49.079458] [106700][Y0-00007FD892AB64E0] [ROUTE]((*route_diagnosis= Trans Current Query:"execute stmt" Route Prompts -------------- > ROUTE_INFO [INFO] Will do table partition location lookup to decide which OBServer to route to > ROUTE_POLICY [INFO] Will route to table's partition leader replica(10.10.10.1:4001) using non route policy because query for STRONG read Route Plan -------------- > SQL_PARSE:{cmd:"COM_QUERY", table:"t0"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_DONE:{table:"t0", table_id:"500078", table_type:"USER TABLE", partition_num:64, entry_from_remote:false} > PARTITION_ID_CALC_START:{} > EXPR_PARSE:{col_val:"=88888888,=1111111"} > RESOLVE_EXPR:{part_range:"[88888888 ; 88888888]", sub_part_range:"[1111111 ; 1111111]"} > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:"BIGINT:88888888", token:"88888888"} > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:"BIGINT:1111111", token:"1111111"} > CALC_PARTITION_ID:{part_description:"partition by hash(INT<binary>) partitions 8 subpartition by hash(INT<binary>) partitions 8"} > PARTITION_ID_CALC_DONE:{partition_id:200073, level:2, partitions:"(p0sp7)", parse_sql:"prepare stmt from 'insert into t0 values(88888888,1111111,9999999)'"} > PARTITION_ENTRY_LOOKUP_DONE:{leader:"10.10.10.1:4001", entry_from_remote:false} > ROUTE_POLICY:{chosen_route_type:"ROUTE_TYPE_LEADER"} > CONGESTION_CONTROL:{svr_addr:"10.10.10.1:4001"}You can find the output of the executed statement in the
parse_sqlfield of the diagnostic information of the PARTITION_ID_CALC_DONE diagnostic point.Example 2: Partitioned table query
Inaccurate partitioned table-based routing because partitioning key values are not provided
Assume that t0 is a subpartitioned table and the executed
select * from t0 where c1=1statement is inaccurately routed. Run the following command for routing diagnostics:obclient> EXPLAIN ROUTE select * from t0 where c1=1\GThe output is as follows:
Trans Current Query:"select * from t0 where c1=1" Route Prompts -------------- > ROUTE_INFO [INFO] Will do table partition location lookup to decide which OBServer to route to > PARTITION_ID_CALC_DONE [WARN] Fail to use partition key value to calculate sub part idx Route Plan -------------- > SQL_PARSE:{cmd:"COM_QUERY", table:"t0"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_DONE:{table:"t0", table_id:"500078", table_type:"USER TABLE", partition_num:64, entry_from_remote:false} > PARTITION_ID_CALC_START:{} > EXPR_PARSE:{col_val:"c1=1"} > RESOLVE_EXPR:{part_range:"[1 ; 1]", sub_part_range:"(MIN ; MAX)always true"} > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:"BIGINT:1", token:"1"} > CALC_PARTITION_ID:{error:-4002, part_description:"partition by hash(INT<binary>) partitions 8 subpartition by hash(INT<binary>) partitions 8"} > PARTITION_ID_CALC_DONE:{error:-4002, partition_id:-1, level:2, partitions:"(p1sp-1)"}Check the diagnostic result.
The
PARTITION_ID_CALC_DONE [WARN] Fail to use partition key value to calculate sub part idxinformation in Route Prompts indicates that subpartition location calculation fails.Check whether relevant information exists near PARTITION_ID_CALC_DONE in Remote Plan.
The diagnostic information at the diagnostic point
RESOLVE_EXPR:{part_range:"[1 ; 1]", sub_part_range:"(MIN ; MAX)always true"}indicates that the subpartition range isMIN:MAX. Therefore, the subpartition location cannot be determined.It can be deemed that subpartitioning key values are not provided in the
Trans Current Query:"select * from t0 where c1=1"statement.
Inaccurate partitioned table-based routing when partitioning key values are calculated by using an unsupported expression
Assume that t0 is a partitioned table and the executed
select * from t0 where c1=abs(-100.123);statement is inaccurately routed. View the corresponding diagnostic log.[2023-09-19 19:43:11.029616] [106683][Y0-00007FD890E544E0] [ROUTE]((*route_diagnosis= Trans Current Query:"select * from t0 where c1=abs(-100.123)" Route Prompts -------------- > ROUTE_INFO [INFO] Will do table partition location lookup to decide which OBServer to route to > RESOLVE_TOKEN [WARN] Not support to resolve expr func(abs) Route Plan -------------- > SQL_PARSE:{cmd:"COM_QUERY", table:"t0"} > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"} > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"} > TABLE_ENTRY_LOOKUP_DONE:{table:"t0", table_id:"500078", table_type:"USER TABLE", partition_num:64, entry_from_remote:false} > PARTITION_ID_CALC_START:{} > EXPR_PARSE:{col_val:"c1=abs"} > RESOLVE_EXPR:{error:-5055, part_range:"(MIN ; MAX)always true", sub_part_range:"(MIN ; MAX)always true"} > RESOLVE_TOKEN:{error:-5055, token_type:"TOKEN_FUNC", token:"abs"} > PARTITION_ID_CALC_DONE:{error:-5055, partition_id:-1, level:2, partitions:"(p-1sp-1)"} > ROUTE_INFO:{route_info_type:"USE_CACHED_SESSION", svr_addr:"10.10.10.1:4001"} > CONGESTION_CONTROL:{svr_addr:"10.10.10.1:4001"} > HANDLE_RESPONSE:{is_parititon_hit:"true", send_action:"SERVER_SEND_REQUEST", state:"CMD_COMPLETE"} )In the diagnostic result, the information
RESOLVE_TOKEN [WARN] Not support to resolve expr func(abs)indicates that the abs expression fails to be parsed. As a result, the partitioning key values cannot be correctly calculated, leading to inaccurate routing.Strategy-based routing
Assume that the
SELECT 100 - max(round(total / mem_limit * 100)) FROM oceanbase.gv$ob_memstorestatement is not routed as expected. Run the following command for routing diagnostics:obclient> EXPLAIN ROUTE SELECT 100 - max(round(total / mem_limit * 100)) FROM oceanbase.gv$ob_memstore\GThe output is as follows:
*************************** 1. row *************************** Route Plan: Trans Current Query:"EXPLAIN ROUTE SELECT 100 - max(round(total / mem_limit * 100)) FROM oceanbase.gv$ob_memstore" Route Prompts ----------------- > ROUTE_INFO [INFO] Will do table partition location lookup to decide which OBServer to route to > TABLE_ENTRY_LOOKUP_DONE [INFO] Non-partition table will be routed by ROUTE_POLICY > ROUTE_POLICY [INFO] All OBServers treated as the SAME_IDC with OBProxy because 'proxy_idc_name' is not configured [INFO] Will route to routing type(NONPARTITION_UNMERGE_LOCAL) matched replica(10.10.10.1:4001) using default route policy MERGE_IDC_ORDER because query for STRONG readThe information
[INFO] All OBServers treated as the SAME_IDC with OBProxy because 'proxy_idc_name' is not configuredindicates that the proxy_idc_name parameter is not configured. Therefore, ODP considers that all OBServer nodes are in the same IDC. As a result, LDC-based routing becomes invalid.