tidb explain

【 TiDB 使用环境】
生产:4.0.15 11 x 16core
测试:5.2.1 kv配置:3 x 4core

【概述】 场景 + 问题概述
explain 结果对比如下:
测试的explain结果:
Projection_8 1.00 1000 root time:2m14.9s, loops:2, Concurrency:OFF
└─TopN_11 1.00 1000 root time:2m14.9s, loops:2
└─HashAgg_37 1.00 2148 root time:2m14.9s, loops:6, partial_worker:{wall_time:2m14.89815157s, concurrency:5, task_num:349, tot_wait:11m14.099532635s, tot_exec:387.55835ms, tot_time:11m14.488336345s, max:2m14.898129291s, p95:2m14.898129291s}, final_worker:{wall_time:2m14.904073041s, concurrency:5, task_num:25, tot_wait:11m14.491842471s, tot_exec:18.321344ms, tot_time:11m14.510174812s, max:2m14.904056573s, p95:2m14.904056573s}
└─IndexLookUp_38 1.00 356541 root time:2m14.9s, loops:350, index_task: {total_time: 2m14s, fetch_handle: 4.41s, build: 403.6µs, wait: 2m9.6s}, table_task: {total_time: 2m14.9s, num: 228, concurrency: 1}
├─Selection_31(Build) 43.09 489103 cop[tikv] time:4.38s, loops:507, cop_task: {num: 42, max: 542.6ms, min: 15.9ms, avg: 104.6ms, p95: 233.4ms, max_proc_keys: 60771, p95_proc_keys: 27894, tot_proc: 3.89s, tot_wait: 264ms, rpc_num: 45, rpc_time: 4.39s, copr_cache_hit_ratio: 0.00}, backoff{regionMiss: 2ms}, tikv_task:{proc max:537ms, min:7ms, p80:129ms, p95:226ms, iters:670, tasks:42}, scan_detail: {total_process_keys: 490941, total_keys: 493644, rocksdb: {delete_skipped_count: 0, key_skipped_count: 490941, block: {cache_hit_count: 18731, read_count: 4158, read_byte: 157.2 MB}}}
│ └─IndexRangeScan_29 43086.11 490941 cop[tikv] table:dish_analysis_result, index:dim_date_brand(dim_pshop_id, date, brand_id) tikv_task:{proc max:535ms, min:7ms, p80:129ms, p95:225ms, iters:670, tasks:42}
└─HashAgg_17(Probe) 1.00 356541 cop[tikv] time:2m14.2s, loops:694, cop_task: {num: 7993, max: 268.5ms, min: 5.83ms, avg: 16.6ms, p95: 35.4ms, max_proc_keys: 389, p95_proc_keys: 126, tot_proc: 32.9s, tot_wait: 7.92s, rpc_num: 7997, rpc_time: 2m12.9s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:191ms, min:0s, p80:5ms, p95:13ms, iters:7993, tasks:7993}, scan_detail: {total_process_keys: 489103, total_keys: 499620, rocksdb: {delete_skipped_count: 0, key_skipped_count: 21418, block: {cache_hit_count: 3904981, read_count: 51626, read_byte: 570.6 MB}}}
└─Selection_32 8.73 456222 cop[tikv] tikv_task:{proc max:190ms, min:0s, p80:4ms, p95:12ms, iters:7993, tasks:7993}
└─TableRowIDScan_30 43.09 489103 cop[tikv] table:dish_analysis_result tikv_task:{proc max:190ms, min:0s, p80:4ms, p95:12ms, iters:7993, tasks:7993}

生产的explain结果:
Projection_8 102.22 1000 root time:1.13s, loops:2, Concurrency:OFF
└─TopN_11 102.22 1000 root time:1.13s, loops:2
└─HashAgg_32 102.22 2138 root time:1.13s, loops:5, PartialConcurrency:2, FinalConcurrency:2
└─IndexLookUp_33 102.22 41236 root time:1.12s, loops:42, index_task: {total_time: 968.9ms, fetch_handle: 89.5ms, build: 21.6µs, wait: 879.3ms}, table_task: {total_time: 4.32s, num: 19, concurrency: 4}
├─Selection_30(Build) 39674.92 306035 cop[tikv] time:62.1ms, loops:309, cop_task: {num: 16, max: 129.8ms, min: 4.77ms, avg: 53.1ms, p95: 129.8ms, max_proc_keys: 60873, p95_proc_keys: 60873, tot_proc: 790ms, tot_wait: 27ms, rpc_num: 16, rpc_time: 849.3ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:125ms, min:3ms, p80:79ms, p95:125ms, iters:368, tasks:16}, scan_detail: {total_process_keys: 306035, total_keys: 311039}
│ └─IndexRangeScan_28 39674924.00 306035 cop[tikv] table:dish_analysis_result, index:dish_data_brand(sh_dish_id, date, brand_id) tikv_task:{proc max:123ms, min:3ms, p80:79ms, p95:123ms, iters:368, tasks:16}
└─HashAgg_16(Probe) 102.22 41236 cop[tikv] time:3.9s, loops:70, cop_task: {num: 1045, max: 209.3ms, min: 3.27ms, avg: 45.5ms, p95: 121.5ms, max_proc_keys: 1014, p95_proc_keys: 723, tot_proc: 16.1s, tot_wait: 15.2s, rpc_num: 1045, rpc_time: 47.5s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:94ms, min:0s, p80:23ms, p95:38ms, iters:1045, tasks:1045}, scan_detail: {total_process_keys: 306035, total_keys: 315783}
└─Selection_31 39674.92 277318 cop[tikv] tikv_task:{proc max:93ms, min:0s, p80:22ms, p95:37ms, iters:1045, tasks:1045}
└─TableRowIDScan_29 39674.92 306035 cop[tikv] table:dish_analysis_result tikv_task:{proc max:93ms, min:0s, p80:22ms, p95:37ms, iters:1045, tasks:1045}

问题:
同张表,使用一样的索引,生产索引扫描行数38万, 测试索引扫描49万。
所有算子执行时间,生产2秒 测试需要149秒。 生产父算子调用子算子695次, 测试父算子调用子算子69次
请问这差异是什么影响的? 感谢

看起来资源不一样,这样比较感觉意义不大吧,数据量也不一样。
您可以在同一套测试环境,比对升级前后的信息。

有对比过两个库的表健康度么?

1 Like

资源很难一样,要考虑成本

健康度不一样, 生产的健康度是 27%, 测试健康度是100%
健康度会影响算子差异?
我将生产健康度提高再试试

是的,可是你这结果和健康度不匹配呀。正常情况健康度越高,效率越快才对

2 Likes

我们建立了两个测试环境:两个测试环境硬件配置一样,表数据量一样,版本不同,测试下来查询速度还是差距很大。

版本4.0.15

版本5.2.1:

数据量一样么?怎么hashagg那一步actRows差异那么大?一个35w,一个17w

版本4.0.15 tableRowScan 算子:
time:20.6s, loops:213, cop_task: {num: 928, max: 849ms, min: 5.89ms, avg: 241.3ms, p95: 623.9ms, max_proc_keys: 2062, p95_proc_keys: 837, tot_proc: 1m10.1s, tot_wait: 46.7s, rpc_num: 928, rpc_time: 3m43.9s, copr_cache_hit_ratio: 0.01}, tikv_task:{proc max:518ms, min:0s, p80:129ms, p95:187ms, iters:969, tasks:928}, scan_detail: {total_process_keys: 486717, total_keys: 537816}
tikv_task:{proc max:490ms, min:0s, p80:121ms, p95:181ms, iters:969, tasks:928}
tikv_task:{proc max:490ms, min:0s, p80:121ms, p95:181ms, iters:969, tasks:928}

版本5.2.1 tableRowsScan算子:

└─HashAgg_17(Probe) 1.00 353397 cop[tikv] time:59s, loops:691, cop_task: {num: 7414, max: 54.4ms, min: 4.73ms, avg: 7.81ms, p95: 9.81ms, max_proc_keys: 316, p95_proc_keys: 129, tot_proc: 10.7s, tot_wait: 573ms, rpc_num: 7414, rpc_time: 57.8s, copr_cache_hit_ratio: 0.03}, tikv_task:{proc max:18ms, min:0s, p80:2ms, p95:4ms, iters:7414, tasks:7414}, scan_detail: {total_process_keys: 462274, total_keys: 471714, rocksdb: {delete_skipped_count: 0, key_skipped_count: 19196, block: {cache_hit_count: 3633272, read_count: 84, read_byte: 873.7 KB}}}
└─Selection_32 38.53 456222 cop[tikv] tikv_task:{proc max:18ms, min:0s, p80:2ms, p95:3ms, iters:7414, tasks:7414}
└─TableRowIDScan_30 38.53 489103 cop[tikv] table:dish_analysis_result tikv_task:{proc max:18ms, min:0s, p80:2ms, p95:3ms, iters:7414, tasks:7414}

数据量一样的

4.0.15 版本统计信息

5.2.1 版本统计信息:

您好 我们部署了2套同样配置,不同版本的2套测试环境(4.0.15 和5.2.1),用同样的表 explain下来,发现差距依旧很大哦

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。