根据主键查询出现慢sql

jiangdj1992 · 2020 年11 月 5 日 02:19

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

【TiDB 版本】：V4.0.0
【问题描述】：

tikv都是32核 , Coprocessor 都是默认80% 配置为25 ,手动执行毫秒级别,不知道为什么会经常有这种慢sql,应该不会慢才对

CREATE TABLE recommend_recall_user_history (
ukey varchar(64) NOT NULL ,
dt date NOT NULL ,
consults text NOT NULL ,
PRIMARY KEY (ukey,dt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( TO_DAYS(dt) ) (
PARTITION p20201028 VALUES LESS THAN (738092),
PARTITION p20201029 VALUES LESS THAN (738093),
PARTITION p20201030 VALUES LESS THAN (738094),
PARTITION p20201031 VALUES LESS THAN (738095),
PARTITION p20201101 VALUES LESS THAN (738096),
PARTITION p20201102 VALUES LESS THAN (738097),
PARTITION p20201103 VALUES LESS THAN (738098)
)

查询语句:

SELECT dt, consults FROM recommend_recall_user_history WHERE ukey = ? ORDER BY dt DESC LIMIT 1 [arguments: 307005];

查询计划:

Projection_22            	root	1	wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults
└─TopN_25                	root	1	wk_bigdata_main.recommend_recall_user_history.dt:desc, offset:0, count:1
  └─Union_29             	root	1	
    ├─Projection_54      	root	0	wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults
    │ └─IndexLookUp_53   	root	0	limit embedded(offset:0, count:1)
    │   ├─Limit_52       	cop 	0	offset:0, count:1
    │   │ └─IndexScan_50 	cop 	0	table:recommend_recall_user_history, partition:p20201028, index:PRIMARY(ukey, dt), range:["307005","307005"], keep order:true, desc
    │   └─TableScan_51   	cop 	0	table:recommend_recall_user_history, partition:p20201028, keep order:false, stats:pseudo
    ├─Projection_83      	root	0	wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults
    │ └─IndexLookUp_82   	root	0	limit embedded(offset:0, count:1)
    │   ├─Limit_81       	cop 	0	offset:0, count:1
    │   │ └─IndexScan_79 	cop 	0	table:recommend_recall_user_history, partition:p20201029, index:PRIMARY(ukey, dt), range:["307005","307005"], keep order:true, desc
    │   └─TableScan_80   	cop 	0	table:recommend_recall_user_history, partition:p20201029, keep order:false, stats:pseudo
    ├─Projection_112     	root	0	wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults
    │ └─IndexLookUp_111  	root	0	limit embedded(offset:0, count:1)
    │   ├─Limit_110      	cop 	0	offset:0, count:1
    │   │ └─IndexScan_108	cop 	0	table:recommend_recall_user_history, partition:p20201030, index:PRIMARY(ukey, dt), range:["307005","307005"], keep order:true, desc
    │   └─TableScan_109  	cop 	0	table:recommend_recall_user_history, partition:p20201030, keep order:false, stats:pseudo
    ├─Projection_141     	root	0	wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults
    │ └─IndexLookUp_140  	root	0	limit embedded(offset:0, count:1)
    │   ├─Limit_139      	cop 	0	offset:0, count:1
    │   │ └─IndexScan_137	cop 	0	table:recommend_recall_user_history, partition:p20201031, index:PRIMARY(ukey, dt), range:["307005","307005"], keep order:true, desc
    │   └─TableScan_138  	cop 	0	table:recommend_recall_user_history, partition:p20201031, keep order:false, stats:pseudo
    ├─Projection_170     	root	1	wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults
    │ └─IndexLookUp_169  	root	1	limit embedded(offset:0, count:1)
    │   ├─Limit_168      	cop 	1	offset:0, count:1
    │   │ └─IndexScan_166	cop 	1	table:recommend_recall_user_history, partition:p20201101, index:PRIMARY(ukey, dt), range:["307005","307005"], keep order:true, desc
    │   └─TableScan_167  	cop 	1	table:recommend_recall_user_history, partition:p20201101, keep order:false, stats:pseudo
    ├─Projection_199     	root	0	wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults
    │ └─IndexLookUp_198  	root	0	limit embedded(offset:0, count:1)
    │   ├─Limit_197      	cop 	0	offset:0, count:1
    │   │ └─IndexScan_195	cop 	0	table:recommend_recall_user_history, partition:p20201102, index:PRIMARY(ukey, dt), range:["307005","307005"], keep order:true, desc
    │   └─TableScan_196  	cop 	0	table:recommend_recall_user_history, partition:p20201102, keep order:false, stats:pseudo
    └─Projection_228     	root	0	wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults
      └─IndexLookUp_227  	root	0	limit embedded(offset:0, count:1)
        ├─Limit_226      	cop 	0	offset:0, count:1
        │ └─IndexScan_224	cop 	0	table:recommend_recall_user_history, partition:p20201103, index:PRIMARY(ukey, dt), range:["307005","307005"], keep order:true, desc
        └─TableScan_225  	cop 	0	table:recommend_recall_user_history, partition:p20201103, keep order:false, stats:pseudo

执行时间:
解析耗时
26.4 µs
解析该 SQL 查询的耗时
优化耗时
1.0 ms
优化该 SQL 查询的耗时
Coprocessor 累计等待耗时
7.4 s
Coprocessor 累计执行耗时
4.0 ms
Coprocessor 累计执行耗时
累计重试等待耗时
0 ns
取 Commit Ts 耗时
0 ns
从 PD 取递交时间戳（事务号）步骤的耗时
Local Latch Wait 耗时
0 ns
事务在 TiDB 本地与其他事务产生了锁冲突并等待的耗时
Resolve Lock 耗时
0 ns
事务在 TiKV 与其他事务产生了锁冲突并处理锁冲突的耗时
Prewrite 阶段耗时
0 ns
Commit 阶段耗时
0 ns
Commit 重试等待耗时
0 ns
SQL 执行时间
3.7 s
由于存在并行执行，因此 SQL 执行时间可能远小于上述各项时间

coprocessor读取:

Coprocessor 请求数
14
可见版本数
14
遇到版本数
21
含已删除或覆盖但未 GC 的版本
最长处理时间实例
10.223.1.159:20171
耗费最长时间处理 Coprocessor 请求的 TiKV 实例地址
最长等待时间实例
10.223.1.155:20171
耗费最长时间等待 Coprocessor 请求的 TiKV 实例地址

集群信息

yilong · 2020 年11 月 5 日 03:37

可以发个慢日志看下主要慢在哪里
看下监控，在慢的时候，是否集群整体压力大，导致 sql duraiton 上升

jiangdj1992 · 2020 年11 月 5 日 07:28

优化该 SQL 查询的耗时
Coprocessor 累计等待耗时
7.4 s
SQL 执行时间
3.7 s

耗时如上
全天的sql duration 执行时间基本在250ms以下,但是该查询的处于等待耗时的慢sql平均每分钟都有1-2个

yilong · 2020 年11 月 5 日 09:33

麻烦能把具体的慢日志和慢日志记录的sql贴一下吗？
explain analayze sql 再单独执行下看看执行计划。
排查下是否某些值耗时比较久

jiangdj1992 · 2020 年11 月 5 日 09:44

手动分析结果

±-------------------------------- | id ±-------------------------------- | Projection_22 | └─TopN_25 | └─Union_29 | ├─Projection_54 | │ └─IndexLookUp_53 | │ ├─Limit_52(Build) | │ │ └─IndexRangeScan_50 | │ └─TableRowIDScan_51(Probe) | ├─Projection_83 | │ └─IndexLookUp_82 | │ ├─Limit_81(Build) | │ │ └─IndexRangeScan_79 | │ └─TableRowIDScan_80(Probe) | ├─Projection_112 | │ └─IndexLookUp_111 | │ ├─Limit_110(Build) | │ │ └─IndexRangeScan_108 | │ └─TableRowIDScan_109(Probe) | ├─Projection_141 | │ └─IndexLookUp_140 | │ ├─Limit_139(Build) | │ │ └─IndexRangeScan_137 | │ └─TableRowIDScan_138(Probe) | ├─Projection_170 | │ └─IndexLookUp_169 | │ ├─Limit_168(Build) | │ │ └─IndexRangeScan_166 | │ └─TableRowIDScan_167(Probe) | ├─Projection_199 | │ └─IndexLookUp_198 | │ ├─Limit_197(Build) | │ │ └─IndexRangeScan_195 | │ └─TableRowIDScan_196(Probe) | └─Projection_228 | └─IndexLookUp_227 | ├─Limit_226(Build) | │ └─IndexRangeScan_224 | └─TableRowIDScan_225(Probe) ±-------------------------------- 38 rows in set (0.01 sec) ----------±--------±--------±----------±----------------------------------------------------------------------------------±-----------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------±-----+
| estRows | actRows | task | access object | execution info | operator info| memory | disk |
----------±--------±--------±----------±----------------------------------------------------------------------------------±-----------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------±-----+
| 1.00 | 1 | root | | time:4.648372ms, loops:2, Concurrency:OFF | wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults| 1.33203125 KB | N/A |
| 1.00 | 1 | root | | time:4.646282ms, loops:2 | wk_bigdata_main.recommend_recall_user_history.dt:desc, offset:0, count:1| 1.33984375 KB | N/A |
| 3.00 | 7 | root | | time:4.616934ms, loops:8 || N/A | N/A |
| 1.00 | 1 | root | | time:2.827014ms, loops:2, Concurrency:OFF | wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults| 1.6953125 KB | N/A |
| 1.00 | 1 | root | | time:2.797285ms, loops:2, rpc num: 1, rpc time:1.087569ms, proc keys:1 | limit embedded(offset:0, count:1)| 1.96484375 KB | N/A |
| 1.00 | 1 | cop[tikv] | | time:1ms, loops:1 | offset:0, count:1| N/A | N/A |
| 1.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201029, index:PRIMARY(ukey, dt) | time:1ms, loops:1 | range:[“59778180”,“59778180”], keep order:true, desc| N/A | N/A |
| 1.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201029 | time:0s, loops:1 | keep order:false, stats:pseudo| N/A | N/A |
| 0.00 | 1 | root | | time:1.804594ms, loops:2, Concurrency:OFF | wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults| 1.6953125 KB | N/A |
| 0.00 | 1 | root | | time:1.793503ms, loops:2, rpc num: 1, rpc time:1.016619ms, proc keys:1 | limit embedded(offset:0, count:1)| 1.96484375 KB | N/A |
| 0.00 | 1 | cop[tikv] | | time:0s, loops:1 | offset:0, count:1| N/A | N/A |
| 0.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201030, index:PRIMARY(ukey, dt) | time:0s, loops:1 | range:[“59778180”,“59778180”], keep order:true, desc| N/A | N/A |
| 0.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201030 | time:0s, loops:1 | keep order:false, stats:pseudo| N/A | N/A |
| 0.00 | 1 | root | | time:2.151436ms, loops:2, Concurrency:OFF | wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults| 1.6953125 KB | N/A |
| 0.00 | 1 | root | | time:2.145251ms, loops:2, rpc num: 1, rpc time:883.016µs, proc keys:1 | limit embedded(offset:0, count:1)| 1.96484375 KB | N/A |
| 0.00 | 1 | cop[tikv] | | time:0s, loops:1 | offset:0, count:1| N/A | N/A |
| 0.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201031, index:PRIMARY(ukey, dt) | time:0s, loops:1 | range:[“59778180”,“59778180”], keep order:true, desc| N/A | N/A |
| 0.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201031 | time:0s, loops:1 | keep order:false, stats:pseudo| N/A | N/A |
| 0.00 | 1 | root | | time:2.384568ms, loops:2, Concurrency:OFF | wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults| 1.6953125 KB | N/A |
| 0.00 | 1 | root | | time:2.375214ms, loops:2, rpc num: 1, rpc time:1.052965ms, proc keys:1 | limit embedded(offset:0, count:1)| 1.96484375 KB | N/A |
| 0.00 | 1 | cop[tikv] | | time:1ms, loops:1 | offset:0, count:1| N/A | N/A |
| 0.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201101, index:PRIMARY(ukey, dt) | time:1ms, loops:1 | range:[“59778180”,“59778180”], keep order:true, desc| N/A | N/A |
| 0.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201101 | time:0s, loops:1 | keep order:false, stats:pseudo| N/A | N/A |
| 1.00 | 1 | root | | time:4.493495ms, loops:2, Concurrency:OFF | wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults| 1.6953125 KB | N/A |
| 1.00 | 1 | root | | time:4.465657ms, loops:2, rpc num: 1, rpc time:1.420524ms, proc keys:1 | limit embedded(offset:0, count:1)| 1.96484375 KB | N/A |
| 1.00 | 1 | cop[tikv] | | time:0s, loops:1 | offset:0, count:1| N/A | N/A |
| 1.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201102, index:PRIMARY(ukey, dt) | time:0s, loops:1 | range:[“59778180”,“59778180”], keep order:true, desc| N/A | N/A |
| 1.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201102 | time:0s, loops:1 | keep order:false, stats:pseudo| N/A | N/A |
| 1.00 | 1 | root | | time:2.433778ms, loops:2, Concurrency:OFF | wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults| 1.6953125 KB | N/A |
| 1.00 | 1 | root | | time:2.423557ms, loops:2, rpc num: 1, rpc time:1.275138ms, proc keys:1 | limit embedded(offset:0, count:1)| 1.96484375 KB | N/A |
| 1.00 | 1 | cop[tikv] | | time:0s, loops:1 | offset:0, count:1| N/A | N/A |
| 1.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201103, index:PRIMARY(ukey, dt) | time:0s, loops:1 | range:[“59778180”,“59778180”], keep order:true, desc| N/A | N/A |
| 1.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201103 | time:0s, loops:1 | keep order:false, stats:pseudo| N/A | N/A |
| 0.00 | 1 | root | | time:2.605437ms, loops:2, Concurrency:OFF | wk_bigdata_main.recommend_recall_user_history.ukey, wk_bigdata_main.recommend_recall_user_history.dt, wk_bigdata_main.recommend_recall_user_history.consults| 1.6953125 KB | N/A |
| 0.00 | 1 | root | | time:2.59585ms, loops:2, rpc num:1, rpc time:670.488µs, proc keys:1 | limit embedded(offset:0, count:1)| 1.96484375 KB | N/A |
| 0.00 | 1 | cop[tikv] | | time:0s, loops:1 | offset:0, count:1| N/A | N/A |
| 0.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201104, index:PRIMARY(ukey, dt) | time:0s, loops:1 | range:[“59778180”,“59778180”], keep order:true, desc| N/A | N/A |
| 0.00 | 1 | cop[tikv] | table:recommend_recall_user_history, partition:p20201104 | time:0s, loops:1 | keep order:false, stats:pseudo| N/A | N/A |
----------±--------±--------±----------±----------------------------------------------------------------------------------±-----------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------±-----+

######慢sql日志

Time: 2020-10-26T08:03:21.753897605+08:00

Txn_start_ts: 420392625564549124

User: wk_aw@10.223.0.105

Conn_ID: 1713191

Query_time: 2.48465201

Parse_time: 0.000010544

Compile_time: 0.000845917

Cop_time: 2.4944637419999998 Process_time: 0.006 Wait_time: 2.482 Request_count: 14 Total_keys: 21 Process_keys: 14

DB: wk_bigdata_main

Index_names: [recommend_recall_user_history:PRIMARY,recommend_recall_user_history:PRIMARY,recommend_recall_user_history:PRIMARY,recommend_recall_user_history:PRIMARY,recommend_recall_user_history:PRIMARY,recommend_recall_user_history:PRIMARY,recommend_recall_user_history:PRIMARY]

Is_internal: false

Digest: 2c79bd9ad4c41b9d4a1bf2e1808d5027d469e905c4026c1225b8e0ab162ff9e8

Stats: recommend_recall_user_history:420390454868312070

Num_cop_tasks: 14

Cop_proc_avg: 0.000428571 Cop_proc_p90: 0.001 Cop_proc_max: 0.001 Cop_proc_addr: 10.223.1.159:20171

Cop_wait_avg: 0.177285714 Cop_wait_p90: 0.001 Cop_wait_max: 2.481 Cop_wait_addr: 10.223.1.156:20171

Mem_max: 28972

Prepared: true

Plan_from_cache: false

Has_more_results: false

Succ: true

Plan: tidb_decode_plan(‘6RzwPDAJM18yMgkwCTEJd2tfYmlnZGF0YV9tYWluLnJlY29tbWVuZF9yZWNhbGxfdXNlcl9oaXN0b3J5LmR0LCC2MgA8Y29uc3VsdHMKMQkxNV8yNdJ1AHg6ZGVzYywgb2Zmc2V0OjAsIGNvdW50OjEKMgk5XzI5BVQoCjMJM181NAkwCTC61QAMdWtleb7XAOoJARg0CTMwXzUzBak4bGltaXQgZW1iZWRkZWQoQuEANCkKNQkxNl81MgkxCTAJQh8AGAo2CTEzXzUhyBwwCXRhYmxlOnLAAZAsIHBhcnRpdGlvbjpwMjAyMDEwMTksIGluZGV4OlBSSU1BUlkoKQ9YZHQpLCByYW5nZTpbIjU5Nzc4MTgwIiwZC0xdLCBrZWVwIG9yZGVyOnRydWUsICGnAbIMMF81MQWy5pQAHVxIZmFsc2UsIHN0YXRzOnBzZXVkbyXtADgpRLaQAgnetjQA/u0BADhhawAwnu0BADgpO1rtAQQ3OepZAQQyMP7tAVLtAQA47oECAZSK7QEEMTEpRba6Af7uAc7uAQQxMWGsou8BBDExKT5a8AEIMTA47vEBADH+8QFS8QEEMTDyhgIAMTI7BF7fAwQxNClI/vIB/vIBivIBADTBdabyAQAzKT5e8gEEMzfu8gEAMv7yAVbyAQAz8ocCADKW8gEANylI/vIB/vIBivIBADYOdQgAMJ7TBQQxNik+XvIBBDY27vIBADP+8gFW8gEANvKHAgAzlvIBADkpSP7yAf7yAYryAQQ5OK6yCQQxOSk+XvIBBDk17vIBADT+8gFW8gEAOfKHAgA0kvIBBDIyIUgAMf7yAf7yAYryAQgyMjcSWQyapAsEMjIhPgAxXvIBDDIyNAkO2QnWEgsEMjX+8gFS8gEEMjJBhwAx6pUAeroJ’)

Plan_digest: cb867b2f28c3cdc92fa97a097f94111f53c6f17678227e7bc4245c925316a58a

SELECT dt, consults FROM recommend_recall_user_history WHERE ukey = ? ORDER BY dt DESC LIMIT 1 [arguments: 59778180];

yilong · 2020 年11 月 5 日 10:37

从慢日志解析

image2359×938 676 KB
慢日志中主要时间花费在 cop 的 wait_time ，应该是这个时间段集群比较繁忙。
wait_time ：该语句在 TiKV 的等待时间之和，因为 TiKV 的 Coprocessor 线程数是有限的，当所有的 Coprocessor 线程都在工作的时候，请求会排队；当队列中有某些请求耗时很长的时候，后面的请求的等待时间都会增加
可以查看 tikv-detail 问题发生时的监控信息，coprocessor 中，看看wait的时间。

jiangdj1992 · 2020 年11 月 5 日 11:33

screencapture-10-223-0-159-3000-d-RDVQiEzZz-cdel-tidb-223-cluster-tikv-details-2020-11-05-19_28_04.pdf (2.9 MB)

上图是coprocessor的监控图, 这个慢sql几乎总会出现, 并不是偶尔一个,上面的监控图可以看到有4-8s等待的情况,目前我们32核的tikv readpool.unified.max-thread-count的值默认是25,这种情况还需要再增加吗, cpu负载可以说很低,不知道为什么还会等待呢, 或者还是配置统一线程池,把readpool.storage.use-unified-pool=true吗

spc_monkey · 2020 年11 月 6 日 02:20

1、先说一下：通过慢日志可以发现，整个 SQL 的耗时发生在【 Cop_wait_max: 2.481 Cop_wait_addr: 10.223.1.156:20171】这一块，即 tikv：10.223.1.156:20171 在处理时 Cop_wait 时间较高。
2、通过给的监控，也可以发现对应时间：kv 扫描的耗时较高，扫描的 kv 数量其实是很高的，但这与实际返回行数 1 其实是不匹配的，给的监控[perf statistics] 中，显示当时扫描了较多 delete_skipped 。该现象可以通过 tikv 日志中能找到证明
3、建议判断下业务：该表的数据是否频繁的 delete/update。适当调整一下 GC 的时间「注意：GC 时间不宜太短」

jiangdj1992 · 2020 年11 月 6 日 02:28

该表是分区表，每天都会drop最旧的分区，添加一个新分区
频繁更新或者删除可能是其他库的其他表

jiangdj1992 · 2020 年11 月 6 日 02:31

该表写入不频繁，只有读频繁

spc_monkey · 2020 年11 月 6 日 03:52

1、通过慢日志，找到对应慢 SQL ，根据慢日志找到当时 SQL 的实际参数【主键及其他条件）
2、使用如下 API 【https://github.com/pingcap/tidb/blob/master/docs/tidb_http_api.md
可以用这里第 11 条的方案 Get MVCC Information of the key with a specified handle ID】，找到对应值，所在的 region 及 mvcc 信息，然后把相关结果信息，麻烦上传一下

jiangdj1992 · 2020 年11 月 6 日 06:24

info.txt (8.1 KB)

具体见附件，麻烦帮忙看下， handle id不太清楚是取哪个值，都用的1

jiangdj1992 · 2020 年11 月 6 日 06:29

tikv.log (54.2 KB)

这个附件是慢sql在 wait time 数据tikv节点的当时时间的日志情况

spc_monkey · 2020 年11 月 6 日 07:15

1、handle id 即主键（主键为int时），或者通过 select _tidb_rowid from xxx where xxx 找到，对应行所对应的 handle id
2、对应的 tikv 时间对吗？感觉没有找到有用的信息

jiangdj1992 · 2020 年11 月 6 日 07:30

info.txt (11.6 KB)

这次都放在附件里了，按_tidb_rowid 获取的

spc_monkey · 2020 年11 月 6 日 10:08

看信息和监控的结论不符，能把tikv-detail 的监控（全部指标）及 tikv 对应时间前后几分钟的日志都给一下吗

jiangdj1992 · 2020 年11 月 6 日 10:13

1356.log (47.6 KB)

这个才是223.154 tikv当时的日志，刚过率有问题，监控图稍等

jiangdj1992 · 2020 年11 月 6 日 10:23

screencapture-10-223-0-159-3000-d-RDVQiEzZz-cdel-tidb-223-cluster-tikv-details-2020-11-06-18_15_30-21600×20259 5.24 MB

jiangdj1992 · 2020 年11 月 6 日 10:41

复制这段内容后打开百度网盘App，操作更方便哦。
链接：https://pan.baidu.com/s/1QHGZbTB7wsbhmtfSRHNHvA
提取码：6oHn

由于图片太大了, 分享不出来贴到网盘了,麻烦了

spc_monkey · 2020 年11 月 7 日 11:32

1、监控有些指标打不开，不过发现有少量的 region not leader 问题，另外，seek 及 get 较慢，建议看看是否有其他慢日志（最好 tidb-server 及 tikv-server 对应前后几分钟，看看是否有 slow query）
2、确认一下，topology 中是否配置了资源限制，目前看资源使用不算高，所以需要确认一下，是否通过 resource contorl 限制了资源使用