sql不间断的慢

中午已经设置了,在观察中。还有个想法,是把后台自动更新统计信息的时间设置成0点到18点,我现在是0点到5点的

搞错,我只设置了会话级别的。v7.1.1版本,这个参数不支持全局设置 :sweat_smile:

我觉得 SQL 不需要走 TiFlash 的 :joy_cat:

目前有些数据量大的查询,走tikv慢了,所以强制走了tiflash

这个 SQL 不需要 Tiflash 吧, Tiflash 主要应对 AP 分析型的负载,而且 AP 型负载不能很高的,你这个 SQL ,感觉走 TiKV 就行了啊

走tikv也可以的,慢了点

走 tikv 的执行计划发出来

Projection_14 1.00 96 root time:3.23s, loops:6, RU:144650.195552, Concurrency:OFF round(Column#10, 2)->Column#11, substring(cast(energy.ads_iot_current_value_minute.data_time, var_string(19)), 1, 16)->Column#12, energy.ads_iot_current_value_minute.measure_tag 8.98 KB N/A
└─Limit_17 1.00 96 root time:3.23s, loops:6 offset:0, count:200 N/A N/A
└─HashAgg_21 1.00 96 root time:3.23s, loops:6, partial_worker:{wall_time:3.22515357s, concurrency:5, task_num:1, tot_wait:16.12498909s, tot_exec:140.601µs, tot_time:16.125159705s, max:3.225110651s, p95:3.225110651s}, final_worker:{wall_time:3.225246275s, concurrency:5, task_num:5, tot_wait:16.125638669s, tot_exec:358.097µs, tot_time:16.126001113s, max:3.225224798s, p95:3.225224798s} group by:Column#31, Column#32, funcs:sum(Column#28)->Column#10, funcs:firstrow(Column#29)->energy.ads_iot_current_value_minute.measure_tag, funcs:firstrow(Column#30)->energy.ads_iot_current_value_minute.data_time 69.7 KB N/A
└─Projection_84 119.73 96 root time:3.22s, loops:2, Concurrency:OFF energy.ads_iot_current_value_minute.data_value, energy.ads_iot_current_value_minute.measure_tag, energy.ads_iot_current_value_minute.data_time, substring(cast(energy.ads_iot_current_value_minute.data_time, var_string(19)), 1, 16)->Column#31, energy.ads_iot_current_value_minute.measure_tag 13.2 KB N/A
└─Selection_25 119.73 96 root time:3.22s, loops:2 eq(Column#9, 1) 19.2 KB N/A
└─Window_28 149.67 96 root time:3.22s, loops:3 row_number()->Column#9 over(partition by energy.ads_iot_current_value_minute.measure_point_id, energy.ads_iot_current_value_minute.measure_tag, energy.ads_iot_current_value_minute.data_time order by energy.ads_iot_current_value_minute.create_time desc rows between current row and current row) N/A N/A
└─Sort_47 149.67 96 root time:3.22s, loops:2 energy.ads_iot_current_value_minute.measure_point_id, energy.ads_iot_current_value_minute.measure_tag, energy.ads_iot_current_value_minute.data_time, energy.ads_iot_current_value_minute.create_time:desc 24.9 KB 0 Bytes
└─IndexLookUp_46 149.67 96 root partition:P_LT_1716566400 time:3.22s, loops:2, index_task: {total_time: 2.74s, fetch_handle: 2.74s, build: 1.01µs, wait: 5.88µs}, table_task: {total_time: 485.3ms, num: 1, concurrency: 5}, next: {wait_index: 2.74s, wait_table_lookup_build: 416.8µs, wait_table_lookup_resp: 484.9ms} 33.6 KB N/A
├─Selection_44(Build) 152.66 96 cop[tikv] time:2.74s, loops:3, cop_task: {num: 124, max: 642.4ms, min: 247.4µs, avg: 313.7ms, p95: 566.1ms, max_proc_keys: 722240, p95_proc_keys: 694594, tot_proc: 38.7s, tot_wait: 26.7ms, rpc_num: 125, rpc_time: 38.9s, copr_cache_hit_ratio: 0.22, build_task_duration: 244.1µs, max_distsql_concurrency: 15}, backoff{regionMiss: 2ms}, tikv_task:{proc max:642ms, min:13ms, avg: 400.6ms, p80:504ms, p95:565ms, iters:60092, tasks:124}, scan_detail: {total_process_keys: 47583075, total_process_keys_size: 8625383333, total_keys: 47583400, get_snapshot_time: 23.1ms, rocksdb: {key_skipped_count: 47620017, block: {cache_hit_count: 191121}}} eq(energy.ads_iot_current_value_minute.measure_point_id, be06c45012f711ed9eb91c8cf913d9a6), eq(energy.ads_iot_current_value_minute.measure_tag, GEN.P) N/A N/A
│ └─IndexRangeScan_42 62963692.68 60962444 cop[tikv] table:ads, index:idx_aicvm_index(data_time, measure_point_id, measure_tag) tikv_task:{proc max:585ms, min:11ms, avg: 357ms, p80:451ms, p95:507ms, iters:60092, tasks:124} range:[2024-05-24 00:00:00,2024-05-24 23:59:59], keep order:false N/A N/A
└─Selection_45(Probe) 149.67 96 cop[tikv] time:484.8ms, loops:2, cop_task: {num: 73, max: 480.6ms, min: 0s, avg: 20.2ms, p95: 148.7ms, max_proc_keys: 3, p95_proc_keys: 2, tot_proc: 2.34s, tot_wait: 27.4ms, rpc_num: 16, rpc_time: 1.48s, copr_cache_hit_ratio: 0.00, build_task_duration: 302.6µs, max_distsql_concurrency: 1, max_extra_concurrency: 7, store_batch_num: 57}, tikv_task:{proc max:471ms, min:0s, avg: 32ms, p80:25ms, p95:171ms, iters:73, tasks:73}, scan_detail: {total_process_keys: 96, total_process_keys_size: 14592, total_keys: 96, get_snapshot_time: 26.4ms, rocksdb: {block: {cache_hit_count: 1126, read_count: 168, read_byte: 5.49 MB, read_time: 1.38s}}} ge(energy.ads_iot_current_value_minute.data_value, 0) N/A N/A
└─TableRowIDScan_43 152.66 96 cop[tikv] table:ads tikv_task:{proc max:471ms, min:0s, avg: 32ms, p80:25ms, p95:171ms, iters:73, tasks:73} keep order:false N/A N/A

走了索引还要预计执行60962444行?

:thinking:那估计应该就是这里的问题。如果返回数据里没有聚合函数,走tikv试试,速度会不会更快?

这个索引范围扫描,没有把需要的数据精确过滤出来。

有聚合的,主要原因就是执行计划变了

ads这个表的索引设置不合理,你设置的联合索引为(data_time,measure_point_id, measure_tag),但是你date_time是范围查询,导致后面的两个字段的索引失效了;ads表的联合索引字段顺序应该是(measure_point_id, measure_tag,data_time),这样你三个字段都能走索引,你的索引过滤效果会大大提高。

SQL执行时快时慢,感觉与SQL本身没有关系,应该是其他SQL、接口调用慢影响了该SQL

感谢,我试试

走tiflash的sql,慢的时候,执行计划里task是cop[tiflash]



tiflash_error.log.2024-05-29-16_44_46.634 (41.9 MB)
tiflash_error.log.2024-05-29-16_46_31.695 (20.3 MB)

不新开贴了,麻烦大佬们帮忙分析下。昨天中午11点前后,两台tiflash内存爆了重启。

发一下时间段,观察一下近1个月的

好的 :sweat_smile: