[Help] SQL query condition's time interval increased by one month, but TiKV execution time increased tenfold

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 【求助】SQL 查询条件的时间间隔增加一个月，TiKV 执行时间却变成10倍

| username: just_coder

To improve efficiency, please provide the following information. Clear problem descriptions can be resolved more quickly:
【TiDB Usage Environment】

【Overview】 Scenario + Problem Overview
Query condition changed from v.recdate>=‘2022-01-01’ and v.recdate<=‘2022-07-01’ to v.recdate>=‘2021-12-01’ and v.recdate<=‘2022-07-01’, resulting in a significant decrease in speed.

【Background】 Operations performed

【Phenomenon】 Business and database phenomena
TiKV querying data within a certain time interval

【Problem】 Current issue encountered

【Business Impact】
The time interval in the date query condition of the SQL statement increased by one month, and the query speed slowed down by 10 times.
The slow query log is as follows:

image
image1571×480 15.8 KB

【TiDB Version】 V5.4.1

【Application Software and Version】

【Attachments】 Relevant logs and configuration information

  • TiUP Cluster Display information
  • TiUP Cluster Edit config information

Monitoring (https://metricstool.pingcap.com/)

  • TiDB-Overview Grafana monitoring
  • TiDB Grafana monitoring
  • TiKV Grafana monitoring
  • PD Grafana monitoring
  • Corresponding module logs (including logs 1 hour before and after the issue)

If the question is related to performance optimization or fault troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output results for upload.

2
| username: just_coder | Original post link 
id                           	task     	estRows   	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                  	memory  	disk
Projection_7                 	root     	456.49    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, Column#98, Column#99, Column#100, Column#101, Column#102, Column#103, Column#104, Column#105, Column#106, Column#107, Column#108, Column#109, Column#110, Column#111, Column#112, Column#113, Column#114, Column#115, Column#93, Column#94, Column#95, Column#96, Column#97                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	136331 	time:2.13s, loops:135, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                          	7.65 MB 	N/A
└─Sort_8                     	root     	456.49    	yd_194_result.feature_dev_moment.payment:desc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	136331 	time:2.11s, loops:135                                                                                                                                                                                                                                                                                                                                                           	207.5 MB	0 Bytes
  └─Projection_10            	root     	456.49    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, 0->Column#98, 0->Column#99, 0->Column#100, 0->Column#101, 0->Column#102, 0->Column#103, 0->Column#104, 0->Column#105, 0->Column#106, 0->Column#107, 0->Column#108, 0->Column#109, 0->Column#110, 0->Column#111, 0->Column#112, 0->Column#113, 0->Column#114, 0->Column#115, Column#93, Column#94, Column#95, Column#96, Column#97, yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	136331 	time:1.3s, loops:136, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                           	178.4 MB	N/A
    └─HashAgg_29             	root     	456.49    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(Column#236)->Column#66, funcs:sum(Column#237)->Column#67, funcs:sum(Column#238)->Column#68, funcs:sum(Column#239)->Column#69, funcs:sum(Column#240)->Column#70, funcs:sum(Column#241)->Column#71, funcs:sum(Column#242)->Column#72, funcs:sum(Column#243)->Column#73, funcs:sum(Column#244)->Column#74, funcs:sum(Column#245)->Column#75, funcs:sum(Column#246)->Column#76, funcs:sum(Column#247)->Column#77, funcs:sum(Column#248)->Column#78, funcs:sum(Column#249)->Column#79, funcs:sum(Column#250)->Column#80, funcs:sum(Column#251)->Column#81, funcs:sum(Column#252)->Column#82, funcs:sum(Column#253)->Column#83, funcs:sum(Column#254)->Column#84, funcs:sum(Column#255)->Column#85, funcs:sum(Column#256)->Column#86, funcs:sum(Column#257)->Column#87, funcs:sum(Column#258)->Column#88, funcs:sum(Column#259)->Column#89, funcs:sum(Column#260)->Column#90, funcs:sum(Column#261)->Column#91, funcs:sum(Column#262)->Column#92, funcs:sum(Column#263)->Column#93, funcs:sum(Column#264)->Column#94, funcs:sum(Column#265)->Column#95, funcs:sum(Column#266)->Column#96, funcs:sum(Column#267)->Column#97, funcs:firstrow(yd_194_result.feature_dev_moment.recdate)->yd_194_result.feature_dev_moment.recdate, funcs:firstrow(Column#269)->yd_194_result.feature_dev_moment.osid, funcs:firstrow(yd_194_result.feature_dev_moment.gameid)->yd_194_result.feature_dev_moment.gameid, funcs:firstrow(yd_194_result.feature_dev_moment.cid)->yd_194_result.feature_dev_moment.cid, funcs:firstrow(Column#272)->yd_194_result.feature_dev_moment.aid, funcs:firstrow(Column#273)->yd_194_result.feature_dev_moment.oid, funcs:firstrow(yd_194_result.feature_dev_moment.pt)->yd_194_result.feature_dev_moment.pt, funcs:firstrow(Column#275)->yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         	136331 	time:1.26s, loops:136, partial_worker:{wall_time:451.107219ms, concurrency:5, task_num:134, tot_wait:261.029605ms, tot_exec:1.802961541s, tot_time:2.189461206s, max:450.915149ms, p95:450.915149ms}, final_worker:{wall_time:1.310306374s, concurrency:5, task_num:25, tot_wait:2.103021226s, tot_exec:4.322647411s, tot_time:6.425723777s, max:1.310116859s, p95:1.310116859s}	503.9 MB	N/A
      └─IndexLookUp_30       	root     	456.49    	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	136580 	time:355.2ms, loops:135, index_task: {total_time: 290.7ms, fetch_handle: 236.1ms, build: 365.4µs, wait: 54.2ms}, table_task: {total_time: 1.45s, num: 91, concurrency: 5}                                                                                                                                                                                                      	61.0 MB 	N/A
        ├─Selection_25       	cop[tikv]	2110794.50	not(in(yd_194_result.feature_dev_moment.gameid, "1604385436956830", "1621669402391940", "1632711274294320", "1636099609615800", "1639364435557330", "1639126762567720", "1616571507848110", "1639110127366330", "1640159205240000", "1633765571609960"))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	1783943	time:7.57ms, loops:1928, cop_task: {num: 4, max: 2.22ms, min: 1.75ms, avg: 1.88ms, p95: 2.22ms, rpc_num: 4, rpc_time: 7.38ms, copr_cache_hit_ratio: 1.00}, tikv_task:{proc max:1.34s, min:515ms, p80:1.34s, p95:1.34s, iters:2148, tasks:4}                                                                                                                                     	N/A     	N/A
        │ └─IndexRangeScan_23	cop[tikv]	2150268.84	table:v, index:PRIMARY(recdate, osid, gameid, cid, aid, oid, pt, moment), range:[2022-01-01,2022-07-01], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	2181886	tikv_task:{proc max:1.11s, min:419ms, p80:1.11s, p95:1.11s, iters:2148, tasks:4}                                                                                                                                                                                                                                                                                                	N/A     	N/A
        └─HashAgg_12         	cop[tikv]	456.49    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(yd_194_result.feature_dev_moment.installcount)->Column#236, funcs:sum(yd_194_result.feature_dev_moment.regdevcount)->Column#237, funcs:sum(yd_194_result.feature_dev_moment.newdevcount)->Column#238, funcs:sum(yd_194_result.feature_dev_moment.newdidrolecount)->Column#239, funcs:sum(yd_194_result.feature_dev_moment.didrolecount)->Column#240, funcs:sum(yd_194_result.feature_dev_moment.reglogincount)->Column#241, funcs:sum(yd_194_result.feature_dev_moment.newlogincount)->Column#242, funcs:sum(yd_194_result.feature_dev_moment.newuserrolecount)->Column#243, funcs:sum(yd_194_result.feature_dev_moment.userrolecount)->Column#244, funcs:sum(yd_194_result.feature_dev_moment.userlive1)->Column#245, funcs:sum(yd_194_result.feature_dev_moment.userlive3)->Column#246, funcs:sum(yd_194_result.feature_dev_moment.userlive4)->Column#247, funcs:sum(yd_194_result.feature_dev_moment.userlive5)->Column#248, funcs:sum(yd_194_result.feature_dev_moment.userlive6)->Column#249, funcs:sum(yd_194_result.feature_dev_moment.userlive7)->Column#250, funcs:sum(yd_194_result.feature_dev_moment.userlive8)->Column#251, funcs:sum(yd_194_result.feature_dev_moment.userlive9)->Column#252, funcs:sum(yd_194_result.feature_dev_moment.userlive10)->Column#253, funcs:sum(yd_194_result.feature_dev_moment.userlive11)->Column#254, funcs:sum(yd_194_result.feature_dev_moment.userlive12)->Column#255, funcs:sum(yd_194_result.feature_dev_moment.userlive13)->Column#256, funcs:sum(yd_194_result.feature_dev_moment.userlive14)->Column#257, funcs:sum(yd_194_result.feature_dev_moment.userlive15)->Column#258, funcs:sum(yd_194_result.feature_dev_moment.userlive16)->Column#259, funcs:sum(yd_194_result.feature_dev_moment.userlive30)->Column#260, funcs:sum(yd_194_result.feature_dev_moment.userlive60)->Column#261, funcs:sum(yd_194_result.feature_dev_moment.userlive90)->Column#262, funcs:sum(yd_194_result.feature_dev_moment.newuserincome)->Column#263, funcs:sum(yd_194_result.feature_dev_moment.newuserpaypeople)->Column#264, funcs:sum(yd_194_result.feature_dev_moment.todayincome)->Column#265, funcs:sum(yd_194_result.feature_dev_moment.todaypaypeople)->Column#266, funcs:sum(yd_194_result.feature_dev_moment.todaypay6people)->Column#267, funcs:firstrow(yd_194_result.feature_dev_moment.osid)->Column#269, funcs:firstrow(yd_194_result.feature_dev_moment.aid)->Column#272, funcs:firstrow(yd_194_result.feature_dev_moment.oid)->Column#273, funcs:firstrow(yd_194_result.feature_dev_moment.payment)->Column#275	136580 	time:318.2ms, loops:193, cop_task: {num: 102, max: 5.09ms, min: 1.43ms, avg: 1.88ms, p95: 2.2ms, rpc_num: 102, rpc_time: 188.1ms, copr_cache_hit_ratio: 0.99}, tikv_task:{proc max:562ms, min:26ms, p80:417ms, p95:530ms, iters:1792, tasks:102}                                                                                                                                	N/A     	N/A
          └─TableRowIDScan_24	cop[tikv]	2110794.50	table:v, keep order:false
3
| username: just_coder | Original post link 
id                           	task     	estRows   	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        	memory  	disk
Projection_7                 	root     	531.57    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, Column#98, Column#99, Column#100, Column#101, Column#102, Column#103, Column#104, Column#105, Column#106, Column#107, Column#108, Column#109, Column#110, Column#111, Column#112, Column#113, Column#114, Column#115, Column#93, Column#94, Column#95, Column#96, Column#97                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	160649 	time:45.3s, loops:158, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	7.65 MB 	N/A
└─Sort_8                     	root     	531.57    	yd_194_result.feature_dev_moment.payment:desc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	160649 	time:45.2s, loops:158                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	237.6 MB	296.8 MB
  └─Projection_10            	root     	531.57    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, 0->Column#98, 0->Column#99, 0->Column#100, 0->Column#101, 0->Column#102, 0->Column#103, 0->Column#104, 0->Column#105, 0->Column#106, 0->Column#107, 0->Column#108, 0->Column#109, 0->Column#110, 0->Column#111, 0->Column#112, 0->Column#113, 0->Column#114, 0->Column#115, Column#93, Column#94, Column#95, Column#96, Column#97, yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	160649 	time:7.01s, loops:161, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	209.5 MB	N/A
    └─HashAgg_29             	root     	531.57    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(Column#236)->Column#66, funcs:sum(Column#237)->Column#67, funcs:sum(Column#238)->Column#68, funcs:sum(Column#239)->Column#69, funcs:sum(Column#240)->Column#70, funcs:sum(Column#241)->Column#71, funcs:sum(Column#242)->Column#72, funcs:sum(Column#243)->Column#73, funcs:sum(Column#244)->Column#74, funcs:sum(Column#245)->Column#75, funcs:sum(Column#246)->Column#76, funcs:sum(Column#247)->Column#77, funcs:sum(Column#248)->Column#78, funcs:sum(Column#249)->Column#79, funcs:sum(Column#250)->Column#80, funcs:sum(Column#251)->Column#81, funcs:sum(Column#252)->Column#82, funcs:sum(Column#253)->Column#83, funcs:sum(Column#254)->Column#84, funcs:sum(Column#255)->Column#85, funcs:sum(Column#256)->Column#86, funcs:sum(Column#257)->Column#87, funcs:sum(Column#258)->Column#88, funcs:sum(Column#259)->Column#89, funcs:sum(Column#260)->Column#90, funcs:sum(Column#261)->Column#91, funcs:sum(Column#262)->Column#92, funcs:sum(Column#263)->Column#93, funcs:sum(Column#264)->Column#94, funcs:sum(Column#265)->Column#95, funcs:sum(Column#266)->Column#96, funcs:sum(Column#267)->Column#97, funcs:firstrow(yd_194_result.feature_dev_moment.recdate)->yd_194_result.feature_dev_moment.recdate, funcs:firstrow(Column#269)->yd_194_result.feature_dev_moment.osid, funcs:firstrow(yd_194_result.feature_dev_moment.gameid)->yd_194_result.feature_dev_moment.gameid, funcs:firstrow(yd_194_result.feature_dev_moment.cid)->yd_194_result.feature_dev_moment.cid, funcs:firstrow(Column#272)->yd_194_result.feature_dev_moment.aid, funcs:firstrow(Column#273)->yd_194_result.feature_dev_moment.oid, funcs:firstrow(yd_194_result.feature_dev_moment.pt)->yd_194_result.feature_dev_moment.pt, funcs:firstrow(Column#275)->yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         	160649 	time:6.96s, loops:161, partial_worker:{wall_time:6.018231429s, concurrency:5, task_num:158, tot_wait:27.712163989s, tot_exec:2.180316903s, tot_time:30.01636946s, max:6.018077734s, p95:6.018077734s}, final_worker:{wall_time:10.131924536s, concurrency:5, task_num:25, tot_wait:29.989720826s, tot_exec:11.264212713s, tot_time:41.25398566s, max:10.131719169s, p95:10.131719169s}                                                                                                                                                                                	591.0 MB	N/A
      └─IndexLookUp_30       	root     	531.57    	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	160926 	time:5.96s, loops:159, index_task: {total_time: 5.45s, fetch_handle: 282.7ms, build: 751.6µs, wait: 5.17s}, table_task: {total_time: 29.3s, num: 107, concurrency: 5}                                                                                                                                                                                                                                                                                                                                                                                                	40.5 MB 	N/A
        ├─Selection_25       	cop[tikv]	2457958.79	not(in(yd_194_result.feature_dev_moment.gameid, "1604385436956830", "1621669402391940", "1632711274294320", "1636099609615800", "1639364435557330", "1639126762567720", "1616571507848110", "1639110127366330", "1640159205240000", "1633765571609960"))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	2112984	time:10.6ms, loops:2250, cop_task: {num: 4, max: 4.49ms, min: 4ms, avg: 4.18ms, p95: 4.49ms, rpc_num: 4, rpc_time: 16.4ms, copr_cache_hit_ratio: 1.00}, tikv_task:{proc max:1.69s, min:515ms, p80:1.69s, p95:1.69s, iters:2475, tasks:4}                                                                                                                                                                                                                                                                                                                              	N/A     	N/A
        │ └─IndexRangeScan_23	cop[tikv]	2503925.50	table:v, index:PRIMARY(recdate, osid, gameid, cid, aid, oid, pt, moment), range:[2021-12-01,2022-07-01], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	2516881	tikv_task:{proc max:1.41s, min:419ms, p80:1.41s, p95:1.41s, iters:2475, tasks:4}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      	N/A     	N/A
        └─HashAgg_12         	cop[tikv]	531.57    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(yd_194_result.feature_dev_moment.installcount)->Column#236, funcs:sum(yd_194_result.feature_dev_moment.regdevcount)->Column#237, funcs:sum(yd_194_result.feature_dev_moment.newdevcount)->Column#238, funcs:sum(yd_194_result.feature_dev_moment.newdidrolecount)->Column#239, funcs:sum(yd_194_result.feature_dev_moment.didrolecount)->Column#240, funcs:sum(yd_194_result.feature_dev_moment.reglogincount)->Column#241, funcs:sum(yd_194_result.feature_dev_moment.newlogincount)->Column#242, funcs:sum(yd_194_result.feature_dev_moment.newuserrolecount)->Column#243, funcs:sum(yd_194_result.feature_dev_moment.userrolecount)->Column#244, funcs:sum(yd_194_result.feature_dev_moment.userlive1)->Column#245, funcs:sum(yd_194_result.feature_dev_moment.userlive3)->Column#246, funcs:sum(yd_194_result.feature_dev_moment.userlive4)->Column#247, funcs:sum(yd_194_result.feature_dev_moment.userlive5)->Column#248, funcs:sum(yd_194_result.feature_dev_moment.userlive6)->Column#249, funcs:sum(yd_194_result.feature_dev_moment.userlive7)->Column#250, funcs:sum(yd_194_result.feature_dev_moment.userlive8)->Column#251, funcs:sum(yd_194_result.feature_dev_moment.userlive9)->Column#252, funcs:sum(yd_194_result.feature_dev_moment.userlive10)->Column#253, funcs:sum(yd_194_result.feature_dev_moment.userlive11)->Column#254, funcs:sum(yd_194_result.feature_dev_moment.userlive12)->Column#255, funcs:sum(yd_194_result.feature_dev_moment.userlive13)->Column#256, funcs:sum(yd_194_result.feature_dev_moment.userlive14)->Column#257, funcs:sum(yd_194_result.feature_dev_moment.userlive15)->Column#258, funcs:sum(yd_194_result.feature_dev_moment.userlive16)->Column#259, funcs:sum(yd_194_result.feature_dev_moment.userlive30)->Column#260, funcs:sum(yd_194_result.feature_dev_moment.userlive60)->Column#261, funcs:sum(yd_194_result.feature_dev_moment.userlive90)->Column#262, funcs:sum(yd_194_result.feature_dev_moment.newuserincome)->Column#263, funcs:sum(yd_194_result.feature_dev_moment.newuserpaypeople)->Column#264, funcs:sum(yd_194_result.feature_dev_moment.todayincome)->Column#265, funcs:sum(yd_194_result.feature_dev_moment.todaypaypeople)->Column#266, funcs:sum(yd_194_result.feature_dev_moment.todaypay6people)->Column#267, funcs:firstrow(yd_194_result.feature_dev_moment.osid)->Column#269, funcs:firstrow(yd_194_result.feature_dev_moment.aid)->Column#272, funcs:firstrow(yd_194_result.feature_dev_moment.oid)->Column#273, funcs:firstrow(yd_194_result.feature_dev_moment.payment)->Column#275	160926 	time:27.9s, loops:229, cop_task: {num: 122, max: 678.9ms, min: 1.53ms, avg: 236.4ms, p95: 532.1ms, max_proc_keys: 21001, p95_proc_keys: 20897, tot_proc: 27.2s, tot_wait: 44ms, rpc_num: 122, rpc_time: 28.8s, copr_cache_hit_ratio: 0.37}, tikv_task:{proc max:638ms, min:27ms, p80:438ms, p95:524ms, iters:2120, tasks:122}, scan_detail: {total_process_keys: 1341314, total_process_keys_size: 659446998, total_keys: 1738600, rocksdb: {delete_skipped_count: 0, key_skipped_count: 3078006, block: {cache_hit_count: 15282, read_count: 0, read_byte: 0 Bytes}}}	N/A     	N/A
          └─TableRowIDScan_24	cop[tikv]	2457958.79	table:v, keep order:false
4
| username: just_coder | Original post link

After comparing the execution plans, it was found that the query speed has significantly decreased. In the queries with long execution times, both tot_wait and rpc_time are very long, and the copr_cache_hit_ratio hit rate is also very low. What should be the next step to troubleshoot the issue?

5
| username: Christophe | Original post link

How many TiKV nodes are there?

6
| username: HACK | Original post link

Check if there is a significant discrepancy between the scanned keys and the time range with the shortest execution time.

7
| username: just_coder | Original post link

3 TiKV nodes

image
image865×137 2.54 KB

8
| username: just_coder | Original post link

Execution time is short

Screenshot1
Screenshot1973×747 32.3 KB

Slow query

Screenshot2
Screenshot2999×543 24 KB

9
| username: h5n1 | Original post link

Is this slow SQL query slow every time it is executed?

10
| username: just_coder | Original post link

Yes, each query is very slow, and checking the slow log reveals that the Cop_time value is very high.

11
| username: just_coder | Original post link

What is currently certain is that as long as the time interval in the query condition reaches 7 months, the query time will change from around 4 seconds for 6 months to 40-50 seconds. However, checking the CPU and memory consumption of TiKV is not high.

12
| username: h5n1 | Original post link

Please provide the output of the SHOW CREATE TABLE statement.

13
| username: 张雨齐0720 | Original post link

How much data is there in that month? Also, which TiKV nodes are the regions stored in? Is it possible that the data volume for that month is too large and the storage nodes are relatively concentrated?

14
| username: just_coder | Original post link

| feature_dev_moment | CREATE TABLE feature_dev_moment (
recdate date NOT NULL COMMENT ‘Date’,
osid smallint(6) NOT NULL COMMENT ‘Operating System Version 1-iOS 2-Android’,
gameid varchar(255) NOT NULL DEFAULT ‘’,
cid varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘Channel ID’,
aid varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘Ad Slot ID’,
oid varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘Landing Page ID’,
pt int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Platform ID’,
moment varchar(5) NOT NULL DEFAULT ‘0’ COMMENT ‘Moment’,
payment decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Investment’,
installCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of Installed Devices - Activated Devices’,
regLoginCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of Registered Accounts (Platform Login)’,
regDevCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of Registered Devices (Platform Login)’,
newLoginCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of New Login Accounts’,
newDevCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of New Login Devices’,
loginCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Total Logins’,
newUserRoleCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of Accounts Creating Roles on the Day’,
userRoleCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Total Number of Accounts Creating Roles’,
didNewCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of New Login Devices (First Login of the Day Deduplicated)’,
newUserIncome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Recharge Amount on the Day’,
newUserPaypeople int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Recharging on the Day’,
todayPay6people int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Recharging 6 Yuan to Date’,
todayIncome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Recharge Amount to Date’,
todayPaypeople int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Recharging to Date’,
newUserIncome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Recharge Amount on the Day - After Accounting’,
todayIncome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Recharge Amount to Date - After Accounting’,
userLive1 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the Next Day’,
userLive3 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 3rd Day’,
userLive4 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 4th Day’,
userLive5 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 5th Day’,
userLive6 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 6th Day’,
userLive7 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 7th Day’,
userLive8 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 8th Day’,
userLive9 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 9th Day’,
userLive10 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 10th Day’,
userLive11 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 11th Day’,
userLive12 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 12th Day’,
userLive13 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 13th Day’,
userLive14 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 14th Day’,
userLive15 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 15th Day’,
userLive16 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 16th Day’,
userLive30 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 30th Day’,
userLive60 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 60th Day’,
userLive90 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Number of People Retained on the 90th Day’,
ltv1Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 1 Recharge’,
ltv1Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 1 Recharge After Accounting’,
ltv3Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 3 Recharge’,
ltv3Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 3 Recharge After Accounting’,
ltv7Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 7 Recharge’,
ltv7Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 7 Recharge After Accounting’,
ltv15Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 15 Recharge’,
ltv15Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 15 Recharge After Accounting’,
ltv30Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 30 Recharge’,
ltv30Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 30 Recharge After Accounting’,
ltv45Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 45 Recharge’,
ltv45Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 45 Recharge After Accounting’,
ltv60Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 60 Recharge’,
ltv60Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 60 Recharge After Accounting’,
ltv90Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 90 Recharge’,
ltv90Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘LTV 90 Recharge After Accounting’,
inner_time timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘timestamp’,
newDidRoleCount int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘Number of New Devices Creating Roles’,
didRoleCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Total Number of Devices Creating Roles’,
field_1 int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘Reserved Field’,
field_2 decimal(10,0) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘Reserved Field’,
PRIMARY KEY (recdate,osid,gameid,cid,aid,oid,pt,moment) /*T![clustered_index] NONCLUSTERED */,
KEY index_id (gameid,cid,aid,oid,moment,pt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘User Feature Report - Time Moment’

15
| username: h5n1 | Original post link

The slow one needs to process 3.07 million historical version keys. Try running an explain analyze on the data for December 2021 separately to see.

16
| username: just_coder | Original post link

Individual queries are fast, but the problem is that as long as the time interval reaches more than seven months, the query takes tens of seconds. Even querying from 20210401 to 20211101 is equally slow.

id                           	task     	estRows  	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	memory 	disk
Projection_7                 	root     	65.34    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, Column#98, Column#99, Column#100, Column#101, Column#102, Column#103, Column#104, Column#105, Column#106, Column#107, Column#108, Column#109, Column#110, Column#111, Column#112, Column#113, Column#114, Column#115, Column#93, Column#94, Column#95, Column#96, Column#97                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	24318  	time:2.48s, loops:25, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      	7.65 MB	N/A
└─Sort_8                     	root     	65.34    	yd_194_result.feature_dev_moment.payment:desc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	24318  	time:2.47s, loops:25                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       	38.2 MB	0 Bytes
  └─Projection_10            	root     	65.34    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, 0->Column#98, 0->Column#99, 0->Column#100, 0->Column#101, 0->Column#102, 0->Column#103, 0->Column#104, 0->Column#105, 0->Column#106, 0->Column#107, 0->Column#108, 0->Column#109, 0->Column#110, 0->Column#111, 0->Column#112, 0->Column#113, 0->Column#114, 0->Column#115, Column#93, Column#94, Column#95, Column#96, Column#97, yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	24318  	time:2.26s, loops:26, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      	32.9 MB	N/A
    └─HashAgg_29             	root     	65.34    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(Column#236)->Column#66, funcs:sum(Column#237)->Column#67, funcs:sum(Column#238)->Column#68, funcs:sum(Column#239)->Column#69, funcs:sum(Column#240)->Column#70, funcs:sum(Column#241)->Column#71, funcs:sum(Column#242)->Column#72, funcs:sum(Column#243)->Column#73, funcs:sum(Column#244)->Column#74, funcs:sum(Column#245)->Column#75, funcs:sum(Column#246)->Column#76, funcs:sum(Column#247)->Column#77, funcs:sum(Column#248)->Column#78, funcs:sum(Column#249)->Column#79, funcs:sum(Column#250)->Column#80, funcs:sum(Column#251)->Column#81, funcs:sum(Column#252)->Column#82, funcs:sum(Column#253)->Column#83, funcs:sum(Column#254)->Column#84, funcs:sum(Column#255)->Column#85, funcs:sum(Column#256)->Column#86, funcs:sum(Column#257)->Column#87, funcs:sum(Column#258)->Column#88, funcs:sum(Column#259)->Column#89, funcs:sum(Column#260)->Column#90, funcs:sum(Column#261)->Column#91, funcs:sum(Column#262)->Column#92, funcs:sum(Column#263)->Column#93, funcs:sum(Column#264)->Column#94, funcs:sum(Column#265)->Column#95, funcs:sum(Column#266)->Column#96, funcs:sum(Column#267)->Column#97, funcs:firstrow(yd_194_result.feature_dev_moment.recdate)->yd_194_result.feature_dev_moment.recdate, funcs:firstrow(Column#269)->yd_194_result.feature_dev_moment.osid, funcs:firstrow(yd_194_result.feature_dev_moment.gameid)->yd_194_result.feature_dev_moment.gameid, funcs:firstrow(yd_194_result.feature_dev_moment.cid)->yd_194_result.feature_dev_moment.cid, funcs:firstrow(Column#272)->yd_194_result.feature_dev_moment.aid, funcs:firstrow(Column#273)->yd_194_result.feature_dev_moment.oid, funcs:firstrow(yd_194_result.feature_dev_moment.pt)->yd_194_result.feature_dev_moment.pt, funcs:firstrow(Column#275)->yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         	24318  	time:2.25s, loops:26, partial_worker:{wall_time:2.057477579s, concurrency:5, task_num:24, tot_wait:9.848699288s, tot_exec:373.551603ms, tot_time:10.244315181s, max:2.057303528s, p95:2.057303528s}, final_worker:{wall_time:2.265677267s, concurrency:5, task_num:25, tot_wait:10.194230485s, tot_exec:1.099307156s, tot_time:11.293589357s, max:2.265470875s, p95:2.265470875s}                                                                                                                                                                          	88.8 MB	N/A
      └─IndexLookUp_30       	root     	65.34    	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	24338  	time:2.03s, loops:25, index_task: {total_time: 1.76s, fetch_handle: 752.7ms, build: 103.8µs, wait: 1s}, table_task: {total_time: 6.25s, num: 20, concurrency: 5}                                                                                                                                                                                                                                                                                                                                                                                          	15.7 MB	N/A
        ├─Selection_25       	cop[tikv]	302142.09	not(in(yd_194_result.feature_dev_moment.gameid, "1604385436956830", "1621669402391940", "1632711274294320", "1636099609615800", "1639364435557330", "1639126762567720", "1616571507848110", "1639110127366330", "1640159205240000", "1633765571609960"))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	329041 	time:706.1ms, loops:329, cop_task: {num: 1, max: 701.4ms, proc_keys: 334995, tot_proc: 655ms, tot_wait: 3ms, rpc_num: 1, rpc_time: 701.4ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:641ms, loops:332}, scan_detail: {total_process_keys: 334995, total_process_keys_size: 47904285, total_keys: 334996, rocksdb: {delete_skipped_count: 0, key_skipped_count: 334995, block: {cache_hit_count: 352, read_count: 0, read_byte: 0 Bytes}}}                                                                                                              	N/A    	N/A
        │ └─IndexRangeScan_23	cop[tikv]	307792.50	table:v, index:PRIMARY(recdate, osid, gameid, cid, aid, oid, pt, moment), range:[2021-12-01,2021-12-31], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	334995 	tikv_task:{time:495ms, loops:332}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          	N/A    	N/A
        └─HashAgg_12         	cop[tikv]	65.34    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(yd_194_result.feature_dev_moment.installcount)->Column#236, funcs:sum(yd_194_result.feature_dev_moment.regdevcount)->Column#237, funcs:sum(yd_194_result.feature_dev_moment.newdevcount)->Column#238, funcs:sum(yd_194_result.feature_dev_moment.newdidrolecount)->Column#239, funcs:sum(yd_194_result.feature_dev_moment.didrolecount)->Column#240, funcs:sum(yd_194_result.feature_dev_moment.reglogincount)->Column#241, funcs:sum(yd_194_result.feature_dev_moment.newlogincount)->Column#242, funcs:sum(yd_194_result.feature_dev_moment.newuserrolecount)->Column#243, funcs:sum(yd_194_result.feature_dev_moment.userrolecount)->Column#244, funcs:sum(yd_194_result.feature_dev_moment.userlive1)->Column#245, funcs:sum(yd_194_result.feature_dev_moment.userlive3)->Column#246, funcs:sum(yd_194_result.feature_dev_moment.userlive4)->Column#247, funcs:sum(yd_194_result.feature_dev_moment.userlive5)->Column#248, funcs:sum(yd_194_result.feature_dev_moment.userlive6)->Column#249, funcs:sum(yd_194_result.feature_dev_moment.userlive7)->Column#250, funcs:sum(yd_194_result.feature_dev_moment.userlive8)->Column#251, funcs:sum(yd_194_result.feature_dev_moment.userlive9)->Column#252, funcs:sum(yd_194_result.feature_dev_moment.userlive10)->Column#253, funcs:sum(yd_194_result.feature_dev_moment.userlive11)->Column#254, funcs:sum(yd_194_result.feature_dev_moment.userlive12)->Column#255, funcs:sum(yd_194_result.feature_dev_moment.userlive13)->Column#256, funcs:sum(yd_194_result.feature_dev_moment.userlive14)->Column#257, funcs:sum(yd_194_result.feature_dev_moment.userlive15)->Column#258, funcs:sum(yd_194_result.feature_dev_moment.userlive16)->Column#259, funcs:sum(yd_194_result.feature_dev_moment.userlive30)->Column#260, funcs:sum(yd_194_result.feature_dev_moment.userlive60)->Column#261, funcs:sum(yd_194_result.feature_dev_moment.userlive90)->Column#262, funcs:sum(yd_194_result.feature_dev_moment.newuserincome)->Column#263, funcs:sum(yd_194_result.feature_dev_moment.newuserpaypeople)->Column#264, funcs:sum(yd_194_result.feature_dev_moment.todayincome)->Column#265, funcs:sum(yd_194_result.feature_dev_moment.todaypaypeople)->Column#266, funcs:sum(yd_194_result.feature_dev_moment.todaypay6people)->Column#267, funcs:firstrow(yd_194_result.feature_dev_moment.osid)->Column#269, funcs:firstrow(yd_194_result.feature_dev_moment.aid)->Column#272, funcs:firstrow(yd_194_result.feature_dev_moment.oid)->Column#273, funcs:firstrow(yd_194_result.feature_dev_moment.payment)->Column#275	24338  	time:6.02s, loops:42, cop_task: {num: 22, max: 525.6ms, min: 53.1ms, avg: 280ms, p95: 476.1ms, max_proc_keys: 20946, p95_proc_keys: 20507, tot_proc: 5.74s, tot_wait: 17ms, rpc_num: 43, rpc_time: 12.3s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:493ms, min:42ms, p80:391ms, p95:448ms, iters:328, tasks:22}, scan_detail: {total_process_keys: 329041, total_process_keys_size: 161876063, total_keys: 658262, rocksdb: {delete_skipped_count: 0, key_skipped_count: 986945, block: {cache_hit_count: 3891, read_count: 0, read_byte: 0 Bytes}}}	N/A    	N/A
          └─TableRowIDScan_24	cop[tikv]	302142.09	table:v, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	329041 	tikv_task:{proc max:205ms, min:17ms, p80:153ms, p95:182ms, iters:328, tasks:22}                                                                                                                                                                                                                                                                                                                                                                                                                                                                            	N/A    	N/A
17
| username: h5n1 | Original post link

When executing slow SQL, check the CPU usage of the three TiKV nodes.

18
| username: just_coder | Original post link

The image cannot be processed. Please provide the text content for translation.

19
| username: tidb狂热爱好者 | Original post link

This is a statistical query. You need to use TiFlash. You also mentioned querying data for 7 months. This is a feature of ClickHouse.

20
| username: tidb狂热爱好者 | Original post link

Moreover, this table is clearly meant for data analysis. The HTAP functionality is not suitable for using TiKV.