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_sql field 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=1 statement is inaccurately routed. Run the following command for routing diagnostics:
obclient> EXPLAIN ROUTE select * from t0 where c1=1\G
The 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_memstore statement 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\G
The 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 read
The information [INFO] All OBServers treated as the SAME_IDC with OBProxy because 'proxy_idc_name' is not configured indicates 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.