tidb诡异的慢查询

版本
4.0.11
3pd+3tidb-server 三台主机混合部署
3tikv 三台主机单独部署
tikv capacity 32G
总数据容量100G

slow.log日志:

Time: 2021-06-05T16:19:53.783556576+08:00

Txn_start_ts: 425428566771499047

Conn_ID: 11957043

Query_time: 3.3727273159999998

Parse_time: 0.000025361

Compile_time: 0.000074588

Rewrite_time: 0.000014497

Prewrite_time: 3.370892345 Commit_time: 0.000878612 Get_commit_ts_time: 0.000131003 Commit_backoff_time: 2.9939999999999998 Backoff_types: [txnLock txnLock txnLock txnLock txnLock] Resolve_lock_time: 0.168004319 Write_keys: 3 Write_size: 1226 Prewrite_region: 2

DB: product

Is_internal: false

Digest: 28ba831ebb4396aeedbfb5521353e835b066f4c620a07cc8cc5be5e1f953a8e2

Num_cop_tasks: 0

Mem_max: 8641

Prepared: false

Plan_from_cache: false

Has_more_results: false

KV_total: 0.365733336

PD_total: 0.000253936

Backoff_total: 2.9939999999999998

Write_sql_response_total: 0

Succ: true

Plan_digest: 93d61d1a96f01290e4449062de22071a01b77d879ebeb77febff55170da7b968

update t1 set xxx=‘abc’ where x = ‘xyz’ and y = ‘m’;

其中 t1有300w的数据,PRIMARY KEY (x,y)

Prewrite_time: 3.370892345,这个时间这很高,有的慢查询这个值都是5秒 8秒的

请问这个怎么优化? 有什么思路? 谢谢!

麻烦发一下 explain analyze的执行计划

image

整个集群的负载很低很低

Point_Get应该是最快的了。。。analyze table product_detail试试

还是参考手册吧

1赞