分区表的一个SQL偶尔变得很慢

[Mingdr (会飞的猫)]

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

• 【TiDB 版本】：5.7.25-TiDB-v3.0.11
• 【问题描述】：同一个SQL，偶尔会变得很慢，正常都是100ms以内，但是不确定什么时候就会出现1-2s才能出结果，服务器压力很小，该怎么确定原因呢？
  分区表结构：

CREATE TABLE t_device_request (
REQUEST_ID bigint(20) NOT NULL AUTO_INCREMENT,
REQ_SEQ varchar(50) DEFAULT NULL ,
ORI_SEQ varchar(50) DEFAULT NULL,
REQUEST_TIME datetime NOT NULL ,
PRIMARY KEY (REQUEST_ID,REQUEST_TIME),
KEY INDEX_REQUEST_ORI_SEQ (ORI_SEQ),
KEY INDEX_REQUEST_REQ_SEQ (REQ_SEQ),
KEY INDEX_REQUEST_TIME (REQUEST_TIME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=450240001
PARTITION BY RANGE ( to_days(request_time) ) (
PARTITION p20200706 VALUES LESS THAN (737977),
PARTITION p20200713 VALUES LESS THAN (737984),
PARTITION p20200720 VALUES LESS THAN (737991),
PARTITION p20200727 VALUES LESS THAN (737998),
PARTITION p20200803 VALUES LESS THAN (738005),
PARTITION p20200810 VALUES LESS THAN (738012),
PARTITION p20200817 VALUES LESS THAN (738019),
PARTITION p20200824 VALUES LESS THAN (738026),
PARTITION p20200831 VALUES LESS THAN (738033),
PARTITION p20200907 VALUES LESS THAN (738040),
PARTITION p20200914 VALUES LESS THAN (738047),
PARTITION p20200921 VALUES LESS THAN (738054),
PARTITION p20200928 VALUES LESS THAN (738061)
)

SQL：

SELECT IF(COUNT(*) > 0, TRUE, FALSE) FROM (SELECT 1 FROM T_DEVICE_REQUEST WHERE REQ_SEQ = ‘20083117280710624874’ AND MERCHANT_ID = 1477 UNION SELECT 1 FROM T_DEVICE_REQUEST WHERE ORI_SEQ = ‘20083117280710624874’ AND MERCHANT_ID = 1477 ) AS T;

正常是100ms以内，但是偶尔就变得很慢
这个是出问题的时候服务器的状态，时间是17:20那个点：

业务上没有什么突然增加的，性能消耗其实也不高，把这个SQL单独拿出来执行也很快，就是在那个时间点变慢了

另外coprocessor_cpu 有点高：

[yilong (yi888long)]

麻烦反馈下这个时间点的慢日志，是否有记录这个慢sql，多谢。

[Mingdr (会飞的猫)]

记录了，都是这个SQL：

[yilong (yi888long)]

1. table 定义中没有 MERCHANT_ID ？ select 1 的两个 sql 的where 条件查看的值都是唯一的吗？
2. 从底下 mem_max 看感觉是不唯一的，每次内存消耗都不一样
3. 能否试一下多次执行同一个where 值的 explain analyze sql ， 判断下是因为执行计划不同导致的慢，还是说系统负载问题导致的慢，多谢。

[Mingdr (会飞的猫)]

@rongyilong-PingCAP

explain analyze

1：定义了的 我没复制完字段，是有这个字段的，每次merchant_id都不一样。
2：每次条件都不一样，所以内存消耗可能就有差别
3：我多次执行了 ，结果都一样：

Projection_41	1.00	root	if(gt(11_col_0, 0), 1, 0)	time:1.380128ms, loops:2, rows:1	N/A
└─StreamAgg_46	1.00	root	funcs:count(1)	time:1.376805ms, loops:2, rows:1	N/A
└─HashAgg_241	2.00	root	group by:1, funcs:firstrow(1)	time:1.374525ms, loops:2, rows:1	N/A
└─Union_50	0.66	root		time:1.313197ms, loops:2, rows:1	N/A
├─Projection_52	0.63	root	1	time:1.278911ms, loops:2, rows:1	N/A
│ └─Union_53	0.63	root		time:1.271026ms, loops:2, rows:1	N/A
│ ├─IndexLookUp_60	0.04	root		time:635.036µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_57	3.07	cop	table:t_device_request, partition:p20200706, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_59	0.04	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_58	3.07	cop	table:t_device_request, partition:p20200706, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_67	0.00	root		time:744.87µs, loops:1, rows:0	192 Bytes
│ │ ├─IndexScan_64	0.00	cop	table:t_device_request, partition:p20200713, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_66	0.00	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_65	0.00	cop	table:t_device_request, partition:p20200713, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_74	0.00	root		time:633.464µs, loops:1, rows:0	192 Bytes
│ │ ├─IndexScan_71	0.00	cop	table:t_device_request, partition:p20200720, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_73	0.00	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_72	0.00	cop	table:t_device_request, partition:p20200720, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_81	0.03	root		time:491.977µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_78	2.23	cop	table:t_device_request, partition:p20200727, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_80	0.03	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_79	2.23	cop	table:t_device_request, partition:p20200727, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_88	0.10	root		time:501.21µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_85	8.20	cop	table:t_device_request, partition:p20200803, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_87	0.10	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_86	8.20	cop	table:t_device_request, partition:p20200803, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_95	0.08	root		time:481.184µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_92	6.63	cop	table:t_device_request, partition:p20200810, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_94	0.08	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_93	6.63	cop	table:t_device_request, partition:p20200810, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_102	0.02	root		time:642.663µs, loops:1, rows:0	192 Bytes
│ │ ├─IndexScan_99	1.30	cop	table:t_device_request, partition:p20200817, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_101	0.02	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_100	1.30	cop	table:t_device_request, partition:p20200817, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_109	0.10	root		time:703.977µs, loops:1, rows:0	192 Bytes
│ │ ├─IndexScan_106	8.63	cop	table:t_device_request, partition:p20200824, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_108	0.10	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_107	8.63	cop	table:t_device_request, partition:p20200824, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_116	0.00	root		time:675.96µs, loops:1, rows:0	194 Bytes
│ │ ├─IndexScan_113	0.00	cop	table:t_device_request, partition:p20200831, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:1ms, loops:1, rows:0	N/A
│ │ └─Selection_115	0.00	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_114	0.00	cop	table:t_device_request, partition:p20200831, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_123	0.24	root		time:1.250077ms, loops:2, rows:1	8.87890625 KB
│ │ ├─IndexScan_120	21.97	cop	table:t_device_request, partition:p20200907, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:1	N/A
│ │ └─Selection_122	0.24	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0s, loops:1, rows:1	N/A
│ │ └─TableScan_121	21.97	cop	table:t_device_request, partition:p20200907, keep order:false	time:0s, loops:1, rows:1	N/A
│ ├─IndexLookUp_130	0.01	root		time:648.977µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_127	10.00	cop	table:t_device_request, partition:p20200914, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_129	0.01	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_128	10.00	cop	table:t_device_request, partition:p20200914, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_137	0.01	root		time:551.737µs, loops:1, rows:0	192 Bytes
│ │ ├─IndexScan_134	10.00	cop	table:t_device_request, partition:p20200921, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_136	0.01	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_135	10.00	cop	table:t_device_request, partition:p20200921, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A
│ └─IndexLookUp_144	0.01	root		time:544.324µs, loops:1, rows:0	194 Bytes
│ ├─IndexScan_141	10.00	cop	table:t_device_request, partition:p20200928, index:REQ_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false, stats:pseudo	time:1ms, loops:1, rows:0	N/A
│ └─Selection_143	0.01	cop	eq(rds.t_device_request.merchant_id, 1477)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_142	10.00	cop	table:t_device_request, partition:p20200928, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A
└─Projection_146	0.03	root	1	time:719.791µs, loops:1, rows:0	N/A
└─Union_147	0.03	root		time:700.886µs, loops:1, rows:0	N/A
├─IndexLookUp_154	0.00	root		time:568.103µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_151	0.00	cop	table:t_device_request, partition:p20200706, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_153	0.00	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_152	0.00	cop	table:t_device_request, partition:p20200706, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_161	0.00	root		time:587.319µs, loops:1, rows:0	192 Bytes
│ ├─IndexScan_158	0.00	cop	table:t_device_request, partition:p20200713, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_160	0.00	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_159	0.00	cop	table:t_device_request, partition:p20200713, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_168	0.00	root		time:554.214µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_165	1.04	cop	table:t_device_request, partition:p20200720, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_167	0.00	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_166	1.04	cop	table:t_device_request, partition:p20200720, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_175	0.00	root		time:506.604µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_172	0.00	cop	table:t_device_request, partition:p20200727, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_174	0.00	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_173	0.00	cop	table:t_device_request, partition:p20200727, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_182	0.00	root		time:429.559µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_179	0.00	cop	table:t_device_request, partition:p20200803, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_181	0.00	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_180	0.00	cop	table:t_device_request, partition:p20200803, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_189	0.00	root		time:538.57µs, loops:1, rows:0	194 Bytes
│ ├─IndexScan_186	5.30	cop	table:t_device_request, partition:p20200810, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:1ms, loops:1, rows:0	N/A
│ └─Selection_188	0.00	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_187	5.30	cop	table:t_device_request, partition:p20200810, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_196	0.00	root		time:527.192µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_193	3.90	cop	table:t_device_request, partition:p20200817, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_195	0.00	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_194	3.90	cop	table:t_device_request, partition:p20200817, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_203	0.00	root		time:532.157µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_200	12.94	cop	table:t_device_request, partition:p20200824, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_202	0.00	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_201	12.94	cop	table:t_device_request, partition:p20200824, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_210	0.00	root		time:550.658µs, loops:1, rows:0	194 Bytes
│ ├─IndexScan_207	0.00	cop	table:t_device_request, partition:p20200831, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:1ms, loops:1, rows:0	N/A
│ └─Selection_209	0.00	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_208	0.00	cop	table:t_device_request, partition:p20200831, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_217	0.00	root		time:616.324µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_214	0.00	cop	table:t_device_request, partition:p20200907, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_216	0.00	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_215	0.00	cop	table:t_device_request, partition:p20200907, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_224	0.01	root		time:604.757µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_221	10.00	cop	table:t_device_request, partition:p20200914, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
│ └─Selection_223	0.01	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_222	10.00	cop	table:t_device_request, partition:p20200914, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_231	0.01	root		time:549.601µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_228	10.00	cop	table:t_device_request, partition:p20200921, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
│ └─Selection_230	0.01	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_229	10.00	cop	table:t_device_request, partition:p20200921, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A
└─IndexLookUp_238	0.01	root		time:419.569µs, loops:1, rows:0	190 Bytes
├─IndexScan_235	10.00	cop	table:t_device_request, partition:p20200928, index:ORI_SEQ, range:[“20083117280710624874”,“20083117280710624874”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
└─Selection_237	0.01	cop	eq(rds.t_device_request.merchant_id, 1473)	time:0ns, loops:0, rows:0	N/A
└─TableScan_236	10.00	cop	table:t_device_request, partition:p20200928, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A

[yilong (yi888long)]

根据您的反馈，这个执行计划的时间是 1.38ms，麻烦您反馈下这个where 条件对应的值慢日志中的详细信息，多谢。

[Mingdr (会飞的猫)]

slow.xls (1.1 MB)

这个就是刚刚的慢日志，里面也有那个SQL

[Mingdr (会飞的猫)]

我发现其他慢SQL的cop_wait_max都很低，就这个慢SQL的cop_wait_max很高

[yilong (yi888long)]

1. 我看了下都是 wait time 比较长， 官网的说明：
   Wait_time ：表示这个语句在 TiKV 的等待时间之和，因为 TiKV 的 Coprocessor 线程数是有限的，当所有的 Coprocessor 线程都在工作的时候，请求会排队；当队列中有某些请求耗时很长的时候，后面的请求的等待时间都会增加
   

   

2. 上面的值我没有找到，麻烦重新反馈下 SELECT IF(COUNT(*) > 0, TRUE, FALSE) FROM (SELECT 1 FROM T_DEVICE_REQUEST WHERE REQ_SEQ = ‘20200901141854749797’ AND MERCHANT_ID = 1837 UNION SELECT 1 FROM T_DEVICE_REQUEST WHERE ORI_SEQ = ‘20200901141854749797’ AND MERCHANT_ID = 1837 ) AS T; 这个 sql 的 explain analyze sql 信息。

3. 看起来大概率是 cop 繁忙导致的，麻烦反馈下 over-view，tidb，detail-tikv 问题发生时的监控

(1)、chrome 安装这个插件https://chrome.google.com/webstore/detail/full-page-screen-capture/fdpohaocaechififmbbbbbknoalclacl

(2)、鼠标焦点置于 Dashboard 上，按 ?可显示所有快捷键，先按 d 再按 E 可将所有 Rows 的 Panels 打开，需等待一段时间待页面加载完成。

(3)、使用这个 full-page-screen-capture 插件进行截屏保存

[Mingdr (会飞的猫)]

@rongyilong-PingCAP

https://chrome.google.com/webstore/detail/full-page-screen-capture/fdpohaocaechififmbbbbbknoalclacl

detail-tikv：

over-view：

tidb：

2：

Projection_41	1.00	root	if(gt(11_col_0, 0), 1, 0)	time:2.518686ms, loops:2, rows:1	N/A
└─StreamAgg_46	1.00	root	funcs:count(1)	time:2.513509ms, loops:2, rows:1	N/A
└─HashAgg_241	2.00	root	group by:1, funcs:firstrow(1)	time:2.508477ms, loops:2, rows:1	N/A
└─Union_50	0.11	root		time:2.432156ms, loops:2, rows:1	N/A
├─Projection_52	0.07	root	1	time:2.40357ms, loops:2, rows:1	N/A
│ └─Union_53	0.07	root		time:2.392096ms, loops:2, rows:1	N/A
│ ├─IndexLookUp_60	0.00	root		time:785.289µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_57	0.00	cop	table:t_device_request, partition:p20200706, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_59	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_58	0.00	cop	table:t_device_request, partition:p20200706, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_67	0.00	root		time:672.996µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_64	1.28	cop	table:t_device_request, partition:p20200713, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_66	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_65	1.28	cop	table:t_device_request, partition:p20200713, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_74	0.00	root		time:582.192µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_71	2.09	cop	table:t_device_request, partition:p20200720, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_73	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_72	2.09	cop	table:t_device_request, partition:p20200720, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_81	0.00	root		time:637.846µs, loops:1, rows:0	192 Bytes
│ │ ├─IndexScan_78	5.57	cop	table:t_device_request, partition:p20200727, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_80	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_79	5.57	cop	table:t_device_request, partition:p20200727, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_88	0.00	root		time:722.355µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_85	4.10	cop	table:t_device_request, partition:p20200803, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_87	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_86	4.10	cop	table:t_device_request, partition:p20200803, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_95	0.00	root		time:749.494µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_92	2.65	cop	table:t_device_request, partition:p20200810, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_94	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_93	2.65	cop	table:t_device_request, partition:p20200810, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_102	0.01	root		time:818.558µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_99	6.50	cop	table:t_device_request, partition:p20200817, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_101	0.01	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_100	6.50	cop	table:t_device_request, partition:p20200817, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_109	0.01	root		time:730.73µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_106	7.55	cop	table:t_device_request, partition:p20200824, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_108	0.01	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_107	7.55	cop	table:t_device_request, partition:p20200824, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_116	0.01	root		time:642.961µs, loops:1, rows:0	192 Bytes
│ │ ├─IndexScan_113	18.19	cop	table:t_device_request, partition:p20200831, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_115	0.01	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_114	18.19	cop	table:t_device_request, partition:p20200831, keep order:false	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_123	0.00	root		time:2.33367ms, loops:2, rows:1	8.87890625 KB
│ │ ├─IndexScan_120	2.44	cop	table:t_device_request, partition:p20200907, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:1	N/A
│ │ └─Selection_122	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0s, loops:1, rows:1	N/A
│ │ └─TableScan_121	2.44	cop	table:t_device_request, partition:p20200907, keep order:false	time:0s, loops:1, rows:1	N/A
│ ├─IndexLookUp_130	0.01	root		time:786.411µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_127	10.00	cop	table:t_device_request, partition:p20200914, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_129	0.01	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_128	10.00	cop	table:t_device_request, partition:p20200914, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A
│ ├─IndexLookUp_137	0.01	root		time:730.406µs, loops:1, rows:0	190 Bytes
│ │ ├─IndexScan_134	10.00	cop	table:t_device_request, partition:p20200921, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
│ │ └─Selection_136	0.01	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ │ └─TableScan_135	10.00	cop	table:t_device_request, partition:p20200921, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A
│ └─IndexLookUp_144	0.01	root		time:660.515µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_141	10.00	cop	table:t_device_request, partition:p20200928, index:REQ_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
│ └─Selection_143	0.01	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_142	10.00	cop	table:t_device_request, partition:p20200928, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A
└─Projection_146	0.04	root	1	time:906.34µs, loops:1, rows:0	N/A
└─Union_147	0.04	root		time:893.998µs, loops:1, rows:0	N/A
├─IndexLookUp_154	0.00	root		time:842.02µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_151	0.00	cop	table:t_device_request, partition:p20200706, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_153	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_152	0.00	cop	table:t_device_request, partition:p20200706, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_161	0.00	root		time:747.34µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_158	0.00	cop	table:t_device_request, partition:p20200713, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_160	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_159	0.00	cop	table:t_device_request, partition:p20200713, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_168	0.00	root		time:623.463µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_165	0.00	cop	table:t_device_request, partition:p20200720, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_167	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_166	0.00	cop	table:t_device_request, partition:p20200720, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_175	0.00	root		time:513.449µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_172	1.11	cop	table:t_device_request, partition:p20200727, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_174	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_173	1.11	cop	table:t_device_request, partition:p20200727, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_182	0.00	root		time:604.079µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_179	1.02	cop	table:t_device_request, partition:p20200803, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_181	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_180	1.02	cop	table:t_device_request, partition:p20200803, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_189	0.01	root		time:554.769µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_186	9.28	cop	table:t_device_request, partition:p20200810, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_188	0.01	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_187	9.28	cop	table:t_device_request, partition:p20200810, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_196	0.00	root		time:628.289µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_193	5.20	cop	table:t_device_request, partition:p20200817, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_195	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_194	5.20	cop	table:t_device_request, partition:p20200817, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_203	0.00	root		time:583.14µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_200	0.00	cop	table:t_device_request, partition:p20200824, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_202	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_201	0.00	cop	table:t_device_request, partition:p20200824, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_210	0.00	root		time:525.774µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_207	0.00	cop	table:t_device_request, partition:p20200831, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_209	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_208	0.00	cop	table:t_device_request, partition:p20200831, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_217	0.00	root		time:712.267µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_214	0.00	cop	table:t_device_request, partition:p20200907, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false	time:0s, loops:1, rows:0	N/A
│ └─Selection_216	0.00	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_215	0.00	cop	table:t_device_request, partition:p20200907, keep order:false	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_224	0.01	root		time:648.46µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_221	10.00	cop	table:t_device_request, partition:p20200914, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
│ └─Selection_223	0.01	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_222	10.00	cop	table:t_device_request, partition:p20200914, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A
├─IndexLookUp_231	0.01	root		time:602.632µs, loops:1, rows:0	190 Bytes
│ ├─IndexScan_228	10.00	cop	table:t_device_request, partition:p20200921, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
│ └─Selection_230	0.01	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
│ └─TableScan_229	10.00	cop	table:t_device_request, partition:p20200921, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A
└─IndexLookUp_238	0.01	root		time:502.397µs, loops:1, rows:0	190 Bytes
├─IndexScan_235	10.00	cop	table:t_device_request, partition:p20200928, index:ORI_SEQ, range:[“20200901141854749797”,“20200901141854749797”], keep order:false, stats:pseudo	time:0s, loops:1, rows:0	N/A
└─Selection_237	0.01	cop	eq(rds.t_device_request.merchant_id, 1837)	time:0ns, loops:0, rows:0	N/A
└─TableScan_236	10.00	cop	table:t_device_request, partition:p20200928, keep order:false, stats:pseudo	time:0s, loops:0, rows:0	N/A

[yilong (yi888long)]

1. 感谢反馈，从您反馈的执行计划看，这个sql应该只需要2ms 多

2. 从监控分析
   （1） 慢日志中此条sql的时间为 2020-09-01 14:19:06.042622
   （2） 查看 tidb 监控看到这个时间点 duration 有升高，和慢日志时间匹配
   

   
   
   （3） 查看 kv requestion duration，主要是 store 6 慢
   
   
   
   （4） 主要是 cop 慢，这点和慢日志中 wait duration长是匹配的
   
   

3. 麻烦帮忙确认下 store 6 是哪个 tikv ，另外 detail-tikv 第一个图看不清楚，麻烦重新采集下。
   store 6 的 node_exporter 和 disk performance 监控麻烦也取下，多谢。

4. 在 dashboard 的慢日志界面，选好这个时间段 半小时吧，按照运行时间和内存排序看看，有没有什么大 sql
   

   

[Mingdr (会飞的猫)]

3：

106：

这个是那个时间段的慢SQL：

[yilong (yi888long)]

1. detail-tikv 中 coprocessor cpu 和 raft store cpu 信息
   
   
   
   
   
2. propose wait duration per server 可以看到 106 稍高，这里可以查看 999 才能和 tidb 监控匹配
   
   
3. 查看 coprocessor over-view request duration 主要是 index
   
   
4. 查看 coprocessor detail ， wati duration 中 index 和select 都达到 1s
   
   
   
   
   

[Mingdr (会飞的猫)]

这些我在监控里面看到了，就是不知道是什么原因造成的，因为这个SQL执行频率高，并不是每次都会出现慢SQL，另外以前出现这种情况的时候，也不止106那台kv，可能是其他的kv节点

[Mingdr (会飞的猫)]

这种情况我在本地测试，也是出现了，感觉是分区的原因造成的性能抖动

[yilong (yi888long)]

嗯，还在分析，只是先记录了进展，多谢。

[yilong (yi888long)]

1. 目前来看只能分析到是 index scan 和 table scan 占用了大量 coprocessor，3.x 版本没有 dashboard，没法具体分析，请问是否方便升级到 4.0的最新版本，之后我们看一下具体执行计划的哪个部分慢了，多谢。

2. 如果不想升级，您这边可以手工检查下业务 sql ， 有哪些sql频繁update，insert，delete这些索引
   PRIMARY KEY (REQUEST_ID,REQUEST_TIME),
   KEY INDEX_REQUEST_ORI_SEQ (ORI_SEQ),
   KEY INDEX_REQUEST_REQ_SEQ (REQ_SEQ),
   KEY INDEX_REQUEST_TIME (REQUEST_TIME)

[Mingdr (会飞的猫)]

4.0我测出了两个比较严重的问题，一个是多表联查报错，一个是大表排序报错，所以现在还不敢升级，不过我本地有复现这个问题，我贴出来

[yilong (yi888long)]

1. 好的，这个问题我们再看下
2. 您说的两个严重问题是什么？ 是否已经提交过其他帖子？ 如果没有，方便再开新帖吗？多谢。

[Mingdr (会飞的猫)]

还在整理，已经反馈了一个了