Hi, Please find the attached query profile.
I am running Drill in local mode on my laptop with default memory allocation to Apache Drill. Let me know if you are not able to find the attachment. Also, sending the file in RAR format. Regards, Jasbir Singh -----Original Message----- From: Abhishek Girish [mailto:agir...@apache.org] Sent: Friday, June 02, 2017 11:00 AM To: user@drill.apache.org Subject: [External] Re: UNORDERED_RECEIVER taking 70% of query time Attachment hasn't come through. Can you upload the query profile to some cloud storage and share a link to it? Also, please share details on how large your dataset is, number of Drillbits, memory and other configurations. On Thu, Jun 1, 2017 at 10:18 PM, <jasbir.s...@accenture.com> wrote: > Hi, > > > > I am running a simple query which performs JOIN operation between two > parquet files and it takes around 3-4 secs and I noticed that 70% of > the time is used by UNORDERED_RECEIVER. > > > > Sample query is – > > > > select sum(sales),week from dfs.`C:\parquet-location\ > F8894180-AFFB-4803-B8CF-CCF883AA5AAF-Search_Snapshot_Data.parquet` > where model_component_id in( > > select model_component_id from > dfs.`C:\parquet-location\poc48k.parquet`) > group by week > > > > > > Can we somehow reduce unordered receiver time? > > > > Please find the below screenshot of Visualized plan > > > > > > > > > > > > > > ------------------------------ > > This message is for the designated recipient only and may contain > privileged, proprietary, or otherwise confidential information. If you > have received it in error, please notify the sender immediately and > delete the original. Any other use of the e-mail by you is prohibited. > Where allowed by local law, electronic communications with Accenture > and its affiliates, including e-mail and instant messaging (including > content), may be scanned by our systems for the purposes of > information security and assessment of internal compliance with Accenture > policy. > ____________________________________________________________ > __________________________ > > www.accenture.com > ________________________________ This message is for the designated recipient only and may contain privileged, proprietary, or otherwise confidential information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy. ______________________________________________________________________________________ www.accenture.com
Query Profile STATE: COMPLETED FOREMAN: DDC1-L-HF2ND02.dir.svc.accenture.com TOTAL FRAGMENTS: 9 DURATION: 3.641 sec PLANNING: 0.750 sec QUEUED: Not Available EXECUTION: 2.891 sec Fragment Profiles Overview 1.00 1.50 2.00 2.50 3.00 3.50 Major Fragment Minor Fragments Reporting First Start Last Start First End Last End Min Runtime Avg Runtime Max Runtime % Busy Max Peak Memory 00-xx-xx 1 / 1 0.750s 0.750s 3.640s 3.640s 2.890s 2.890s 2.890s 0.02% 3MB 01-xx-xx 3 / 3 0.750s 0.750s 3.616s 3.616s 2.866s 2.866s 2.866s 0.18% 11MB 02-xx-xx 1 / 1 0.750s 0.750s 3.606s 3.606s 2.856s 2.856s 2.856s 0.11% 2MB 03-xx-xx 3 / 3 0.750s 0.750s 2.662s 3.606s 1.912s 2.538s 2.856s 98.61% 27MB 04-xx-xx 1 / 1 0.750s 0.750s 1.487s 1.487s 0.737s 0.737s 0.737s 0.50% 2MB Major Fragment: 00-xx-xx Minor Fragment ID Host Name Start End Runtime Max Records Max Batches Last Update Last Progress Peak Memory State 00-00-xx DDC1-L-HF2ND02.dir.svc.accenture.com 0.750s 3.640s 2.890s 52 4 13:16:21 13:16:21 3MB FINISHED Major Fragment: 01-xx-xx Minor Fragment ID Host Name Start End Runtime Max Records Max Batches Last Update Last Progress Peak Memory State 01-00-xx DDC1-L-HF2ND02.dir.svc.accenture.com 0.750s 3.616s 2.866s 39 2 13:16:21 13:16:21 11MB FINISHED 01-01-xx DDC1-L-HF2ND02.dir.svc.accenture.com 0.750s 3.616s 2.866s 51 2 13:16:21 13:16:21 11MB FINISHED 01-02-xx DDC1-L-HF2ND02.dir.svc.accenture.com 0.750s 3.616s 2.866s 66 2 13:16:21 13:16:21 11MB FINISHED Major Fragment: 02-xx-xx Minor Fragment ID Host Name Start End Runtime Max Records Max Batches Last Update Last Progress Peak Memory State 02-00-xx DDC1-L-HF2ND02.dir.svc.accenture.com 0.750s 3.606s 2.856s 156 4 13:16:21 13:16:21 2MB FINISHED Major Fragment: 03-xx-xx Minor Fragment ID Host Name Start End Runtime Max Records Max Batches Last Update Last Progress Peak Memory State 03-00-xx DDC1-L-HF2ND02.dir.svc.accenture.com 0.750s 3.596s 2.846s 1,676,730 243 13:16:21 13:16:21 26MB FINISHED 03-01-xx DDC1-L-HF2ND02.dir.svc.accenture.com 0.750s 2.662s 1.912s 916,698 129 13:16:20 13:16:20 25MB FINISHED 03-02-xx DDC1-L-HF2ND02.dir.svc.accenture.com 0.750s 3.606s 2.856s 1,568,154 227 13:16:21 13:16:21 27MB FINISHED Major Fragment: 04-xx-xx Minor Fragment ID Host Name Start End Runtime Max Records Max Batches Last Update Last Progress Peak Memory State 04-00-xx DDC1-L-HF2ND02.dir.svc.accenture.com 0.750s 1.487s 0.737s 48,090 12 13:16:19 13:16:19 2MB FINISHED Operator Profiles Overview Operator ID Type Avg Setup Time Max Setup Time Avg Process Time Max Process Time Min Wait Time Avg Wait Time Max Wait Time % Fragment Time % Query Time Rows Avg Peak Memory Max Peak Memory 00-xx-00 SCREEN 0.000s 0.000s 0.000s 0.000s 0.050s 0.050s 0.050s 42.20% 0.00% 52 5KB 5KB 00-xx-01 PROJECT 0.000s 0.000s 0.000s 0.000s 0.000s 0.000s 0.000s 7.47% 0.00% 52 2KB 2KB 00-xx-02 UNORDERED_RECEIVER 0.000s 0.000s 0.000s 0.000s 2.110s 2.110s 2.110s 50.33% 0.00% 52 - - 01-xx-00 SINGLE_SENDER 0.000s 0.000s 0.000s 0.000s 0.001s 0.001s 0.002s 2.79% 0.01% 52 4MB 4MB 01-xx-01 PROJECT 0.000s 0.000s 0.000s 0.000s 0.000s 0.000s 0.000s 0.63% 0.00% 52 4MB 4MB 01-xx-02 HASH_AGGREGATE 0.016s 0.036s 0.003s 0.004s 0.000s 0.000s 0.000s 89.51% 0.18% 156 6MB 6MB 01-xx-03 PROJECT 0.000s 0.000s 0.000s 0.000s 0.000s 0.000s 0.000s 3.13% 0.01% 156 4KB 4KB 01-xx-04 UNORDERED_RECEIVER 0.000s 0.000s 0.000s 0.000s 2.081s 2.103s 2.116s 3.95% 0.01% 156 - - 02-xx-00 HASH_PARTITION_SENDER 0.000s 0.000s 0.002s 0.002s 0.001s 0.001s 0.001s 85.58% 0.04% 156 110KB 110KB 02-xx-01 UNORDERED_RECEIVER 0.000s 0.000s 0.000s 0.000s 2.124s 2.124s 2.124s 14.42% 0.01% 156 - - 03-xx-00 SINGLE_SENDER 0.000s 0.000s 0.000s 0.000s 0.001s 0.002s 0.003s 0.01% 0.01% 156 4MB 4MB 03-xx-01 PROJECT 0.005s 0.012s 0.000s 0.000s 0.000s 0.000s 0.000s 0.00% 0.00% 156 4MB 4MB 03-xx-02 HASH_AGGREGATE 0.005s 0.006s 0.166s 0.198s 0.000s 0.000s 0.000s 8.84% 8.82% 2,377,492 6MB 6MB 03-xx-03 PROJECT 0.000s 0.000s 0.004s 0.004s 0.000s 0.000s 0.000s 0.19% 0.19% 2,377,492 312KB 312KB 03-xx-04 HASH_JOIN 0.000s 0.000s 0.588s 0.712s 0.000s 0.000s 0.000s 31.34% 31.26% 4,161,582 8MB 9MB 03-xx-05 PROJECT 0.000s 0.000s 0.000s 0.000s 0.000s 0.000s 0.000s 0.00% 0.00% 144,270 256KB 256KB 03-xx-06 PARQUET_ROW_GROUP_SCAN 0.000s 0.000s 1.117s 1.303s 0.003s 0.005s 0.006s 59.55% 59.39% 4,017,312 7MB 7MB 03-xx-07 UNORDERED_RECEIVER 0.000s 0.000s 0.001s 0.002s 0.012s 0.020s 0.025s 0.06% 0.06% 144,270 - - 04-xx-00 BROADCAST_SENDER 0.000s 0.000s 0.001s 0.001s 0.133s 0.133s 0.133s 100.00% 0.01% 48,090 876KB 876KB 00-xx-00 - SCREEN Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 00-00-00 0.000s 0.000s 0.050s 4 52 5KB Operator Metrics 00-xx-01 - PROJECT Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 00-00-01 0.000s 0.000s 0.000s 4 52 2KB Operator Metrics 00-xx-02 - UNORDERED_RECEIVER Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 00-00-02 0.000s 0.000s 2.110s 4 52 - Operator Metrics 01-xx-00 - SINGLE_SENDER Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 01-00-00 0.000s 0.000s 0.001s 2 13 4MB 01-01-00 0.000s 0.000s 0.002s 2 17 4MB 01-02-00 0.000s 0.000s 0.001s 2 22 4MB Operator Metrics 01-xx-01 - PROJECT Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 01-00-01 0.000s 0.000s 0.000s 2 13 4MB 01-01-01 0.000s 0.000s 0.000s 2 17 4MB 01-02-01 0.000s 0.000s 0.000s 2 22 4MB Operator Metrics 01-xx-02 - HASH_AGGREGATE Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 01-00-02 0.007s 0.004s 0.000s 2 39 6MB 01-01-02 0.036s 0.003s 0.000s 2 51 6MB 01-02-02 0.005s 0.003s 0.000s 2 66 6MB Operator Metrics 01-xx-03 - PROJECT Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 01-00-03 0.000s 0.000s 0.000s 2 39 4KB 01-01-03 0.000s 0.000s 0.000s 2 51 4KB 01-02-03 0.000s 0.000s 0.000s 2 66 4KB Operator Metrics 01-xx-04 - UNORDERED_RECEIVER Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 01-00-04 0.000s 0.000s 2.110s 2 39 - 01-01-04 0.000s 0.000s 2.081s 2 51 - 01-02-04 0.000s 0.000s 2.116s 2 66 - Operator Metrics 02-xx-00 - HASH_PARTITION_SENDER Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 02-00-00 0.000s 0.002s 0.001s 4 156 110KB Operator Metrics 02-xx-01 - UNORDERED_RECEIVER Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 02-00-01 0.000s 0.000s 2.124s 4 156 - Operator Metrics 03-xx-00 - SINGLE_SENDER Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 03-00-00 0.000s 0.000s 0.003s 2 52 4MB 03-01-00 0.000s 0.000s 0.001s 2 52 4MB 03-02-00 0.000s 0.000s 0.001s 2 52 4MB Operator Metrics 03-xx-01 - PROJECT Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 03-00-01 0.001s 0.000s 0.000s 2 52 4MB 03-01-01 0.012s 0.000s 0.000s 2 52 4MB 03-02-01 0.001s 0.000s 0.000s 2 52 4MB Operator Metrics 03-xx-02 - HASH_AGGREGATE Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 03-00-02 0.006s 0.198s 0.000s 243 965,276 6MB 03-01-02 0.004s 0.110s 0.000s 129 510,952 6MB 03-02-02 0.005s 0.189s 0.000s 227 901,264 6MB Operator Metrics 03-xx-03 - PROJECT Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 03-00-03 0.000s 0.004s 0.000s 243 965,276 312KB 03-01-03 0.000s 0.002s 0.000s 129 510,952 312KB 03-02-03 0.000s 0.004s 0.000s 227 901,264 312KB Operator Metrics 03-xx-04 - HASH_JOIN Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 03-00-04 0.000s 0.665s 0.000s 53 1,632,735 8MB 03-01-04 0.000s 0.386s 0.000s 29 872,703 7MB 03-02-04 0.000s 0.712s 0.000s 49 1,524,159 9MB Operator Metrics 03-xx-05 - PROJECT Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 03-00-05 0.000s 0.000s 0.000s 12 48,090 256KB 03-01-05 0.000s 0.000s 0.000s 12 48,090 256KB 03-02-05 0.000s 0.000s 0.000s 12 48,090 256KB Operator Metrics 03-xx-06 - PARQUET_ROW_GROUP_SCAN Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 03-00-06 0.000s 1.303s 0.006s 52 1,628,640 7MB 03-01-06 0.000s 0.752s 0.003s 28 868,608 7MB 03-02-06 0.000s 1.295s 0.005s 48 1,520,064 7MB Operator Metrics 03-xx-07 - UNORDERED_RECEIVER Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 03-00-07 0.000s 0.002s 0.025s 12 48,090 - 03-01-07 0.000s 0.001s 0.022s 12 48,090 - 03-02-07 0.000s 0.001s 0.012s 12 48,090 - Operator Metrics 04-xx-00 - BROADCAST_SENDER Minor Fragment Setup Time Process Time Wait Time Max Batches Max Records Peak Memory 04-00-00 0.000s 0.001s 0.133s 12 48,090 876KB