False & true sharing issue of RWMutex in mutexForRowContainer

[gengliqi]

Enhancement

@Yui-Song found a complex query has a very unstable execution time.

Slow One: 517.1s

+------------------------------------------+------------+------------+-----------+---------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| ID                                       | ESTROWS    | ACTROWS    | TASK      | ACCESS OBJECT                               | EXECUTION INFO                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | OPERATOR INFO                                                                                                                                                                               | MEMORY    | DISK    |
+------------------------------------------+------------+------------+-----------+---------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| HashAgg_35                               | 1.00       | 1          | root      |                                             | time:8m37.1s, loops:2, partial_worker:{wall_time:8m37.129402424s, concurrency:5, task_num:9006, tot_wait:43m3.269668938s, tot_exec:2.371660839s, tot_time:43m5.645615052s, max:8m37.129320038s, p95:8m37.129320038s}, final_worker:{wall_time:8m37.129477906s, concurrency:5, task_num:5, tot_wait:43m5.647022619s, tot_exec:125.148µs, tot_time:43m5.647154521s, max:8m37.129450209s, p95:8m37.129450209s}                                                                               | funcs:min(imdb.company_name.name)->Column#39, funcs:min(imdb.movie_companies.note)->Column#40, funcs:min(imdb.title.title)->Column#41                                                       | 2.15 MB   | N/A     |
| └─Projection_37                          | 4804.48    | 9221275    | root      |                                             | time:8m37.1s, loops:9007, Concurrency:5                                                                                                                                                                                                                                                                                                                                                                                                                                                   | imdb.company_name.name, imdb.movie_companies.note, imdb.title.title                                                                                                                         | 2.00 MB   | N/A     |
|   └─HashJoin_50                          | 4804.48    | 9221275    | root      |                                             | time:8m37.1s, loops:9007, build_hash_table:{total:8.82ms, fetch:8.81ms, build:8.03µs}, probe:{concurrency:5, total:43m5.6s, max:8m37.1s, probe:6m22.9s, fetch:36m42.7s}                                                                                                                                                                                                                                                                                                                   | inner join, equal:[eq(imdb.movie_keyword.keyword_id, imdb.keyword.id)]                                                                                                                      | 25.7 KB   | 0 Bytes |
|     ├─IndexLookUp_236(Build)             | 134.37     | 3          | root      |                                             | time:8.72ms, loops:2, index_task: {total_time: 2.52ms, fetch_handle: 2.52ms, build: 2.78µs, wait: 1.31µs}, table_task: {total_time: 6.05ms, num: 1, concurrency: 5}                                                                                                                                                                                                                                                                                                                       |                                                                                                                                                                                             | 13.0 KB   | N/A     |
|     │ ├─IndexRangeScan_233(Build)        | 134.37     | 258        | cop[tikv] | table:k, index:keyword_idx_keyword(keyword) | time:2.49ms, loops:3, cop_task: {num: 2, max: 1.65ms, min: 712.4µs, avg: 1.18ms, p95: 1.65ms, max_proc_keys: 224, p95_proc_keys: 224, rpc_num: 2, rpc_time: 2.31ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:5, tasks:2}, scan_detail: {total_process_keys: 258, total_process_keys_size: 11868, total_keys: 262, get_snapshot_time: 537.2µs, rocksdb: {key_skipped_count: 258, block: {cache_hit_count: 8}}} | range:["based","based"], ["reven","reven"], ["seque","seque"], keep order:false                                                                                                             | N/A       | N/A     |
|     │ └─Selection_235(Probe)             | 134.37     | 3          | cop[tikv] |                                             | time:5.63ms, loops:2, cop_task: {num: 1, max: 5.49ms, proc_keys: 258, tot_proc: 4ms, rpc_num: 1, rpc_time: 5.47ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:4ms, loops:4}, scan_detail: {total_process_keys: 258, total_process_keys_size: 16408, total_keys: 265, get_snapshot_time: 533.4µs, rocksdb: {key_skipped_count: 14, block: {cache_hit_count: 407}}}                                                                                              | in(imdb.keyword.keyword, "sequel", "revenge", "based-on-novel")                                                                                                                             | N/A       | N/A     |
|     │   └─TableRowIDScan_234             | 134.37     | 258        | cop[tikv] | table:k                                     | tikv_task:{time:4ms, loops:4}                                                                                                                                                                                                                                                                                                                                                                                                                                                             | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|     └─HashJoin_64(Probe)                 | 8378871.38 | 2581940039 | root      |                                             | time:8m23.4s, loops:2521431, build_hash_table:{total:9.15s, fetch:4.07s, build:5.08s}, probe:{concurrency:5, total:42m58.4s, max:8m37.1s, probe:42m12.5s, fetch:45.9s}                                                                                                                                                                                                                                                                                                                    | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_keyword.movie_id) eq(imdb.movie_link.movie_id, imdb.movie_keyword.movie_id) eq(imdb.title.id, imdb.movie_keyword.movie_id)] | 2.32 GB   | 0 Bytes |
|       ├─HashJoin_75(Build)               | 2585150.00 | 17625104   | root      |                                             | time:4.7s, loops:17214, build_hash_table:{total:198.7µs, fetch:169µs, build:29.7µs}, probe:{concurrency:5, total:45.7s, max:9.15s, probe:26.7s, fetch:19s}                                                                                                                                                                                                                                                                                                                                | inner join, equal:[eq(imdb.movie_link.link_type_id, imdb.link_type.id)]                                                                                                                     | 9.46 KB   | 0 Bytes |
|       │ ├─TableReader_225(Build)         | 18.00      | 18         | root      |                                             | time:87.4µs, loops:2, cop_task: {num: 1, max: 1.52ms, proc_keys: 18, rpc_num: 1, rpc_time: 1.42ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                                                                                                                                   | data:TableFullScan_224                                                                                                                                                                      | 411 Bytes | N/A     |
|       │ │ └─TableFullScan_224            | 18.00      | 18         | cop[tikv] | table:lt                                    | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 18, total_process_keys_size: 486, total_keys: 19, get_snapshot_time: 482.3µs, rocksdb: {key_skipped_count: 18, block: {cache_hit_count: 2}}}                                                                                                                                                                                                                                                                              | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │ └─HashJoin_89(Probe)             | 2585150.00 | 17625104   | root      |                                             | time:3.83s, loops:17215, build_hash_table:{total:3.79s, fetch:3.02s, build:768.9ms}, probe:{concurrency:5, total:45.7s, max:9.15s, probe:26.8s, fetch:19s}                                                                                                                                                                                                                                                                                                                                | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_link.movie_id) eq(imdb.title.id, imdb.movie_link.movie_id)]                                                                 | 251.3 MB  | 0 Bytes |
|       │   ├─IndexJoin_97(Build)          | 2307846.25 | 1714499    | root      |                                             | time:3.71s, loops:1676, inner:{total:11.8s, concurrency:5, task:84, construct:2.13s, fetch:9.27s, build:367.8ms}, probe:2.97s                                                                                                                                                                                                                                                                                                                                                             | inner join, inner:TableReader_93, outer key:imdb.movie_companies.movie_id, inner key:imdb.title.id, equal cond:eq(imdb.movie_companies.movie_id, imdb.title.id)                             | 28.1 MB   | N/A     |
|       │   │ ├─HashJoin_146(Build)        | 2288639.00 | 1954290    | root      |                                             | time:1.1s, loops:1912, build_hash_table:{total:507.3ms, fetch:350.3ms, build:157ms}, probe:{concurrency:5, total:17.6s, max:3.53s, probe:14.5s, fetch:3.13s}                                                                                                                                                                                                                                                                                                                              | inner join, equal:[eq(imdb.movie_companies.company_id, imdb.company_name.id)]                                                                                                               | 38.3 MB   | 0 Bytes |
|       │   │ │ ├─TableReader_215(Build)   | 337906.00  | 337601     | root      |                                             | time:357.3ms, loops:356, cop_task: {num: 15, max: 81.7ms, min: 718µs, avg: 31.3ms, p95: 81.7ms, max_proc_keys: 55264, p95_proc_keys: 55264, tot_proc: 424ms, tot_wait: 2ms, rpc_num: 15, rpc_time: 468.5ms, copr_cache_hit_ratio: 0.07, distsql_concurrency: 15}                                                                                                                                                                                                                          | data:Selection_214                                                                                                                                                                          | 4.46 MB   | N/A     |
|       │   │ │ │ └─Selection_214          | 337906.00  | 337601     | cop[tikv] |                                             | tikv_task:{proc max:67ms, min:1ms, avg: 28.3ms, p80:63ms, p95:67ms, iters:413, tasks:15}, scan_detail: {total_process_keys: 307891, total_process_keys_size: 34167163, total_keys: 307905, get_snapshot_time: 328.6µs, rocksdb: {key_skipped_count: 307891, block: {cache_hit_count: 611}}}                                                                                                                                                                                               | ne(imdb.company_name.country_code, "[pl]")                                                                                                                                                  | N/A       | N/A     |
|       │   │ │ │   └─TableFullScan_213    | 362131.00  | 362131     | cop[tikv] | table:cn                                    | tikv_task:{proc max:61ms, min:1ms, avg: 25.3ms, p80:57ms, p95:61ms, iters:413, tasks:15}                                                                                                                                                                                                                                                                                                                                                                                                  | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │   │ │ └─HashJoin_194(Probe)      | 2288639.00 | 1980874    | root      |                                             | time:199.9ms, loops:1938, build_hash_table:{total:215.9µs, fetch:196.7µs, build:19.2µs}, probe:{concurrency:5, total:17.6s, max:3.53s, probe:16.7s, fetch:909.9ms}                                                                                                                                                                                                                                                                                                                        | inner join, equal:[eq(imdb.company_type.id, imdb.movie_companies.company_type_id)]                                                                                                          | 25.7 KB   | 0 Bytes |
|       │   │ │   ├─TableReader_205(Build) | 3.20       | 3          | root      |                                             | time:111.3µs, loops:2, cop_task: {num: 1, max: 1.02ms, proc_keys: 4, rpc_num: 1, rpc_time: 928.2µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                                                                                                                                  | data:Selection_204                                                                                                                                                                          | 399 Bytes | N/A     |
|       │   │ │   │ └─Selection_204        | 3.20       | 3          | cop[tikv] |                                             | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 4, total_process_keys_size: 224, total_keys: 5, get_snapshot_time: 29.8µs, rocksdb: {key_skipped_count: 4, block: {cache_hit_count: 2}}}                                                                                                                                                                                                                                                                                  | ne(imdb.company_type.kind, "production companies"), not(isnull(imdb.company_type.kind))                                                                                                     | N/A       | N/A     |
|       │   │ │   │   └─TableFullScan_203  | 4.00       | 4          | cop[tikv] | table:ct                                    | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                              | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │   │ │   └─TableReader_212(Probe) | 2288639.00 | 2288639    | root      |                                             | time:230.3ms, loops:2313, cop_task: {num: 85, max: 309.5ms, min: 410.1µs, avg: 28.6ms, p95: 93.1ms, max_proc_keys: 400352, p95_proc_keys: 53216, tot_proc: 2.2s, tot_wait: 10ms, rpc_num: 85, rpc_time: 2.43s, copr_cache_hit_ratio: 0.59, distsql_concurrency: 15}                                                                                                                                                                                                                       | data:Selection_211                                                                                                                                                                          | 13.7 MB   | N/A     |
|       │   │ │     └─Selection_211        | 2288639.00 | 2288639    | cop[tikv] |                                             | tikv_task:{proc max:392ms, min:1ms, avg: 57.5ms, p80:72ms, p95:198ms, iters:5182, tasks:85}, scan_detail: {total_process_keys: 1841156, total_process_keys_size: 124901007, total_keys: 1841191, get_snapshot_time: 4.28ms, rocksdb: {key_skipped_count: 1841156, block: {cache_hit_count: 2332}}}                                                                                                                                                                                        | not(isnull(imdb.movie_companies.note))                                                                                                                                                      | N/A       | N/A     |
|       │   │ │       └─TableFullScan_210  | 4958296.00 | 4958296    | cop[tikv] | table:mc                                    | tikv_task:{proc max:365ms, min:1ms, avg: 52ms, p80:64ms, p95:188ms, iters:5182, tasks:85}                                                                                                                                                                                                                                                                                                                                                                                                 | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │   │ └─TableReader_93(Probe)      | 0.88       | 911175     | root      |                                             | time:8.93s, loops:1113, cop_task: {num: 803, max: 63.5ms, min: 382.4µs, avg: 12.6ms, p95: 35.6ms, max_proc_keys: 6789, p95_proc_keys: 5088, tot_proc: 6.95s, tot_wait: 39ms, rpc_num: 803, rpc_time: 10.1s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                          | data:Selection_92                                                                                                                                                                           | N/A       | N/A     |
|       │   │   └─Selection_92             | 0.88       | 911175     | cop[tikv] |                                             | tikv_task:{proc max:57ms, min:0s, avg: 8.08ms, p80:13ms, p95:29ms, iters:3647, tasks:803}, scan_detail: {total_process_keys: 1049131, total_process_keys_size: 118038591, total_keys: 1214693, get_snapshot_time: 13.6ms, rocksdb: {key_skipped_count: 807130, block: {cache_hit_count: 586139}}}                                                                                                                                                                                         | gt(imdb.title.production_year, 1950)                                                                                                                                                        | N/A       | N/A     |
|       │   │     └─TableRangeScan_91      | 1.00       | 1049131    | cop[tikv] | table:t                                     | tikv_task:{proc max:57ms, min:0s, avg: 7.99ms, p80:13ms, p95:29ms, iters:3647, tasks:803}                                                                                                                                                                                                                                                                                                                                                                                                 | range: decided by [imdb.movie_companies.movie_id], keep order:false                                                                                                                         | N/A       | N/A     |
|       │   └─TableReader_223(Probe)       | 2585150.00 | 2585150    | root      |                                             | time:16.4ms, loops:2529, cop_task: {num: 77, max: 57.9ms, min: 573.4µs, avg: 4.05ms, p95: 52.2ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 243ms, tot_wait: 14ms, rpc_num: 77, rpc_time: 309.7ms, copr_cache_hit_ratio: 0.81, distsql_concurrency: 15}                                                                                                                                                                                                                       | data:TableFullScan_222                                                                                                                                                                      | 3.07 MB   | N/A     |
|       │     └─TableFullScan_222          | 2585150.00 | 2585150    | cop[tikv] | table:ml                                    | tikv_task:{proc max:55ms, min:0s, avg: 32.3ms, p80:51ms, p95:54ms, iters:2831, tasks:77}, scan_detail: {total_process_keys: 221984, total_process_keys_size: 11315627, total_keys: 221999, get_snapshot_time: 3.64ms, rocksdb: {key_skipped_count: 221984, block: {cache_hit_count: 243}}}                                                                                                                                                                                                | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       └─TableReader_229(Probe)           | 7480087.00 | 7480087    | root      |                                             | time:123.5ms, loops:7318, cop_task: {num: 230, max: 62.6ms, min: 476.8µs, avg: 9.56ms, p95: 59.9ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1.83s, tot_wait: 92ms, rpc_num: 230, rpc_time: 2.19s, copr_cache_hit_ratio: 0.71, distsql_concurrency: 15}                                                                                                                                                                                                                      | data:TableFullScan_228                                                                                                                                                                      | 8.43 MB   | N/A     |
|         └─TableFullScan_228              | 7480087.00 | 7480087    | cop[tikv] | table:mk                                    | tikv_task:{proc max:76ms, min:0s, avg: 30.2ms, p80:51ms, p95:56ms, iters:8220, tasks:230}, scan_detail: {total_process_keys: 1630624, total_process_keys_size: 73902635, total_keys: 1630691, get_snapshot_time: 91.7ms, rocksdb: {key_skipped_count: 1630624, block: {cache_hit_count: 1575}}}                                                                                                                                                                                           | keep order:false                                                                                                                                                                            | N/A       | N/A     |
+------------------------------------------+------------+------------+-----------+---------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+

Fast one: 333.3s

+------------------------------------------+------------+------------+-----------+---------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| ID                                       | ESTROWS    | ACTROWS    | TASK      | ACCESS OBJECT                               | EXECUTION INFO                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | OPERATOR INFO                                                                                                                                                                               | MEMORY    | DISK    |
+------------------------------------------+------------+------------+-----------+---------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| HashAgg_35                               | 1.00       | 1          | root      |                                             | time:5m33.3s, loops:2, partial_worker:{wall_time:5m33.287911652s, concurrency:5, task_num:9007, tot_wait:27m44.158596207s, tot_exec:2.276627897s, tot_time:27m46.438695181s, max:5m33.287862326s, p95:5m33.287862326s}, final_worker:{wall_time:5m33.287973757s, concurrency:5, task_num:5, tot_wait:27m46.439487721s, tot_exec:54.379µs, tot_time:27m46.439548653s, max:5m33.287934386s, p95:5m33.287934386s}                                                                           | funcs:min(imdb.company_name.name)->Column#39, funcs:min(imdb.movie_companies.note)->Column#40, funcs:min(imdb.title.title)->Column#41                                                       | 2.17 MB   | N/A     |
| └─Projection_37                          | 4589.95    | 9221275    | root      |                                             | time:5m33.3s, loops:9008, Concurrency:5                                                                                                                                                                                                                                                                                                                                                                                                                                                  | imdb.company_name.name, imdb.movie_companies.note, imdb.title.title                                                                                                                         | 2.00 MB   | N/A     |
|   └─HashJoin_50                          | 4589.95    | 9221275    | root      |                                             | time:5m33.2s, loops:9008, build_hash_table:{total:8.25ms, fetch:8.24ms, build:10.2µs}, probe:{concurrency:5, total:27m46.4s, max:5m33.3s, probe:6m34s, fetch:21m12.5s}                                                                                                                                                                                                                                                                                                                   | inner join, equal:[eq(imdb.movie_keyword.keyword_id, imdb.keyword.id)]                                                                                                                      | 25.7 KB   | 0 Bytes |
|     ├─IndexLookUp_236(Build)             | 128.37     | 3          | root      |                                             | time:8.14ms, loops:2, index_task: {total_time: 2.4ms, fetch_handle: 2.4ms, build: 1.91µs, wait: 1.5µs}, table_task: {total_time: 5.62ms, num: 1, concurrency: 5}                                                                                                                                                                                                                                                                                                                         |                                                                                                                                                                                             | 13.0 KB   | N/A     |
|     │ ├─IndexRangeScan_233(Build)        | 128.37     | 258        | cop[tikv] | table:k, index:keyword_idx_keyword(keyword) | time:2.35ms, loops:3, cop_task: {num: 2, max: 1.62ms, min: 620.9µs, avg: 1.12ms, p95: 1.62ms, max_proc_keys: 224, p95_proc_keys: 224, rpc_num: 2, rpc_time: 2.2ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:5, tasks:2}, scan_detail: {total_process_keys: 258, total_process_keys_size: 11868, total_keys: 262, get_snapshot_time: 503.5µs, rocksdb: {key_skipped_count: 258, block: {cache_hit_count: 8}}} | range:["based","based"], ["reven","reven"], ["seque","seque"], keep order:false                                                                                                             | N/A       | N/A     |
|     │ └─Selection_235(Probe)             | 128.37     | 3          | cop[tikv] |                                             | time:5.32ms, loops:2, cop_task: {num: 1, max: 5.18ms, proc_keys: 258, tot_proc: 4ms, tot_wait: 1ms, rpc_num: 1, rpc_time: 5.15ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:4ms, loops:4}, scan_detail: {total_process_keys: 258, total_process_keys_size: 16408, total_keys: 265, get_snapshot_time: 534.7µs, rocksdb: {key_skipped_count: 14, block: {cache_hit_count: 407}}}                                                                              | in(imdb.keyword.keyword, "sequel", "revenge", "based-on-novel")                                                                                                                             | N/A       | N/A     |
|     │   └─TableRowIDScan_234             | 128.37     | 258        | cop[tikv] | table:k                                     | tikv_task:{time:4ms, loops:4}                                                                                                                                                                                                                                                                                                                                                                                                                                                            | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|     └─HashJoin_64(Probe)                 | 8378871.38 | 2581940039 | root      |                                             | time:5m19.3s, loops:2521429, build_hash_table:{total:9s, fetch:3.97s, build:5.02s}, probe:{concurrency:5, total:27m36s, max:5m33.3s, probe:26m50.9s, fetch:45.2s}                                                                                                                                                                                                                                                                                                                        | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_keyword.movie_id) eq(imdb.movie_link.movie_id, imdb.movie_keyword.movie_id) eq(imdb.title.id, imdb.movie_keyword.movie_id)] | 2.32 GB   | 0 Bytes |
|       ├─HashJoin_75(Build)               | 2585150.00 | 17625104   | root      |                                             | time:4.63s, loops:17215, build_hash_table:{total:270.5µs, fetch:260.6µs, build:9.91µs}, probe:{concurrency:5, total:45s, max:8.99s, probe:26.6s, fetch:18.4s}                                                                                                                                                                                                                                                                                                                            | inner join, equal:[eq(imdb.movie_link.link_type_id, imdb.link_type.id)]                                                                                                                     | 9.46 KB   | 0 Bytes |
|       │ ├─TableReader_225(Build)         | 18.00      | 18         | root      |                                             | time:91.6µs, loops:2, cop_task: {num: 1, max: 1.46ms, proc_keys: 18, rpc_num: 1, rpc_time: 1.41ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                                                                                                                                  | data:TableFullScan_224                                                                                                                                                                      | 415 Bytes | N/A     |
|       │ │ └─TableFullScan_224            | 18.00      | 18         | cop[tikv] | table:lt                                    | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 18, total_process_keys_size: 486, total_keys: 19, get_snapshot_time: 520.8µs, rocksdb: {key_skipped_count: 18, block: {cache_hit_count: 2}}}                                                                                                                                                                                                                                                                             | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │ └─HashJoin_89(Probe)             | 2585150.00 | 17625104   | root      |                                             | time:3.69s, loops:17215, build_hash_table:{total:3.65s, fetch:2.92s, build:729ms}, probe:{concurrency:5, total:44.9s, max:8.99s, probe:26.6s, fetch:18.3s}                                                                                                                                                                                                                                                                                                                               | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_link.movie_id) eq(imdb.title.id, imdb.movie_link.movie_id)]                                                                 | 251.1 MB  | 0 Bytes |
|       │   ├─IndexJoin_97(Build)          | 2307846.25 | 1714499    | root      |                                             | time:3.6s, loops:1676, inner:{total:10.9s, concurrency:5, task:84, construct:1.96s, fetch:8.59s, build:321.5ms}, probe:2.86s                                                                                                                                                                                                                                                                                                                                                             | inner join, inner:TableReader_93, outer key:imdb.movie_companies.movie_id, inner key:imdb.title.id, equal cond:eq(imdb.movie_companies.movie_id, imdb.title.id)                             | 27.9 MB   | N/A     |
|       │   │ ├─HashJoin_146(Build)        | 2288639.00 | 1954290    | root      |                                             | time:1.09s, loops:1913, build_hash_table:{total:539.4ms, fetch:371.4ms, build:167.9ms}, probe:{concurrency:5, total:17.2s, max:3.43s, probe:14.1s, fetch:3.09s}                                                                                                                                                                                                                                                                                                                          | inner join, equal:[eq(imdb.movie_companies.company_id, imdb.company_name.id)]                                                                                                               | 38.3 MB   | 0 Bytes |
|       │   │ │ ├─TableReader_215(Build)   | 337902.00  | 337601     | root      |                                             | time:378.5ms, loops:356, cop_task: {num: 15, max: 80.9ms, min: 529.4µs, avg: 34.3ms, p95: 80.9ms, max_proc_keys: 55264, p95_proc_keys: 55264, tot_proc: 461ms, rpc_num: 15, rpc_time: 513.6ms, copr_cache_hit_ratio: 0.07, distsql_concurrency: 15}                                                                                                                                                                                                                                      | data:Selection_214                                                                                                                                                                          | 4.46 MB   | N/A     |
|       │   │ │ │ └─Selection_214          | 337902.00  | 337601     | cop[tikv] |                                             | tikv_task:{proc max:69ms, min:0s, avg: 27.3ms, p80:57ms, p95:69ms, iters:413, tasks:15}, scan_detail: {total_process_keys: 360115, total_process_keys_size: 39951058, total_keys: 360129, get_snapshot_time: 294.3µs, rocksdb: {key_skipped_count: 360115, block: {cache_hit_count: 710}}}                                                                                                                                                                                               | ne(imdb.company_name.country_code, "[pl]")                                                                                                                                                  | N/A       | N/A     |
|       │   │ │ │   └─TableFullScan_213    | 362131.00  | 362131     | cop[tikv] | table:cn                                    | tikv_task:{proc max:60ms, min:0s, avg: 23.9ms, p80:50ms, p95:60ms, iters:413, tasks:15}                                                                                                                                                                                                                                                                                                                                                                                                  | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │   │ │ └─HashJoin_194(Probe)      | 2288639.00 | 1980874    | root      |                                             | time:165.5ms, loops:1938, build_hash_table:{total:123.9µs, fetch:118.9µs, build:5.07µs}, probe:{concurrency:5, total:17.1s, max:3.43s, probe:16.9s, fetch:293.9ms}                                                                                                                                                                                                                                                                                                                       | inner join, equal:[eq(imdb.company_type.id, imdb.movie_companies.company_type_id)]                                                                                                          | 25.7 KB   | 0 Bytes |
|       │   │ │   ├─TableReader_205(Build) | 3.20       | 3          | root      |                                             | time:50µs, loops:2, cop_task: {num: 1, max: 1.6ms, proc_keys: 4, rpc_num: 1, rpc_time: 1.54ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                                                                                                                                      | data:Selection_204                                                                                                                                                                          | 403 Bytes | N/A     |
|       │   │ │   │ └─Selection_204        | 3.20       | 3          | cop[tikv] |                                             | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 4, total_process_keys_size: 224, total_keys: 5, get_snapshot_time: 587.8µs, rocksdb: {key_skipped_count: 4, block: {cache_hit_count: 2}}}                                                                                                                                                                                                                                                                                | ne(imdb.company_type.kind, "production companies"), not(isnull(imdb.company_type.kind))                                                                                                     | N/A       | N/A     |
|       │   │ │   │   └─TableFullScan_203  | 4.00       | 4          | cop[tikv] | table:ct                                    | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                             | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │   │ │   └─TableReader_212(Probe) | 2288639.00 | 2288639    | root      |                                             | time:78.4ms, loops:2312, cop_task: {num: 85, max: 481.7ms, min: 377.9µs, avg: 29.7ms, p95: 86.3ms, max_proc_keys: 542688, p95_proc_keys: 53216, tot_proc: 2.34s, tot_wait: 3ms, rpc_num: 85, rpc_time: 2.52s, copr_cache_hit_ratio: 0.58, distsql_concurrency: 15}                                                                                                                                                                                                                       | data:Selection_211                                                                                                                                                                          | 12.4 MB   | N/A     |
|       │   │ │     └─Selection_211        | 2288639.00 | 2288639    | cop[tikv] |                                             | tikv_task:{proc max:462ms, min:0s, avg: 58.3ms, p80:70ms, p95:189ms, iters:5182, tasks:85}, scan_detail: {total_process_keys: 2050004, total_process_keys_size: 131126555, total_keys: 2050040, get_snapshot_time: 1.5ms, rocksdb: {key_skipped_count: 2050004, block: {cache_hit_count: 2465}}}                                                                                                                                                                                         | not(isnull(imdb.movie_companies.note))                                                                                                                                                      | N/A       | N/A     |
|       │   │ │       └─TableFullScan_210  | 4958296.00 | 4958296    | cop[tikv] | table:mc                                    | tikv_task:{proc max:438ms, min:0s, avg: 52.6ms, p80:60ms, p95:173ms, iters:5182, tasks:85}                                                                                                                                                                                                                                                                                                                                                                                               | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │   │ └─TableReader_93(Probe)      | 0.89       | 911192     | root      |                                             | time:8.3s, loops:1113, cop_task: {num: 829, max: 54.5ms, min: 441.3µs, avg: 11.7ms, p95: 32.2ms, max_proc_keys: 6846, p95_proc_keys: 5088, tot_proc: 6.63s, tot_wait: 68ms, rpc_num: 830, rpc_time: 9.66s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, backoff{regionMiss: 2ms}                                                                                                                                                                                                | data:Selection_92                                                                                                                                                                           | N/A       | N/A     |
|       │   │   └─Selection_92             | 0.89       | 911192     | cop[tikv] |                                             | tikv_task:{proc max:50ms, min:0s, avg: 7.48ms, p80:13ms, p95:26ms, iters:3756, tasks:829}, scan_detail: {total_process_keys: 1049148, total_process_keys_size: 118040541, total_keys: 1214742, get_snapshot_time: 16.6ms, rocksdb: {key_skipped_count: 807279, block: {cache_hit_count: 584311, read_count: 1873, read_byte: 39.8 MB, read_time: 19.8ms}}}                                                                                                                               | gt(imdb.title.production_year, 1950)                                                                                                                                                        | N/A       | N/A     |
|       │   │     └─TableRangeScan_91      | 1.00       | 1049148    | cop[tikv] | table:t                                     | tikv_task:{proc max:49ms, min:0s, avg: 7.4ms, p80:13ms, p95:26ms, iters:3756, tasks:829}                                                                                                                                                                                                                                                                                                                                                                                                 | range: decided by [imdb.movie_companies.movie_id], keep order:false                                                                                                                         | N/A       | N/A     |
|       │   └─TableReader_223(Probe)       | 2585150.00 | 2585150    | root      |                                             | time:17ms, loops:2529, cop_task: {num: 77, max: 62ms, min: 473.5µs, avg: 18.2ms, p95: 59.5ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1.29s, tot_wait: 9ms, rpc_num: 77, rpc_time: 1.4s, copr_cache_hit_ratio: 0.55, distsql_concurrency: 15}                                                                                                                                                                                                                              | data:TableFullScan_222                                                                                                                                                                      | 3.07 MB   | N/A     |
|       │     └─TableFullScan_222          | 2585150.00 | 2585150    | cop[tikv] | table:ml                                    | tikv_task:{proc max:58ms, min:0s, avg: 32.2ms, p80:51ms, p95:53ms, iters:2831, tasks:77}, scan_detail: {total_process_keys: 1195011, total_process_keys_size: 60932204, total_keys: 1195046, get_snapshot_time: 3.79ms, rocksdb: {key_skipped_count: 1195011, block: {cache_hit_count: 1227}}}                                                                                                                                                                                           | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       └─TableReader_229(Probe)           | 7480087.00 | 7480087    | root      |                                             | time:111.6ms, loops:7317, cop_task: {num: 230, max: 62.2ms, min: 542.5µs, avg: 14.5ms, p95: 60.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 2.95s, tot_wait: 59ms, rpc_num: 230, rpc_time: 3.33s, copr_cache_hit_ratio: 0.57, distsql_concurrency: 15}                                                                                                                                                                                                                     | data:TableFullScan_228                                                                                                                                                                      | 8.43 MB   | N/A     |
|         └─TableFullScan_228              | 7480087.00 | 7480087    | cop[tikv] | table:mk                                    | tikv_task:{proc max:68ms, min:0s, avg: 31.1ms, p80:51ms, p95:55ms, iters:8220, tasks:230}, scan_detail: {total_process_keys: 2695352, total_process_keys_size: 122173332, total_keys: 2695451, get_snapshot_time: 58.1ms, rocksdb: {key_skipped_count: 2695352, block: {cache_hit_count: 2573}}}                                                                                                                                                                                         | keep order:false                                                                                                                                                                            | N/A       | N/A     |
+------------------------------------------+------------+------------+-----------+---------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+

The main difference between these two executions is the probe time of the second join.
The first one is

probe:{concurrency:5, total:42m58.4s, max:8m37.1s, probe:42m12.5s, fetch:45.9s}

The second one is

probe:{concurrency:5, total:27m36s, max:5m33.3s, probe:26m50.9s, fetch:45.2s}                                                                                                                                                                                                                                                                                                                        

In the flame graph, I find the rLock.RLock and rLock.RULock of mutexForRowContainer is extremely slow.

tidb/util/chunk/row_container.go

Lines 38 to 50 in aab394f

	type mutexForRowContainer struct {
	// RWMutex guarantees spill and get operator for rowContainer is mutually exclusive.
	// `rLock` and `wLocks` is introduced to reduce the contention when multiple
	// goroutine touch the same rowContainer concurrently. If there are multiple
	// goroutines touch the same rowContainer concurrently, it's recommended to
	// use RowContainer.ShallowCopyWithNewMutex to build a new RowContainer for
	// each goroutine. Thus each goroutine holds its own rLock but share the same
	// underlying data, which can reduce the contention on m.rLock remarkably and
	// get better performance.
	rLock *sync.RWMutex
	wLocks []*sync.RWMutex
	records *rowContainerRecord
	}

As the comments said, each goroutine holds its own rLock. So rLock should not have any contention if there is no spill disk operation. Actually, the spill disk operation doesn't exist in these executions.

If there is no contention, rLock.rlock will just add one to readerCount, an atomic variable in RWMutex.

In consideration of this, I extrapolate it's due to false sharing.

I filed a PR(#37627) to verify this extrapolation.

The result is amazing. The time of this query is almost twice as fast as the slow one.

After Patch: 249.8s

See the result of explain analyze below due to the issue that this comment is too long.

Test 4 times and the results are 249.8s, 244.2s, 247.1s, and 250.1s.
Furthermore, the execution time is stable after patching this demo PR.

After discussing with @XuHuaiyu, it's better to use a more elegant method instead of just adding padding for this RWMutex to fix this issue because there may be more false sharing issues in this join implementation.

[gengliqi]

After Patch: 249.8s

+------------------------------------------+------------+------------+-----------+---------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| ID                                       | ESTROWS    | ACTROWS    | TASK      | ACCESS OBJECT                               | EXECUTION INFO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | OPERATOR INFO                                                                                                                                                                               | MEMORY    | DISK    |
+------------------------------------------+------------+------------+-----------+---------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| HashAgg_35                               | 1.00       | 1          | root      |                                             | time:4m9.8s, loops:2, partial_worker:{wall_time:4m9.774416914s, concurrency:5, task_num:9009, tot_wait:20m47.334251049s, tot_exec:1.534655129s, tot_time:20m48.871908639s, max:4m9.774388024s, p95:4m9.774388024s}, final_worker:{wall_time:4m9.774441734s, concurrency:5, task_num:5, tot_wait:20m48.872027779s, tot_exec:33.331µs, tot_time:20m48.87206507s, max:4m9.774421054s, p95:4m9.774421054s}                                                                                        | funcs:min(imdb.company_name.name)->Column#39, funcs:min(imdb.movie_companies.note)->Column#40, funcs:min(imdb.title.title)->Column#41                                                       | 2.14 MB   | N/A     |
| └─Projection_37                          | 4447.37    | 9221275    | root      |                                             | time:4m9.8s, loops:9010, Concurrency:5                                                                                                                                                                                                                                                                                                                                                                                                                                                        | imdb.company_name.name, imdb.movie_companies.note, imdb.title.title                                                                                                                         | 1.99 MB   | N/A     |
|   └─HashJoin_50                          | 4447.37    | 9221275    | root      |                                             | time:4m9.7s, loops:9010, build_hash_table:{total:4.01ms, fetch:4.01ms, build:5.01µs}, probe:{concurrency:5, total:20m48.9s, max:4m9.8s, probe:3m58.3s, fetch:16m50.5s}                                                                                                                                                                                                                                                                                                                        | inner join, equal:[eq(imdb.movie_keyword.keyword_id, imdb.keyword.id)]                                                                                                                      | 25.7 KB   | 0 Bytes |
|     ├─IndexLookUp_236(Build)             | 124.38     | 3          | root      |                                             | time:3.97ms, loops:2, index_task: {total_time: 942.2µs, fetch_handle: 939.8µs, build: 710ns, wait: 1.72µs}, table_task: {total_time: 2.93ms, num: 1, concurrency: 5}                                                                                                                                                                                                                                                                                                                          |                                                                                                                                                                                             | 13.0 KB   | N/A     |
|     │ ├─IndexRangeScan_233(Build)        | 124.38     | 258        | cop[tikv] | table:k, index:keyword_idx_keyword(keyword) | time:924.2µs, loops:3, cop_task: {num: 2, max: 520.2µs, min: 346.2µs, avg: 433.2µs, p95: 520.2µs, max_proc_keys: 224, p95_proc_keys: 224, rpc_num: 2, rpc_time: 841.5µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:5, tasks:2}, scan_detail: {total_process_keys: 258, total_process_keys_size: 11868, total_keys: 262, get_snapshot_time: 15.5µs, rocksdb: {key_skipped_count: 258, block: {cache_hit_count: 8}}} | range:["based","based"], ["reven","reven"], ["seque","seque"], keep order:false                                                                                                             | N/A       | N/A     |
|     │ └─Selection_235(Probe)             | 124.38     | 3          | cop[tikv] |                                             | time:2.72ms, loops:2, cop_task: {num: 1, max: 2.65ms, proc_keys: 258, tot_proc: 2ms, rpc_num: 1, rpc_time: 2.64ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:4}, scan_detail: {total_process_keys: 258, total_process_keys_size: 16408, total_keys: 265, get_snapshot_time: 8.38µs, rocksdb: {key_skipped_count: 14, block: {cache_hit_count: 407}}}                                                                                                    | in(imdb.keyword.keyword, "sequel", "revenge", "based-on-novel")                                                                                                                             | N/A       | N/A     |
|     │   └─TableRowIDScan_234             | 124.38     | 258        | cop[tikv] | table:k                                     | tikv_task:{time:0s, loops:4}                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|     └─HashJoin_64(Probe)                 | 8378871.38 | 2581940039 | root      |                                             | time:4m5.4s, loops:2521428, build_hash_table:{total:6.79s, fetch:2.53s, build:4.25s}, probe:{concurrency:5, total:20m39.8s, max:4m9.8s, probe:20m5.8s, fetch:34s}                                                                                                                                                                                                                                                                                                                             | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_keyword.movie_id) eq(imdb.movie_link.movie_id, imdb.movie_keyword.movie_id) eq(imdb.title.id, imdb.movie_keyword.movie_id)] | 2.32 GB   | 0 Bytes |
|       ├─HashJoin_75(Build)               | 2585150.00 | 17625104   | root      |                                             | time:2.53s, loops:17215, build_hash_table:{total:79.4µs, fetch:69.7µs, build:9.74µs}, probe:{concurrency:5, total:33.9s, max:6.79s, probe:21.5s, fetch:12.4s}                                                                                                                                                                                                                                                                                                                                 | inner join, equal:[eq(imdb.movie_link.link_type_id, imdb.link_type.id)]                                                                                                                     | 9.46 KB   | 0 Bytes |
|       │ ├─TableReader_225(Build)         | 18.00      | 18         | root      |                                             | time:41.8µs, loops:2, cop_task: {num: 1, max: 584.1µs, proc_keys: 18, rpc_num: 1, rpc_time: 551.7µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                                                                                                                                     | data:TableFullScan_224                                                                                                                                                                      | 415 Bytes | N/A     |
|       │ │ └─TableFullScan_224            | 18.00      | 18         | cop[tikv] | table:lt                                    | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 18, total_process_keys_size: 486, total_keys: 19, get_snapshot_time: 19.1µs, rocksdb: {key_skipped_count: 18, block: {cache_hit_count: 2}}}                                                                                                                                                                                                                                                                                   | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │ └─HashJoin_89(Probe)             | 2585150.00 | 17625104   | root      |                                             | time:2.5s, loops:17216, build_hash_table:{total:2.46s, fetch:1.86s, build:598.6ms}, probe:{concurrency:5, total:33.9s, max:6.79s, probe:21.6s, fetch:12.3s}                                                                                                                                                                                                                                                                                                                                   | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_link.movie_id) eq(imdb.title.id, imdb.movie_link.movie_id)]                                                                 | 251.4 MB  | 0 Bytes |
|       │   ├─IndexJoin_97(Build)          | 2307846.25 | 1714499    | root      |                                             | time:2.42s, loops:1676, inner:{total:9.37s, concurrency:5, task:84, construct:2.11s, fetch:7.01s, build:248ms}, probe:1.5s                                                                                                                                                                                                                                                                                                                                                                    | inner join, inner:TableReader_93, outer key:imdb.movie_companies.movie_id, inner key:imdb.title.id, equal cond:eq(imdb.movie_companies.movie_id, imdb.title.id)                             | 27.6 MB   | N/A     |
|       │   │ ├─HashJoin_146(Build)        | 2288639.00 | 1954290    | root      |                                             | time:730.9ms, loops:1912, build_hash_table:{total:408.1ms, fetch:285.5ms, build:122.6ms}, probe:{concurrency:5, total:11.5s, max:2.3s, probe:9.38s, fetch:2.11s}                                                                                                                                                                                                                                                                                                                              | inner join, equal:[eq(imdb.movie_companies.company_id, imdb.company_name.id)]                                                                                                               | 38.3 MB   | 0 Bytes |
|       │   │ │ ├─TableReader_215(Build)   | 337880.00  | 337601     | root      |                                             | time:292.2ms, loops:356, cop_task: {num: 15, max: 60.4ms, min: 929µs, avg: 26.3ms, p95: 60.4ms, max_proc_keys: 55264, p95_proc_keys: 55264, tot_proc: 353ms, rpc_num: 15, rpc_time: 394.1ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                                             | data:Selection_214                                                                                                                                                                          | 4.46 MB   | N/A     |
|       │   │ │ │ └─Selection_214          | 337880.00  | 337601     | cop[tikv] |                                             | tikv_task:{proc max:60ms, min:0s, avg: 22.7ms, p80:50ms, p95:60ms, iters:413, tasks:15}, scan_detail: {total_process_keys: 362131, total_process_keys_size: 40171508, total_keys: 362146, get_snapshot_time: 489.3µs, rocksdb: {key_skipped_count: 362131, block: {cache_hit_count: 715}}}                                                                                                                                                                                                    | ne(imdb.company_name.country_code, "[pl]")                                                                                                                                                  | N/A       | N/A     |
|       │   │ │ │   └─TableFullScan_213    | 362131.00  | 362131     | cop[tikv] | table:cn                                    | tikv_task:{proc max:50ms, min:0s, avg: 20.7ms, p80:50ms, p95:50ms, iters:413, tasks:15}                                                                                                                                                                                                                                                                                                                                                                                                       | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │   │ │ └─HashJoin_194(Probe)      | 2288639.00 | 1980874    | root      |                                             | time:23.7ms, loops:1939, build_hash_table:{total:81.1µs, fetch:75.3µs, build:5.77µs}, probe:{concurrency:5, total:11.5s, max:2.3s, probe:11.4s, fetch:59.3ms}                                                                                                                                                                                                                                                                                                                                 | inner join, equal:[eq(imdb.company_type.id, imdb.movie_companies.company_type_id)]                                                                                                          | 25.7 KB   | 0 Bytes |
|       │   │ │   ├─TableReader_205(Build) | 3.20       | 3          | root      |                                             | time:37µs, loops:2, cop_task: {num: 1, max: 620.4µs, proc_keys: 4, rpc_num: 1, rpc_time: 593.5µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                                                                                                                                        | data:Selection_204                                                                                                                                                                          | 409 Bytes | N/A     |
|       │   │ │   │ └─Selection_204        | 3.20       | 3          | cop[tikv] |                                             | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 4, total_process_keys_size: 224, total_keys: 5, get_snapshot_time: 226.3µs, rocksdb: {key_skipped_count: 4, block: {cache_hit_count: 2}}}                                                                                                                                                                                                                                                                                     | ne(imdb.company_type.kind, "production companies"), not(isnull(imdb.company_type.kind))                                                                                                     | N/A       | N/A     |
|       │   │ │   │   └─TableFullScan_203  | 4.00       | 4          | cop[tikv] | table:ct                                    | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │   │ │   └─TableReader_212(Probe) | 2288639.00 | 2288639    | root      |                                             | time:21ms, loops:2311, cop_task: {num: 85, max: 2.26ms, min: 189.3µs, avg: 457.4µs, p95: 1.37ms, max_proc_keys: 2016, p95_proc_keys: 992, tot_proc: 4ms, rpc_num: 85, rpc_time: 37.6ms, copr_cache_hit_ratio: 0.87, distsql_concurrency: 15}                                                                                                                                                                                                                                                  | data:Selection_211                                                                                                                                                                          | 11.0 MB   | N/A     |
|       │   │ │     └─Selection_211        | 2288639.00 | 2288639    | cop[tikv] |                                             | tikv_task:{proc max:490ms, min:0s, avg: 53.9ms, p80:60ms, p95:190ms, iters:5182, tasks:85}, scan_detail: {total_process_keys: 8096, total_process_keys_size: 532858, total_keys: 8107, get_snapshot_time: 788.4µs, rocksdb: {key_skipped_count: 8096, block: {cache_hit_count: 31}}}                                                                                                                                                                                                          | not(isnull(imdb.movie_companies.note))                                                                                                                                                      | N/A       | N/A     |
|       │   │ │       └─TableFullScan_210  | 4958296.00 | 4958296    | cop[tikv] | table:mc                                    | tikv_task:{proc max:460ms, min:0s, avg: 50.2ms, p80:60ms, p95:190ms, iters:5182, tasks:85}                                                                                                                                                                                                                                                                                                                                                                                                    | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       │   │ └─TableReader_93(Probe)      | 0.88       | 911125     | root      |                                             | time:6.75s, loops:1112, cop_task: {num: 827, max: 44.2ms, min: 276.1µs, avg: 9.42ms, p95: 26.9ms, max_proc_keys: 6746, p95_proc_keys: 5088, tot_proc: 5s, rpc_num: 827, rpc_time: 7.78s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                                                 | data:Selection_92                                                                                                                                                                           | N/A       | N/A     |
|       │   │   └─Selection_92             | 0.88       | 911125     | cop[tikv] |                                             | tikv_task:{proc max:40ms, min:0s, avg: 6.3ms, p80:10ms, p95:20ms, iters:3765, tasks:827}, scan_detail: {total_process_keys: 1049083, total_process_keys_size: 118033599, total_keys: 1214615, get_snapshot_time: 9.84ms, rocksdb: {key_skipped_count: 807262, block: {cache_hit_count: 585904}}}                                                                                                                                                                                              | gt(imdb.title.production_year, 1950)                                                                                                                                                        | N/A       | N/A     |
|       │   │     └─TableRangeScan_91      | 1.00       | 1049083    | cop[tikv] | table:t                                     | tikv_task:{proc max:40ms, min:0s, avg: 6.28ms, p80:10ms, p95:20ms, iters:3765, tasks:827}                                                                                                                                                                                                                                                                                                                                                                                                     | range: decided by [imdb.movie_companies.movie_id], keep order:false                                                                                                                         | N/A       | N/A     |
|       │   └─TableReader_223(Probe)       | 2585150.00 | 2585150    | root      |                                             | time:15.1ms, loops:2529, cop_task: {num: 77, max: 2.35ms, min: 297.7µs, avg: 578.1µs, p95: 2ms, max_proc_keys: 2016, p95_proc_keys: 1563, tot_proc: 4ms, rpc_num: 77, rpc_time: 42.9ms, copr_cache_hit_ratio: 0.83, distsql_concurrency: 15}                                                                                                                                                                                                                                                  | data:TableFullScan_222                                                                                                                                                                      | 3.07 MB   | N/A     |
|       │     └─TableFullScan_222          | 2585150.00 | 2585150    | cop[tikv] | table:ml                                    | tikv_task:{proc max:50ms, min:0s, avg: 27ms, p80:40ms, p95:50ms, iters:2831, tasks:77}, scan_detail: {total_process_keys: 12699, total_process_keys_size: 639174, total_keys: 12712, get_snapshot_time: 781.2µs, rocksdb: {key_skipped_count: 12699, block: {cache_hit_count: 37}}}                                                                                                                                                                                                           | keep order:false                                                                                                                                                                            | N/A       | N/A     |
|       └─TableReader_229(Probe)           | 7480087.00 | 7480087    | root      |                                             | time:50.8ms, loops:7317, cop_task: {num: 230, max: 2.32ms, min: 344.9µs, avg: 747.5µs, p95: 1.76ms, max_proc_keys: 2016, p95_proc_keys: 992, tot_proc: 10ms, rpc_num: 230, rpc_time: 166.4ms, copr_cache_hit_ratio: 0.83, distsql_concurrency: 15}                                                                                                                                                                                                                                            | data:TableFullScan_228                                                                                                                                                                      | 8.43 MB   | N/A     |
|         └─TableFullScan_228              | 7480087.00 | 7480087    | cop[tikv] | table:mk                                    | tikv_task:{proc max:50ms, min:0s, avg: 26.6ms, p80:40ms, p95:50ms, iters:8220, tasks:230}, scan_detail: {total_process_keys: 37120, total_process_keys_size: 1673770, total_keys: 37160, get_snapshot_time: 22ms, rocksdb: {key_skipped_count: 37120, block: {cache_hit_count: 110}}}                                                                                                                                                                                                         | keep order:false                                                                                                                                                                            | N/A       | N/A     |
+------------------------------------------+------------+------------+-----------+---------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+

[gengliqi]

Update: 190s

When fixing this issue, I find there is a mistake in my prior description.

The rlock is only one for all workers in the current implementation and it's a bug obviously.

So the performance issue here is not only false sharing but also true sharing.

After fixing this true sharing issue in #37627, the execution time can reduce to 190s.
Test 4 times and the result is 190.4s, 189.9s, 196.7s, and 189.7s.

The flame graph shows the time of rLock.RLock and rLock.RULock of mutexForRowContainer is reduced to 3.2s.

I also test the case without padding and with a lock per worker.
The time is a little bit less than the master's and still unstable.
Test 4 times and the results are 404.2s, 312.2s, 413.1s, and 449.5s.

Conclusion

The performance issue is caused by false sharing and true sharing of rlock.
Furthermore, the false sharing issue occupies the dominant factor.