fine grained shuffle doesn't scale well when stream count is very large
Open
@guo-shaoge is already working on this.
Since Mar 25, 2025.
type/enhancement
- Dominant language
- C++
- Stars
- 1k
- Forks
- 423
- Avg merge
- 1d 15h
- Merged PRs (30d)
- 24
Description
Enhancement
- prepare a big table(I use ossinsight workload, which has 1.9B rows)
- run the follow sql:
explain analyze select lead(type) over(partition by repo_name, actor_login, language) from github_events;
mysql> set @@tiflash_fine_grained_shuffle_stream_count=1024;
Query OK, 0 rows affected (0.00 sec)
mysql> explain analyze select lead(type) over(partition by repo_name, actor_login, language) from github_events;
ERROR 1146 (42S02): Table 'test.github_events' doesn't exist
mysql> use gharchive_dev;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> explain analyze select lead(type) over(partition by repo_name, actor_login, language) from github_events;
+------------------------------------+----------+------------+--------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+----------+------------+--------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| TableReader_26 | 10000.00 | 1944093760 | root | partition:all | time:3m10.6s, open:11.9ms, close:6.47µs, loops:1898603, RU:6094906.64, cop_task: {num: 29741, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 3, data:ExchangeSender_25 | 2.24 MB | N/A |
| └─ExchangeSender_25 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:3m10.7s, min:3m3s, avg: 3m6.8s, p80:3m10.7s, p95:3m10.7s, iters:29739, tasks:2, threads:144}, tiflash_network: {inner_zone_send_bytes: 31218262943} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_8 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:3m9.8s, min:3m2s, avg: 3m5.9s, p80:3m9.8s, p95:3m9.8s, iters:29739, tasks:2, threads:144} | Column#35->Column#36, stream_count: 1024 | N/A | N/A |
| └─Window_24 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:3m9.8s, min:3m2s, avg: 3m5.9s, p80:3m9.8s, p95:3m9.8s, iters:29739, tasks:2, threads:144} | lead(gharchive_dev.github_events.type)->Column#35 over(partition by gharchive_dev.github_events.repo_name, gharchive_dev.github_events.actor_login, gharchive_dev.github_events.language), stream_count: 1024 | N/A | N/A |
| └─Sort_14 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:3m6.1s, min:2m58.8s, avg: 3m2.5s, p80:3m6.1s, p95:3m6.1s, iters:29739, tasks:2, threads:144} | gharchive_dev.github_events.repo_name, gharchive_dev.github_events.actor_login, gharchive_dev.github_events.language, stream_count: 1024 | N/A | N/A |
| └─ExchangeReceiver_13 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:2m7.6s, min:2m7.6s, avg: 2m7.6s, p80:2m7.6s, p95:2m7.6s, iters:179932, tasks:2, threads:144}, tiflash_wait: {pipeline_queue_wait: 14243ms}, tiflash_network: {inner_zone_receive_bytes: 31114381577} | stream_count: 1024 | N/A | N/A |
| └─ExchangeSender_12 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:2m15.6s, min:0s, avg: 1m7.8s, p80:2m15.6s, p95:2m15.6s, iters:31006, tasks:2, threads:144}, tiflash_network: {inner_zone_send_bytes: 31114381577} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: gharchive_dev.github_events.repo_name, collate: utf8mb4_bin], [name: gharchive_dev.github_events.actor_login, collate: utf8mb4_bin], [name: gharchive_dev.github_events.language, collate: utf8mb4_bin], stream_count: 1024 | N/A | N/A |
| └─TableFullScan_11 | 10000.00 | 1944093760 | mpp[tiflash] | table:github_events | tiflash_task:{proc max:9.77s, min:0s, avg: 4.88s, p80:9.77s, p95:9.77s, iters:31006, tasks:2, threads:144}, tiflash_wait: {pipeline_queue_wait: 3971ms}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, local_regions:2205, remote_regions:0, tot_learner_read:98ms, region_balance:{instance_num: 2, max/min: 1105/1100=1.004545}, delta_rows:0, delta_bytes:0, segments:2534, stale_read_regions:0, tot_build_snapshot:13ms, tot_build_bitmap:71ms, tot_build_inputstream:24885ms, min_local_stream:3103ms, max_local_stream:9701ms, dtfile:{data_scanned_rows:1944093760, data_skipped_rows:14980722, mvcc_scanned_rows:0, mvcc_skipped_rows:0, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:5543ms, tot_read:1087595ms}} | keep order:false, stats:pseudo, PartitionTableScan:true | N/A | N/A |
+------------------------------------+----------+------------+--------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
8 rows in set (3 min 10.75 sec)
mysql> explain analyze select lead(type) over(partition by repo_name, actor_login, language) from github_events;
+------------------------------------+----------+------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+----------+------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| TableReader_26 | 10000.00 | 1944093760 | root | partition:all | time:3m7.9s, open:7.74ms, close:13.3µs, loops:1898603, RU:6597391.64, cop_task: {num: 29741, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 3, data:ExchangeSender_25 | 2.20 MB | N/A |
| └─ExchangeSender_25 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:3m8s, min:3m5.4s, avg: 3m6.7s, p80:3m8s, p95:3m8s, iters:29739, tasks:2, threads:144}, tiflash_network: {inner_zone_send_bytes: 31218507888} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_8 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:3m6.4s, min:3m4.1s, avg: 3m5.3s, p80:3m6.4s, p95:3m6.4s, iters:29739, tasks:2, threads:144} | Column#35->Column#36, stream_count: 1024 | N/A | N/A |
| └─Window_24 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:3m6.4s, min:3m4.1s, avg: 3m5.2s, p80:3m6.4s, p95:3m6.4s, iters:29739, tasks:2, threads:144} | lead(gharchive_dev.github_events.type)->Column#35 over(partition by gharchive_dev.github_events.repo_name, gharchive_dev.github_events.actor_login, gharchive_dev.github_events.language), stream_count: 1024 | N/A | N/A |
| └─Sort_14 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:3m1.5s, min:2m59s, avg: 3m0.3s, p80:3m1.5s, p95:3m1.5s, iters:29739, tasks:2, threads:144} | gharchive_dev.github_events.repo_name, gharchive_dev.github_events.actor_login, gharchive_dev.github_events.language, stream_count: 1024 | N/A | N/A |
| └─ExchangeReceiver_13 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:1m43.9s, min:1m43.9s, avg: 1m43.9s, p80:1m43.9s, p95:1m43.9s, iters:181739, tasks:2, threads:144}, tiflash_wait: {pipeline_queue_wait: 12324ms}, tiflash_network: {inner_zone_receive_bytes: 31136050865} | stream_count: 1024 | N/A | N/A |
| └─ExchangeSender_12 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:1m51.2s, min:0s, avg: 55.6s, p80:1m51.2s, p95:1m51.2s, iters:31007, tasks:2, threads:144}, tiflash_network: {inner_zone_send_bytes: 31136050865} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: gharchive_dev.github_events.repo_name, collate: utf8mb4_bin], [name: gharchive_dev.github_events.actor_login, collate: utf8mb4_bin], [name: gharchive_dev.github_events.language, collate: utf8mb4_bin], stream_count: 1024 | N/A | N/A |
| └─TableFullScan_11 | 10000.00 | 1944093760 | mpp[tiflash] | table:github_events | tiflash_task:{proc max:10.2s, min:0s, avg: 5.1s, p80:10.2s, p95:10.2s, iters:31007, tasks:2, threads:144}, tiflash_wait: {pipeline_queue_wait: 3269ms}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, local_regions:2205, remote_regions:0, tot_learner_read:51ms, region_balance:{instance_num: 2, max/min: 1105/1100=1.004545}, delta_rows:0, delta_bytes:0, segments:2534, stale_read_regions:0, tot_build_snapshot:7ms, tot_build_bitmap:62ms, tot_build_inputstream:34959ms, min_local_stream:3629ms, max_local_stream:10166ms, dtfile:{data_scanned_rows:1944093760, data_skipped_rows:14974079, mvcc_scanned_rows:0, mvcc_skipped_rows:0, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:1861ms, tot_read:877209ms}} | keep order:false, stats:pseudo, PartitionTableScan:true | N/A | N/A |
+------------------------------------+----------+------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
8 rows in set (3 min 8.05 sec)
mysql> set @@tiflash_fine_grained_shuffle_stream_count=64;
Query OK, 0 rows affected (0.00 sec)
mysql> explain analyze select lead(type) over(partition by repo_name, actor_login, language) from github_events;
+------------------------------------+----------+------------+--------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+----------+------------+--------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| TableReader_26 | 10000.00 | 1944093760 | root | partition:all | time:2m10.7s, open:15.1ms, close:9.6µs, loops:1898598, RU:6444988.64, cop_task: {num: 29729, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 3, data:ExchangeSender_25 | 2.19 MB | N/A |
| └─ExchangeSender_25 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:2m10.8s, min:2m10.2s, avg: 2m10.5s, p80:2m10.8s, p95:2m10.8s, iters:29727, tasks:2, threads:128}, tiflash_network: {inner_zone_send_bytes: 31215182258} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_8 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:2m9.4s, min:2m9.1s, avg: 2m9.3s, p80:2m9.4s, p95:2m9.4s, iters:29727, tasks:2, threads:128} | Column#35->Column#36, stream_count: 64 | N/A | N/A |
| └─Window_24 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:2m9.4s, min:2m9.1s, avg: 2m9.2s, p80:2m9.4s, p95:2m9.4s, iters:29727, tasks:2, threads:128} | lead(gharchive_dev.github_events.type)->Column#35 over(partition by gharchive_dev.github_events.repo_name, gharchive_dev.github_events.actor_login, gharchive_dev.github_events.language), stream_count: 64 | N/A | N/A |
| └─Sort_14 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:2m5.1s, min:2m4.5s, avg: 2m4.8s, p80:2m5.1s, p95:2m5.1s, iters:29727, tasks:2, threads:128} | gharchive_dev.github_events.repo_name, gharchive_dev.github_events.actor_login, gharchive_dev.github_events.language, stream_count: 64 | N/A | N/A |
| └─ExchangeReceiver_13 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:42.1s, min:39.9s, avg: 41s, p80:42.1s, p95:42.1s, iters:196499, tasks:2, threads:128}, tiflash_wait: {pipeline_queue_wait: 2038ms}, tiflash_network: {inner_zone_receive_bytes: 33329954821} | stream_count: 64 | N/A | N/A |
| └─ExchangeSender_12 | 10000.00 | 1944093760 | mpp[tiflash] | | tiflash_task:{proc max:52s, min:0s, avg: 26s, p80:52s, p95:52s, iters:31007, tasks:2, threads:144}, tiflash_network: {inner_zone_send_bytes: 33329954821} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: gharchive_dev.github_events.repo_name, collate: utf8mb4_bin], [name: gharchive_dev.github_events.actor_login, collate: utf8mb4_bin], [name: gharchive_dev.github_events.language, collate: utf8mb4_bin], stream_count: 64 | N/A | N/A |
| └─TableFullScan_11 | 10000.00 | 1944093760 | mpp[tiflash] | table:github_events | tiflash_task:{proc max:1.51s, min:0s, avg: 752.8ms, p80:1.51s, p95:1.51s, iters:31007, tasks:2, threads:144}, tiflash_wait: {pipeline_queue_wait: 1132ms}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, local_regions:2205, remote_regions:0, tot_learner_read:78ms, region_balance:{instance_num: 2, max/min: 1105/1100=1.004545}, delta_rows:0, delta_bytes:0, segments:2534, stale_read_regions:0, tot_build_snapshot:20ms, tot_build_bitmap:90ms, tot_build_inputstream:47230ms, min_local_stream:527ms, max_local_stream:1428ms, dtfile:{data_scanned_rows:1944093760, data_skipped_rows:14980833, mvcc_scanned_rows:0, mvcc_skipped_rows:0, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:21167ms, tot_read:516270ms}} | keep order:false, stats:pseudo, PartitionTableScan:true | N/A | N/A |
+------------------------------------+----------+------------+--------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
8 rows in set (2 min 10.87 sec)
Sender:
- stream_1024: 101s
- stream_64: 51s
the profile is as following:
profile_64.zip
profile_1024.zip
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Assessment
This issue has not been assessed yet.