Description
Enhancement
└─IndexLookUp_48(Probe) | 84.44 | 895287 | root | partition:all | time:27.5s, loops:876, index_task: {total_time: 25.8s, fetch_handle: 12.6s, build: 137.5µs, wait: 13.3s}, table_task: {total_time: 5m16.1s, num: 88, concurrency: 15} | | 37.4 MB | N/A |
| ├─IndexRangeScan_45(Build) | 91.09 | 898714 | cop[tikv] | table:ge, index:index_ge_on_repo_name_type(repo_name, type) | time:12.6s, loops:958, cop_task: {num: 610, max: 2.49s, min: 997.7µs, avg: 81.5ms, p95: 284.7ms, max_proc_keys: 50144, p95_proc_keys: 5088, tot_proc: 15.5s, tot_wait: 28.8s, rpc_num: 610, rpc_time: 49.7s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{proc max:2.47s, min:0s, avg: 25.9ms, p80:4ms, p95:88ms, iters:2487, tasks:610}, scan_detail: {total_process_keys: 898714, total_process_keys_size: 109875384, total_keys: 900811, get_snapshot_time: 8.94s, rocksdb: {delete_skipped_count: 40, key_skipped_count: 899377, block: {cache_hit_count: 20224, read_count: 2089, read_byte: 33.7 MB, read_time: 719.4ms}}} | range: decided by [eq(gharchive_dev.github_events.repo_name, gharchive_dev.github_events.repo_name)], keep order:false | N/A | N/A |
| └─Selection_47(Probe) | 84.44 | 895287 | cop[tikv] | | time:5m15.4s, loops:992, cop_task: {num: 52368, max: 4.93s, min: 398.2µs, avg: 346.5ms, p95: 1.6s, max_proc_keys: 364, p95_proc_keys: 75, tot_proc: 3h11m7.2s, tot_wait: 1h43m14.4s, rpc_num: 52380, rpc_time: 5h2m32.7s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, backoff{regionMiss: 14ms}, tikv_task:{proc max:4.9s, min:0s, avg: 219.4ms, p80:228ms, p95:1.38s, iters:60831, tasks:52368}, scan_detail: {total_process_keys: 898714, total_process_keys_size: 241363652, total_keys: 899948, get_snapshot_time: 54.8s, rocksdb: {delete_skipped_count: 65, key_skipped_count: 3258, block: {cache_hit_count: 9080304, read_count: 792971, read_byte: 15.1 GB, read_time: 5m2.6s}}} | ne(gharchive_dev.github_events.actor_login, "robpike") | N/A | N/A |
| └─TableRowIDScan_46 | 91.09 | 898714 | cop[tikv] | table:ge | tikv_task:{proc max:4.9s, min:0s, avg: 219.4ms, p80:228ms, p95:1.38s, iters:60831, tasks:52368} | keep order:false
The concurrency of the index lookup executor is increased to 15, but the dist scan concurrency is still 15.
From the experiments
tidb_distsql_scan_concurrency=256 can reduce from 25s to 15s, and then further setting session tidb_store_batch_size=100 can further reduce to 9s (but no impact by setting to 40);
If the dist scan concurrency could be increased as expected, the query latency could be reduced 40%. A possibility is that the default https://github.com/pingcap/tidb/blob/master/store/copr/coprocessor.go#L69 is not suitable for this scenario.