弱读咋没生效呢 ？

OceanBase 技术问题
#1

图片
图片984×216 8.96 KB

select /+READ_CONSISTENCY(WEAK)/ * from t1 where tid=2003;

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.1.73:2881) using non route policy because query for STRONG read

Route Plan

SQL_PARSE:{cmd:“OB_MYSQL_COM_QUERY”, table:“t1”}
ROUTE_INFO:{route_info_type:“USE_PARTITION_LOCATION_LOOKUP”}
LOCATION_CACHE_LOOKUP:{mode:“oceanbase”}
TABLE_ENTRY_LOOKUP_DONE:{table:“t1”, table_id:“500002”, table_type:“USER TABLE”, partition_num:3, entry_from_remote:false}
PARTITION_ID_CALC_START:{}
EXPR_PARSE:{col_val:“tid=2003”}
RESOLVE_EXPR:{part_range:"[2003 ; 2003]"}
RESOLVE_TOKEN:{token_type:“TOKEN_INT_VAL”, resolve:“BIGINT:2003”, token:“2003”}
CALC_PARTITION_ID:{part_description:“partition by hash(INT) partitions 3”}
PARTITION_ID_CALC_DONE:{partition_id:200003, level:1, partitions:"(p2)"}
PARTITION_ENTRY_LOOKUP_DONE:{leader:“10.10.1.73:2881”, entry_from_remote:false}
ROUTE_POLICY:{chosen_route_type:“ROUTE_TYPE_LEADER”}
CONGESTION_CONTROL:{svr_addr:“10.10.1.73:2881”}
HANDLE_RESPONSE:{is_parititon_hit:“true”, send_action:“SERVER_SEND_REQUEST”, state:“TRANSACTION_COMPLETE”}
)

看看，明明要求弱读了，它还是偏偏走强读。

2 个赞
#3

这里写的有点问题吧：
select /+READ_CONSISTENCY(WEAK)/ * from t1 where tid=2003;
是不是应该写成这样呢：
select /+READ_CONSISTENCY(WEAK) / * from t1 where tid=2003;
再就是排查一下，如果是mysql租户，连接数据库的时候有没有加-c参数，没加的话hint不生效

2 个赞
#4

写在这里的*星号没有显示 :joy:

1 个赞
#5

Follower 副本有延迟吗

1 个赞
#6

有这个说法哈 ，那我测试一下。

1 个赞
#7

测了一下，不是这个原因

1 个赞
#8

楼上说的这种**号问题尝试了么/*+READ_CONSISTENCY(WEAK) */
你的obproxy+ob集群架构是怎样的

1 个赞
#9

试过了，
[INFO] Will route to table’s partition leader replica(10.10.1.73:2881) using non route policy because query for STRONG read

它还是strong。

集群是，3observer + 3obproxy

1 个赞
#10

加上explain extend看一下执行计划

1 个赞
#11

MySQL [test]> explain extended select /+READ_CONSISTENCY(WEAK)/ * from t1 where tid=2003;
±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|t1 |1 |5 | |
| =============================================== |

Outputs & filters:
0 - output([t1.tid(0x7f126be1fad0)], [t1.name(0x7f126be20d30)], [t1.gender(0x7f126be21010)], [t1.age(0x7f126be212f0)], [t1.subject(0x7f126be215d0)], [t1.salary(0x7f126be218b0)]), filter([t1.tid(0x7f1
26be1fad0) = 2003(0x7f126be20350)]), rowset=16
access([t1.tid(0x7f126be1fad0)], [t1.name(0x7f126be20d30)], [t1.gender(0x7f126be21010)], [t1.age(0x7f126be212f0)], [t1.subject(0x7f126be215d0)], [t1.salary(0x7f126be218b0)]), partitions(p2)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([t1.__pk_increment(0x7f126be21dc0)]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
/*+
READ_CONSISTENCY( WEAK )
*/
Qb name trace:
-------------------------------------
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@“SEL$1” “test”.“t1”@“SEL$1”)
READ_CONSISTENCY( WEAK )
OPTIMIZER_FEATURES_ENABLE(‘4.0.0.0’)
END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
t1:
table_rows:4
physical_range_rows:4
logical_range_rows:4
index_back_rows:0
output_rows:1
table_dop:1
dop_method:Table DOP
avaiable_index_name:[t1]
stats version:1733148002848847
dynamic sampling level:0
Plan Type:
LOCAL
Note:
Degree of Parallelisim is 1 because of table property

±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
48 rows in set (0.06 sec)

1 个赞
#12

MySQL [OCEANBASE]> select region,idc from dba_ob_zones;
±----------±-----+
| region | idc |
±----------±-----+
| chong | idc1 |
| chong | idc2 |
| chong | idc3 |
±----------±-----+

#13

先修改obproxy参数设置备优先读，再使用hint方式弱读试试
alter proxyconfig set proxy_route_policy=“follower_first”;

2 个赞
#14

对 对 ， 你的方法正确

#15

奥 原来是这样啊