try to optimize index lookup query may send too many cop task

[crazycs520]

Enhancement

1. Minimal reproduce step

drop table if exists t;
create table t (id int key auto_increment, b int, c int, index idx (b));
insert into t () values (), (), (), (), (), (), (), ();
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t;
insert into t (b) select b from t limit 34464;
update t set b = rand() * 1000000 ;
split table t between (0) and (100000) regions 100;
set @@tidb_store_batch_size=0; -- disable store batch, to make the issue more clearer.
explain analyze select * from t use index(idx) where b >= 0 and b < 200000;

> explain analyze select * from t use index(idx) where b >= 0 and b < 200000;
+---------------------------+----------+---------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+---------+------+
| id                        | estRows  | actRows | task      | access object         | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | operator info                      | memory  | disk |
+---------------------------+----------+---------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+---------+------+
| IndexLookUp_7             | 19451.37 | 19951   | root      |                       | time:122.6ms, loops:21, RU:503.624224, index_task: {total_time: 67.9ms, fetch_handle: 67.8ms, build: 3.79µs, wait: 6.5µs}, table_task: {total_time: 147ms, num: 5, concurrency: 5}, next: {wait_index: 18.2ms, wait_table_lookup_build: 3.07ms, wait_table_lookup_resp: 100.9ms}                                                                                                                                                                                                                                     |                                    | 3.01 MB | N/A  |
| ├─IndexRangeScan_5(Build) | 19451.37 | 19951   | cop[tikv] | table:t, index:idx(b) | time:67.3ms, loops:22, cop_task: {num: 5, max: 18.1ms, min: 1.26ms, avg: 13.5ms, p95: 18.1ms, max_proc_keys: 9184, p95_proc_keys: 9184, tot_proc: 46.7ms, tot_wait: 206.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 8.13µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:5, total_time:67.7ms}}, tikv_task:{proc max:18ms, min:1ms, avg: 13.6ms, p80:18ms, p95:18ms, iters:40, tasks:5}, scan_detail: {total_process_keys: 19951, total_process_keys_size: 917746, total_keys: 60134, get_snapsh... | range:[0,200000), keep order:false | N/A     | N/A  |
| └─TableRowIDScan_6(Probe) | 19451.37 | 19951   | cop[tikv] | table:t               | time:140.9ms, loops:25, cop_task: {num: 500, max: 18.2ms, min: 1.06ms, avg: 5.72ms, p95: 10.4ms, max_proc_keys: 119, p95_proc_keys: 98, tot_proc: 666.3ms, tot_wait: 1.17s, copr_cache_hit_ratio: 0.00, build_task_duration: 701.4µs, max_distsql_concurrency: 15, max_extra_concurrency: 39}, rpc_info:{Cop:{num_rpc:500, total_time:2.86s}}, tikv_task:{proc max:14ms, min:0s, avg: 2.55ms, p80:4ms, p95:6ms, iters:783, tasks:500}, scan_detail: {total_process_keys: 19951, total_process_keys_size: 791456, ... | keep order:false                   | N/A     | N/A  |
+---------------------------+----------+---------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+---------+------+

Pay attention to the execution information of TableRowIDScan_6 executor:

TableRowIDScan_6(Probe) | ... | time:140.9ms, loops:25, cop_task: {num: 500, ...}, {Cop:{num_rpc:500, total_time:2.86s}}

The table t only has 100 regions, but the TableRowIDScan executor sends a total of 500 cop requests, It's too wasteful (consuming) resources.

But, if set tidb_max_chunk_size=20000, we can fix this:

>  set @@tidb_max_chunk_size=20000;
>  explain analyze select * from t use index(idx) where b >= 0 and b < 200000;
+---------------------------+----------+---------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+---------+------+
| id                        | estRows  | actRows | task      | access object         | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | operator info                      | memory  | disk |
+---------------------------+----------+---------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+---------+------+
| IndexLookUp_7             | 19451.37 | 19951   | root      |                       | time:28.6ms, loops:2, RU:94.991985, index_task: {total_time: 1.74ms, fetch_handle: 1.74ms, build: 499ns, wait: 1.96µs}, table_task: {total_time: 26.4ms, num: 1, concurrency: 5}, next: {wait_index: 1.81ms, wait_table_lookup_build: 5.63ms, wait_table_lookup_resp: 20.7ms}                                                                                                                                                                                                                                        |                                    | 3.07 MB | N/A  |
| ├─IndexRangeScan_5(Build) | 19451.37 | 19951   | cop[tikv] | table:t, index:idx(b) | time:1.28ms, loops:3, cop_task: {num: 5, max: 354.5µs, min: 185.5µs, avg: 243.4µs, p95: 354.5µs, tot_proc: 5.42µs, tot_wait: 187.5µs, copr_cache_hit_ratio: 1.00, build_task_duration: 21.2µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:5, total_time:1.19ms}}, tikv_task:{proc max:18ms, min:2ms, avg: 13.8ms, p80:18ms, p95:18ms, iters:40, tasks:5}, scan_detail: {get_snapshot_time: 111µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 5.42µs, total_wait_time: 187.5µs, tikv_wall_t... | range:[0,200000), keep order:false | N/A     | N/A  |
| └─TableRowIDScan_6(Probe) | 19451.37 | 19951   | cop[tikv] | table:t               | time:20.6ms, loops:2, cop_task: {num: 100, max: 10.1ms, min: 248.4µs, avg: 2.73ms, p95: 9.13ms, max_proc_keys: 224, p95_proc_keys: 210, tot_proc: 125.7ms, tot_wait: 32.7ms, copr_cache_hit_ratio: 0.73, build_task_duration: 318.3µs, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:100, total_time:272.7ms}}, tikv_task:{proc max:12ms, min:2ms, avg: 8.29ms, p80:9ms, p95:11ms, iters:303, tasks:100}, scan_detail: {total_process_keys: 5303, total_process_keys_size: 210298, total_keys: 10157, get_... | keep order:false                   | N/A     | N/A  |
+---------------------------+----------+---------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+---------+------+

You can see the execution information of TableRowIDScan_6 executor as following, it send less cop rpc and total rpc time is less, which also means consumes less resources.

TableRowIDScan_6(Probe) | ... | time:20.6ms, loops:2, cop_task: {num: 100, ...}, rpc_info:{Cop:{num_rpc:100, total_time:272.7ms}}

Can we try to automatically adjust tidb_max_chunk_size or something else to optimize this issue?

2. What is your TiDB version? (Required)

+-----------------------------------------------------------+
| tidb_version()                                            |
+-----------------------------------------------------------+
| Release Version: v8.2.0-alpha-311-gad0dc3e128             |
| Edition: Community                                        |
| Git Commit Hash: ad0dc3e128e138dae15f77adad0c37eac431f8a5 |
| Git Branch: HEAD                                          |
| UTC Build Time: 2024-06-06 02:54:58                       |
| GoVersion: go1.21.10                                      |
| Race Enabled: false                                       |
| Check Table Before Drop: false                            |
| Store: tikv                                               |
+-----------------------------------------------------------+