FAQ
hi: All
I execute following query against a impala table. At the beginning, there
is much disk I/O activity taking place. But after a while, i/o storm grind
to a halt, however, the query didn't finish yet. this situation sustain for
about half the query time. So my question is what does impala do in this
long time period, if it didn't reading data?

>

SELECT

a.cdr_type,

a.bearer_type,

a.product_no,

sum(a.duration)

FROM factable a

LEFT OUTER JOIN

(

SELECT substr(x.sh0123,1,7) as sh0123 ,MAX(x.city_id) as city_id

FROM DIM_PUB_H0123 x

WHERE city_id in (240,410,411,412,413,414,415,416,417,418,419,421,427,429)

GROUP BY substr(x.sh0123,1,7)

)b

on substr(a.product_no,3,7)=b.sh0123

GROUP BY

a.cdr_type,

a.bearer_type,

a.product_no

LIMIT 100

;

      note: the joined table DIM_PUB_H0123 is very small, ~20M in size;
factable is ~200G in size, compressed in snappy.

I use hive to execute the same query against same data set, the execution
time is even a little less than that of impala.


--
Anty Rao

Search Discussions

  • Anty Rao at Jun 22, 2013 at 3:32 am
    Hi Skye
    this is profile for this query

    Query (id=810c777b56854165:8d05f16c9a909f7f): Summary: Start Time:
    2013-06-20 13:27:21 End Time: 2013-06-20 14:06:12 Query Type: QUERY Query
    State: FINISHED Impala Version: impalad version 1.0 RELEASE (build
    92f0ec5d133396f5b486e095891051f4d63c4e96) Built on Tue, 18 Jun 2013
    09:19:29 CST User: anty Default Db: default Sql Statement: select
    a.cdr_type, a.bearer_type, a.product_no, sum(a.duration) FROM HUGETABLE a
    LEFT OUTER JOIN ( SELECT substr(x.sh0123,1,7) as sh0123 ,MAX(x.city_id) as
    city_id FROM dim_pub_h0123 x WHERE city_id in
    (240,410,411,412,413,414,415,416,417,418,419,421,427,429) GROUP BY
    substr(x.sh0123,1,7) )b on substr(a.product_no,3,7)=b.sh0123 GROUP BY
    a.cdr_type, a.bearer_type, a.product_no LIMIT 100 Plan: ----------------
    PLAN FRAGMENT 0 PARTITION: UNPARTITIONED 10:EXCHANGE limit: 100 tuple ids:
    4 PLAN FRAGMENT 1 PARTITION: HASH_PARTITIONED: <slot 10>, <slot 11>, <slot
    12> STREAM DATA SINK EXCHANGE ID: 10 UNPARTITIONED 9:AGGREGATE | output:
    SUM(<slot 13>) | group by: <slot 10>, <slot 11>, <slot 12> | limit: 100 |
    tuple ids: 4 | 8:EXCHANGE tuple ids: 4 PLAN FRAGMENT 2 PARTITION: RANDOM
    STREAM DATA SINK EXCHANGE ID: 8 HASH_PARTITIONED: <slot 10>, <slot 11>,
    <slot 12> 4:AGGREGATE | output: SUM(a.duration) | group by: a.cdr_type,
    a.bearer_type, a.product_no | tuple ids: 4 | 3:HASH JOIN | join op: LEFT
    OUTER JOIN (BROADCAST) | hash predicates: | substr(a.product_no, 3, 7) =
    <slot 2> | tuple ids: 0 2N | |----7:EXCHANGE | tuple ids: 2 | 0:SCAN HDFS
    table=default.hugetable #partitions=1 size=197.58GB tuple ids: 0 PLAN
    FRAGMENT 3 PARTITION: HASH_PARTITIONED: <slot 2> STREAM DATA SINK EXCHANGE
    ID: 7 UNPARTITIONED 6:AGGREGATE | output: MAX(<slot 3>) | group by: <slot
    2> | tuple ids: 2 | 5:EXCHANGE tuple ids: 2 PLAN FRAGMENT 4 PARTITION:
    RANDOM STREAM DATA SINK EXCHANGE ID: 5 HASH_PARTITIONED: <slot 2>
    2:AGGREGATE | output: MAX(x.city_id) | group by: substr(x.sh0123, 1, 7) |
    tuple ids: 2 | 1:SCAN HDFS table=default.dim_pub_h0123 #partitions=1
    size=19.15MB predicates: city_id IN (240, 410, 411, 412, 413, 414, 415,
    416, 417, 418, 419, 421, 427, 429) tuple ids: 1 ---------------- Query
    Timeline: 38m51s - Start execution: 3.722us (3.722us) - Planning finished:
    1s467ms (1s467ms) - Rows available: 3s931ms (2s464ms) - First row fetched:
    3s934ms (2.687ms) - Unregister query: 38m51s (38m47s) ImpalaServer: -
    RowMaterializationTimer: 679.120us Execution Profile
    810c777b56854165:8d05f16c9a909f7f:(Active: 38m49s, % non-child: 0.00%) -
    FinalizationTimer: 0ns Coordinator Fragment:(Active: 38m46s, % non-child:
    0.00%) - AverageThreadTokens: 1.00 - RowsProduced: 100 CodeGen:(Active:
    82.408ms, % non-child: 0.00%) - CodegenTime: 0ns - CompileTime: 73.879ms -
    LoadTime: 8.527ms - ModuleFileSize: 65.23 KB EXCHANGE_NODE (id=10):(Active:
    38m46s, % non-child: 100.00%) - BytesReceived: 2.43 KB -
    ConvertRowBatchTime: 3.741us - DataArrivalWaitTime: 38m46s -
    DeserializeRowBatchTimer: 24.668us - FirstBatchArrivalWaitTime: 0ns -
    MemoryUsed: 0.00 - RowsReturned: 100 - RowsReturnedRate: 0 -
    SendersBlockedTimer: 0ns - SendersBlockedTotalTimer(*): 0ns Averaged
    Fragment 4:(Active: 509.388ms, % non-child: 0.00%) split sizes: min: 19.15
    MB, max: 19.15 MB, avg: 19.15 MB, stddev: 0.00 completion times:
    min:511.242ms max:511.242ms mean: 511.242ms stddev:0ns execution rates:
    min:37.46 MB/sec max:37.46 MB/sec mean:37.46 MB/sec stddev:0.00 /sec num
    instances: 1 - AverageThreadTokens: 1.50 - RowsProduced: 9.71K (9713)
    CodeGen:(Active: 306.115ms, % non-child: 60.09%) - CodegenTime: 8.821ms -
    CompileTime: 281.150ms - LoadTime: 24.963ms - ModuleFileSize: 65.23 KB
    DataStreamSender (dst_id=5):(Active: 4.496ms, % non-child: 0.88%) -
    BytesSent: 157.99 KB - NetworkThroughput(*): 44.46 MB/sec -
    OverallThroughput: 34.31 MB/sec - SerializeBatchTime: 3.425ms -
    ThriftTransmitTime(*): 3.470ms - UncompressedRowBatchSize: 332.03 KB
    AGGREGATION_NODE (id=2):(Active: 508.683ms, % non-child: 11.22%) -
    BuildBuckets: 8.19K (8192) - BuildTime: 29.794ms - GetResultsTime:
    639.567us - LoadFactor: 0.69 - MemoryUsed: 640.91 KB - RowsReturned: 9.71K
    (9713) - RowsReturnedRate: 19.09 K/sec HDFS_SCAN_NODE (id=1):(Active:
    451.525ms, % non-child: 88.64%) - AverageHdfsReadThreadConcurrency: 1.00 -
    HdfsReadThreadConcurrencyCountPercentage=0: 0.00 -
    HdfsReadThreadConcurrencyCountPercentage=1: 100.00 -
    HdfsReadThreadConcurrencyCountPercentage=2: 0.00 -
    HdfsReadThreadConcurrencyCountPercentage=3: 0.00 -
    HdfsReadThreadConcurrencyCountPercentage=4: 0.00 -
    AverageIoMgrQueueCapcity: 8.00 - AverageIoMgrQueueSize: 0.00 -
    AverageScannerThreadConcurrency: 1.00 - BytesRead: 19.15 MB - MemoryUsed:
    40.00 B - NumDisksAccessed: 1 - PerReadThreadRawHdfsThroughput: 65.53
    MB/sec - RowsRead: 278.79K (278790) - RowsReturned: 9.71K (9713) -
    RowsReturnedRate: 21.51 K/sec - ScanRangesComplete: 1 -
    ScannerThreadsInvoluntaryContextSwitches: 1.52K (1522) -
    ScannerThreadsTotalWallClockTime: 385.841ms - DelimiterParseTime: 276.955ms
    - MaterializeTupleTime(*): 35.787ms - ScannerThreadsSysTime: 3.999ms -
    ScannerThreadsUserTime: 302.953ms - ScannerThreadsVoluntaryContextSwitches:
    5 - TotalRawHdfsReadTime(*): 292.266ms - TotalReadThroughput: 8.00 MB/sec
    Averaged Fragment 3:(Active: 881.438ms, % non-child: 0.00%) split sizes:
    min: 0.00 , max: 0.00 , avg: 0.00 , stddev: 0.00 completion times:
    min:884.0ms max:884.0ms mean: 884.0ms stddev:0ns execution rates: min:0.00
    /sec max:0.00 /sec mean:0.00 /sec stddev:0.00 /sec num instances: 1 -
    AverageThreadTokens: 1.00 - RowsProduced: 9.71K (9713) CodeGen:(Active:
    170.246ms, % non-child: 19.31%) - CodegenTime: 3.638ms - CompileTime:
    161.679ms - LoadTime: 8.564ms - ModuleFileSize: 65.23 KB DataStreamSender
    (dst_id=7):(Active: 11.158ms, % non-child: 1.27%) - BytesSent: 157.87 KB -
    NetworkThroughput(*): 4.99 MB/sec - OverallThroughput: 13.82 MB/sec -
    SerializeBatchTime: 4.571ms - ThriftTransmitTime(*): 30.879ms -
    UncompressedRowBatchSize: 332.03 KB AGGREGATION_NODE (id=6):(Active:
    873.934ms, % non-child: 5.15%) - BuildBuckets: 8.19K (8192) - BuildTime:
    19.721ms - GetResultsTime: 819.847us - LoadFactor: 0.69 - MemoryUsed:
    640.91 KB - RowsReturned: 9.71K (9713) - RowsReturnedRate: 11.11 K/sec
    EXCHANGE_NODE (id=5):(Active: 828.550ms, % non-child: 94.00%) -
    BytesReceived: 157.99 KB - ConvertRowBatchTime: 243.308us -
    DataArrivalWaitTime: 801.608ms - DeserializeRowBatchTimer: 1.143ms -
    FirstBatchArrivalWaitTime: 0ns - MemoryUsed: 0.00 - RowsReturned: 9.71K
    (9713) - RowsReturnedRate: 11.72 K/sec - SendersBlockedTimer: 0ns -
    SendersBlockedTotalTimer(*): 0ns Averaged Fragment 2:(Active: 28m7s, %
    non-child: 0.00%) split sizes: min: 49.17 GB, max: 49.50 GB, avg: 49.40 GB,
    stddev: 133.50 MB completion times: min:9m14s max:35m7s mean: 28m7s
    stddev:10m55s execution rates: min:24.02 MB/sec max:91.36 MB/sec mean:41.21
    MB/sec stddev:28.96 MB/sec num instances: 4 - AverageThreadTokens: 3.00 -
    RowsProduced: 4.23M (4229769) CodeGen:(Active: 406.255ms, % non-child:
    0.07%) - CodegenTime: 11.908ms - CompileTime: 397.666ms - LoadTime: 8.588ms
    - ModuleFileSize: 65.23 KB DataStreamSender (dst_id=8):(Active: 18m30s, %
    non-child: 10.89%) - BytesSent: 98.49 MB - NetworkThroughput(*): 345.68
    KB/sec - OverallThroughput: 430.63 KB/sec - SerializeBatchTime: 2s013ms -
    ThriftTransmitTime(*): 19m5s - UncompressedRowBatchSize: 262.25 MB
    AGGREGATION_NODE (id=4):(Active: 9m36s, % non-child: 23.30%) -
    BuildBuckets: 4.19M (4194304) - BuildTime: 2m34s - GetResultsTime: 1s023ms
    - LoadFactor: 0.63 - MemoryUsed: 407.07 MB - RowsReturned: 4.23M (4229769)
    - RowsReturnedRate: 7.40 K/sec HASH_JOIN_NODE (id=3):(Active: 7m1s, %
    non-child: 23.73%) - BuildBuckets: 8.19K (8192) - BuildRows: 9.71K (9713) -
    BuildTime: 1.696ms - LoadFactor: 0.71 - MemoryUsed: 0.00 - ProbeRows:
    182.15M (182147326) - ProbeTime: 2m43s - RowsReturned: 182.15M (182147326)
    - RowsReturnedRate: 440.46 K/sec EXCHANGE_NODE (id=7):(Active: 1s044ms, %
    non-child: 0.19%) - BytesReceived: 157.87 KB - ConvertRowBatchTime:
    236.276us - DataArrivalWaitTime: 1s043ms - DeserializeRowBatchTimer:
    1.143ms - FirstBatchArrivalWaitTime: 0ns - MemoryUsed: 0.00 - RowsReturned:
    9.71K (9713) - RowsReturnedRate: 9.31 K/sec - SendersBlockedTimer: 0ns -
    SendersBlockedTotalTimer(*): 0ns HDFS_SCAN_NODE (id=0):(Active: 4m15s, %
    non-child: 41.88%) - AverageHdfsReadThreadConcurrency: 3.36 -
    HdfsReadThreadConcurrencyCountPercentage=0: 0.60 -
    HdfsReadThreadConcurrencyCountPercentage=1: 5.95 -
    HdfsReadThreadConcurrencyCountPercentage=2: 17.43 -
    HdfsReadThreadConcurrencyCountPercentage=3: 9.18 -
    HdfsReadThreadConcurrencyCountPercentage=4: 66.84 -
    AverageIoMgrQueueCapcity: 256.00 - AverageIoMgrQueueSize: 4.23 -
    AverageScannerThreadConcurrency: 1.60 - BytesRead: 49.40 GB - MemoryUsed:
    0.00 - NumDisksAccessed: 4 - PerReadThreadRawHdfsThroughput: 26.73 MB/sec -
    RowsRead: 0 - RowsReturned: 182.15M (182147326) - RowsReturnedRate: 756.17
    K/sec - ScanRangesComplete: 107 - ScannerThreadsInvoluntaryContextSwitches:
    170.13K (170128) - ScannerThreadsTotalWallClockTime: 44m48s -
    MaterializeTupleTime(*): 0ns - ScannerThreadsSysTime: 21s354ms -
    ScannerThreadsUserTime: 8m3s - ScannerThreadsVoluntaryContextSwitches:
    101.58K (101585) - TotalRawHdfsReadTime(*): 32m35s - TotalReadThroughput:
    88.37 MB/sec Averaged Fragment 1:(Active: 9m42s, % non-child: 0.00%) split
    sizes: min: 0.00 , max: 0.00 , avg: 0.00 , stddev: 0.00 completion times:
    min:38m48s max:38m48s mean: 38m48s stddev:112.997ms execution rates:
    min:0.00 /sec max:0.00 /sec mean:0.00 /sec stddev:0.00 /sec num instances:
    4 - AverageThreadTokens: 1.00 - RowsProduced: 25 CodeGen:(Active:
    629.774ms, % non-child: 0.02%) - CodegenTime: 90.526ms - CompileTime:
    271.280ms - LoadTime: 358.492ms - ModuleFileSize: 65.23 KB DataStreamSender
    (dst_id=10):(Active: 5.820ms, % non-child: 0.00%) - BytesSent: 623.00 B -
    NetworkThroughput(*): 1.90 MB/sec - OverallThroughput: 26.18 KB/sec -
    SerializeBatchTime: 5.793ms - ThriftTransmitTime(*): 78.127us -
    UncompressedRowBatchSize: 1.59 KB AGGREGATION_NODE (id=9):(Active: 9m42s, %
    non-child: 78.72%) - BuildBuckets: 1.02K (1024) - BuildTime: 30m31s -
    GetResultsTime: 3.769us - LoadFactor: 0.25 - MemoryUsed: 172.54 MB -
    RowsReturned: 25 - RowsReturnedRate: 0 EXCHANGE_NODE (id=8):(Active: 8m15s,
    % non-child: 21.28%) - BytesReceived: 98.49 MB - ConvertRowBatchTime:
    113.509ms - DataArrivalWaitTime: 8m15s - DeserializeRowBatchTimer:
    741.964ms - FirstBatchArrivalWaitTime: 0ns - MemoryUsed: 0.00 -
    RowsReturned: 4.21M (4205020) - RowsReturnedRate: 8.49 K/sec -
    SendersBlockedTimer: 6m47s - SendersBlockedTotalTimer(*): 18m46s Fragment
    1: Instance 810c777b56854165:8d05f16c9a909f81
    (host=nd1-rack0-cloud:22000):(Active: 38m48s, % non-child: 0.00%) -
    AverageThreadTokens: 1.00 - RowsProduced: 100 CodeGen:(Active: 493.720ms, %
    non-child: 0.02%) - CodegenTime: 65.753ms - CompileTime: 265.675ms -
    LoadTime: 228.42ms - ModuleFileSize: 65.23 KB DataStreamSender
    (dst_id=10):(Active: 23.238ms, % non-child: 0.00%) - BytesSent: 2.43 KB -
    NetworkThroughput(*): 7.60 MB/sec - OverallThroughput: 104.72 KB/sec -
    SerializeBatchTime: 23.175ms - ThriftTransmitTime(*): 312.510us -
    UncompressedRowBatchSize: 6.35 KB AGGREGATION_NODE (id=9):(Active: 38m48s,
    % non-child: 78.72%) ExecOption: Codegen Enabled - BuildBuckets: 1.02K
    (1024) - BuildTime: 30m32s - GetResultsTime: 15.78us - LoadFactor: 0.25 -
    MemoryUsed: 172.83 MB - RowsReturned: 100 - RowsReturnedRate: 0
    EXCHANGE_NODE (id=8):(Active: 8m15s, % non-child: 21.28%) - BytesReceived:
    98.42 MB - ConvertRowBatchTime: 112.465ms - DataArrivalWaitTime: 8m15s -
    DeserializeRowBatchTimer: 752.610ms - FirstBatchArrivalWaitTime: 0ns -
    MemoryUsed: 0.00 - RowsReturned: 4.23M (4226928) - RowsReturnedRate: 8.53
    K/sec - SendersBlockedTimer: 0ns - SendersBlockedTotalTimer(*): 0ns
    Instance 810c777b56854165:8d05f16c9a909f82 (host=nd4-rack0-cloud:22000): -
    AverageThreadTokens: 1.00 - RowsProduced: 0 CodeGen:(Active: 596.458ms, %
    non-child: 0.00%) - CodegenTime: 100.124ms - CompileTime: 296.232ms -
    LoadTime: 300.224ms - ModuleFileSize: 65.23 KB DataStreamSender
    (dst_id=10):(Active: 13.659us, % non-child: 0.00%) - BytesSent: 0.00 -
    NetworkThroughput(*): 0.00 /sec - OverallThroughput: 0.00 /sec -
    SerializeBatchTime: 0ns - ThriftTransmitTime(*): 0ns -
    UncompressedRowBatchSize: 0.00 AGGREGATION_NODE (id=9):(Active: 106.715ms,
    % non-child: 0.00%) ExecOption: Codegen Enabled - BuildBuckets: 1.02K
    (1024) - BuildTime: 30m31s - GetResultsTime: 0ns - LoadFactor: 0.25 -
    MemoryUsed: 172.61 MB - RowsReturned: 0 - RowsReturnedRate: 0 EXCHANGE_NODE
    (id=8):(Active: 8m15s, % non-child: 0.00%) - BytesReceived: 98.53 MB -
    ConvertRowBatchTime: 115.0ms - DataArrivalWaitTime: 8m15s -
    DeserializeRowBatchTimer: 728.134ms - FirstBatchArrivalWaitTime: 0ns -
    MemoryUsed: 0.00 - RowsReturned: 4.21M (4211712) - RowsReturnedRate: 8.50
    K/sec - SendersBlockedTimer: 12m46s - SendersBlockedTotalTimer(*): 36m33s
    Instance 810c777b56854165:8d05f16c9a909f83 (host=nd2-rack0-cloud:22000): -
    AverageThreadTokens: 1.00 - RowsProduced: 0 CodeGen:(Active: 912.958ms, %
    non-child: 0.00%) - CodegenTime: 99.739ms - CompileTime: 262.657ms -
    LoadTime: 650.299ms - ModuleFileSize: 65.23 KB DataStreamSender
    (dst_id=10):(Active: 15.399us, % non-child: 0.00%) - BytesSent: 0.00 -
    NetworkThroughput(*): 0.00 /sec - OverallThroughput: 0.00 /sec -
    SerializeBatchTime: 0ns - ThriftTransmitTime(*): 0ns -
    UncompressedRowBatchSize: 0.00 AGGREGATION_NODE (id=9):(Active: 105.153ms,
    % non-child: 0.00%) ExecOption: Codegen Enabled - BuildBuckets: 1.02K
    (1024) - BuildTime: 30m32s - GetResultsTime: 0ns - LoadFactor: 0.25 -
    MemoryUsed: 172.19 MB - RowsReturned: 0 - RowsReturnedRate: 0 EXCHANGE_NODE
    (id=8):(Active: 8m14s, % non-child: 0.00%) - BytesReceived: 98.55 MB -
    ConvertRowBatchTime: 113.269ms - DataArrivalWaitTime: 8m14s -
    DeserializeRowBatchTimer: 740.518ms - FirstBatchArrivalWaitTime: 0ns -
    MemoryUsed: 0.00 - RowsReturned: 4.18M (4179968) - RowsReturnedRate: 8.44
    K/sec - SendersBlockedTimer: 13m - SendersBlockedTotalTimer(*): 36m37s
    Instance 810c777b56854165:8d05f16c9a909f84 (host=nd3-rack0-cloud:22000): -
    AverageThreadTokens: 1.00 - RowsProduced: 0 CodeGen:(Active: 515.961ms, %
    non-child: 0.00%) - CodegenTime: 96.489ms - CompileTime: 260.555ms -
    LoadTime: 255.405ms - ModuleFileSize: 65.23 KB DataStreamSender
    (dst_id=10):(Active: 14.611us, % non-child: 0.00%) - BytesSent: 0.00 -
    NetworkThroughput(*): 0.00 /sec - OverallThroughput: 0.00 /sec -
    SerializeBatchTime: 0ns - ThriftTransmitTime(*): 0ns -
    UncompressedRowBatchSize: 0.00 AGGREGATION_NODE (id=9):(Active: 96.517ms, %
    non-child: 0.00%) ExecOption: Codegen Enabled - BuildBuckets: 1.02K (1024)
    - BuildTime: 30m31s - GetResultsTime: 0ns - LoadFactor: 0.25 - MemoryUsed:
    172.52 MB - RowsReturned: 0 - RowsReturnedRate: 0 EXCHANGE_NODE
    (id=8):(Active: 8m15s, % non-child: 0.00%) - BytesReceived: 98.46 MB -
    ConvertRowBatchTime: 113.303ms - DataArrivalWaitTime: 8m15s -
    DeserializeRowBatchTimer: 746.596ms - FirstBatchArrivalWaitTime: 0ns -
    MemoryUsed: 0.00 - RowsReturned: 4.20M (4201472) - RowsReturnedRate: 8.48
    K/sec - SendersBlockedTimer: 1m21s - SendersBlockedTotalTimer(*): 1m54s
    Fragment 2: Instance 810c777b56854165:8d05f16c9a909f86
    (host=nd4-rack0-cloud:22000):(Active: 35m7s, % non-child: 0.00%) Hdfs split
    stats (<volume id>:<# splits>/<split lengths>): 0:35/17.16 GB 1:30/12.34 GB
    2:44/19.94 GB - AverageThreadTokens: 2.59 - RowsProduced: 4.15M (4145932)
    CodeGen:(Active: 403.601ms, % non-child: 0.02%) - CodegenTime: 11.479ms -
    CompileTime: 395.66ms - LoadTime: 8.534ms - ModuleFileSize: 65.23 KB
    DataStreamSender (dst_id=8):(Active: 23m51s, % non-child: 67.91%) -
    BytesSent: 96.55 MB - NetworkThroughput(*): 67.47 KB/sec -
    OverallThroughput: 69.08 KB/sec - SerializeBatchTime: 2s018ms -
    ThriftTransmitTime(*): 24m25s - UncompressedRowBatchSize: 257.05 MB
    AGGREGATION_NODE (id=4):(Active: 11m16s, % non-child: 6.95%) ExecOption:
    Codegen Enabled - BuildBuckets: 4.19M (4194304) - BuildTime: 2m25s -
    GetResultsTime: 999.964ms - LoadFactor: 0.63 - MemoryUsed: 401.95 MB -
    RowsReturned: 4.15M (4145932) - RowsReturnedRate: 6.13 K/sec HASH_JOIN_NODE
    (id=3):(Active: 8m49s, % non-child: 7.68%) ExecOption: Build Side Codegen
    Enabled, Probe Side Codegen Enabled, Hash Table Built Asynchronously -
    BuildBuckets: 8.19K (8192) - BuildRows: 9.71K (9713) - BuildTime: 1.600ms -
    LoadFactor: 0.71 - MemoryUsed: 0.00 - ProbeRows: 176.16M (176157518) -
    ProbeTime: 2m41s - RowsReturned: 176.16M (176157518) - RowsReturnedRate:
    332.50 K/sec EXCHANGE_NODE (id=7):(Active: 1s077ms, % non-child: 0.05%) -
    BytesReceived: 157.87 KB - ConvertRowBatchTime: 236.455us -
    DataArrivalWaitTime: 1s076ms - DeserializeRowBatchTimer: 1.112ms -
    FirstBatchArrivalWaitTime: 0ns - MemoryUsed: 0.00 - RowsReturned: 9.71K
    (9713) - RowsReturnedRate: 9.02 K/sec - SendersBlockedTimer: 0ns -
    SendersBlockedTotalTimer(*): 0ns HDFS_SCAN_NODE (id=0):(Active: 6m6s, %
    non-child: 17.41%) Hdfs split stats (<volume id>:<# splits>/<split
    lengths>): 0:35/17.16 GB 1:30/12.34 GB 2:44/19.94 GB File Formats:
    HFILE/NONE:109 ExecOption: Codegen enabled: 0 out of 48 -
    AverageHdfsReadThreadConcurrency: 3.82 -
    HdfsReadThreadConcurrencyCountPercentage=0: 2.22 -
    HdfsReadThreadConcurrencyCountPercentage=1: 0.00 -
    HdfsReadThreadConcurrencyCountPercentage=2: 2.15 -
    HdfsReadThreadConcurrencyCountPercentage=3: 5.11 -
    HdfsReadThreadConcurrencyCountPercentage=4: 90.52 -
    AverageIoMgrQueueCapcity: 256.00 - AverageIoMgrQueueSize: 10.39 -
    AverageScannerThreadConcurrency: 1.49 - BytesRead: 47.80 GB - MemoryUsed:
    0.00 - NumDisksAccessed: 4 - PerReadThreadRawHdfsThroughput: 19.00 MB/sec -
    RowsRead: 0 - RowsReturned: 176.16M (176157518) - RowsReturnedRate: 480.15
    K/sec - ScanRangesComplete: 109 - ScannerThreadsInvoluntaryContextSwitches:
    161.06K (161057) - ScannerThreadsTotalWallClockTime: 54m52s -
    MaterializeTupleTime(*): 0ns - ScannerThreadsSysTime: 20s205ms -
    ScannerThreadsUserTime: 7m44s - ScannerThreadsVoluntaryContextSwitches:
    102.75K (102749) - TotalRawHdfsReadTime(*): 42m56s - TotalReadThroughput:
    72.45 MB/sec Instance 810c777b56854165:8d05f16c9a909f88
    (host=nd3-rack0-cloud:22000):(Active: 34m37s, % non-child: 0.00%) Hdfs
    split stats (<volume id>:<# splits>/<split lengths>): 0:30/14.35 GB
    1:36/15.98 GB 2:40/18.84 GB - AverageThreadTokens: 2.32 - RowsProduced:
    4.53M (4531090) CodeGen:(Active: 401.695ms, % non-child: 0.02%) -
    CodegenTime: 12.489ms - CompileTime: 393.303ms - LoadTime: 8.390ms -
    ModuleFileSize: 65.23 KB DataStreamSender (dst_id=8):(Active: 25m3s, %
    non-child: 72.36%) - BytesSent: 105.52 MB - NetworkThroughput(*): 70.22
    KB/sec - OverallThroughput: 71.88 KB/sec - SerializeBatchTime: 2s199ms -
    ThriftTransmitTime(*): 25m38s - UncompressedRowBatchSize: 280.93 MB
    AGGREGATION_NODE (id=4):(Active: 9m34s, % non-child: 8.61%) ExecOption:
    Codegen Enabled - BuildBuckets: 4.19M (4194304) - BuildTime: 2m57s -
    GetResultsTime: 1s108ms - LoadFactor: 0.66 - MemoryUsed: 425.46 MB -
    RowsReturned: 4.53M (4531090) - RowsReturnedRate: 7.89 K/sec HASH_JOIN_NODE
    (id=3):(Active: 6m35s, % non-child: 8.99%) ExecOption: Build Side Codegen
    Enabled, Probe Side Codegen Enabled, Hash Table Built Asynchronously -
    BuildBuckets: 8.19K (8192) - BuildRows: 9.71K (9713) - BuildTime: 1.872ms -
    LoadFactor: 0.71 - MemoryUsed: 0.00 - ProbeRows: 203.91M (203906980) -
    ProbeTime: 3m6s - RowsReturned: 203.91M (203906980) - RowsReturnedRate:
    515.71 K/sec EXCHANGE_NODE (id=7):(Active: 1s068ms, % non-child: 0.05%) -
    BytesReceived: 157.87 KB - ConvertRowBatchTime: 242.450us -
    DataArrivalWaitTime: 1s067ms - DeserializeRowBatchTimer: 1.218ms -
    FirstBatchArrivalWaitTime: 0ns - MemoryUsed: 0.00 - RowsReturned: 9.71K
    (9713) - RowsReturnedRate: 9.09 K/sec - SendersBlockedTimer: 0ns -
    SendersBlockedTotalTimer(*): 0ns HDFS_SCAN_NODE (id=0):(Active: 3m27s, %
    non-child: 9.99%) Hdfs split stats (<volume id>:<# splits>/<split
    lengths>): 0:30/14.35 GB 1:36/15.98 GB 2:40/18.84 GB File Formats:
    HFILE/NONE:106 ExecOption: Codegen enabled: 0 out of 56 -
    AverageHdfsReadThreadConcurrency: 3.40 -
    HdfsReadThreadConcurrencyCountPercentage=0: 0.17 -
    HdfsReadThreadConcurrencyCountPercentage=1: 5.93 -
    HdfsReadThreadConcurrencyCountPercentage=2: 15.10 -
    HdfsReadThreadConcurrencyCountPercentage=3: 11.52 -
    HdfsReadThreadConcurrencyCountPercentage=4: 67.28 -
    AverageIoMgrQueueCapcity: 256.00 - AverageIoMgrQueueSize: 0.81 -
    AverageScannerThreadConcurrency: 1.88 - BytesRead: 55.30 GB - MemoryUsed:
    0.00 - NumDisksAccessed: 4 - PerReadThreadRawHdfsThroughput: 29.06 MB/sec -
    RowsRead: 0 - RowsReturned: 203.91M (203906980) - RowsReturnedRate: 982.07
    K/sec - ScanRangesComplete: 106 - ScannerThreadsInvoluntaryContextSwitches:
    172.53K (172531) - ScannerThreadsTotalWallClockTime: 45m36s -
    MaterializeTupleTime(*): 0ns - ScannerThreadsSysTime: 24s903ms -
    ScannerThreadsUserTime: 9m4s - ScannerThreadsVoluntaryContextSwitches:
    124.65K (124653) - TotalRawHdfsReadTime(*): 32m28s - TotalReadThroughput:
    98.73 MB/sec Instance 810c777b56854165:8d05f16c9a909f87
    (host=nd2-rack0-cloud:22000):(Active: 33m29s, % non-child: 0.00%) Hdfs
    split stats (<volume id>:<# splits>/<split lengths>): 0:36/17.22 GB
    1:35/16.43 GB 2:37/15.84 GB - AverageThreadTokens: 2.31 - RowsProduced:
    4.42M (4419857) CodeGen:(Active: 404.181ms, % non-child: 0.02%) -
    CodegenTime: 11.695ms - CompileTime: 395.366ms - LoadTime: 8.813ms -
    ModuleFileSize: 65.23 KB DataStreamSender (dst_id=8):(Active: 24m7s, %
    non-child: 72.02%) - BytesSent: 102.89 MB - NetworkThroughput(*): 70.27
    KB/sec - OverallThroughput: 72.80 KB/sec - SerializeBatchTime: 2s124ms -
    ThriftTransmitTime(*): 24m59s - UncompressedRowBatchSize: 274.03 MB
    AGGREGATION_NODE (id=4):(Active: 9m22s, % non-child: 8.35%) ExecOption:
    Codegen Enabled - BuildBuckets: 4.19M (4194304) - BuildTime: 2m46s -
    GetResultsTime: 1s077ms - LoadFactor: 0.65 - MemoryUsed: 418.67 MB -
    RowsReturned: 4.42M (4419857) - RowsReturnedRate: 7.86 K/sec HASH_JOIN_NODE
    (id=3):(Active: 6m34s, % non-child: 8.78%) ExecOption: Build Side Codegen
    Enabled, Probe Side Codegen Enabled, Hash Table Built Asynchronously -
    BuildBuckets: 8.19K (8192) - BuildRows: 9.71K (9713) - BuildTime: 1.582ms -
    LoadFactor: 0.71 - MemoryUsed: 0.00 - ProbeRows: 202.70M (202695410) -
    ProbeTime: 2m56s - RowsReturned: 202.70M (202695410) - RowsReturnedRate:
    513.93 K/sec EXCHANGE_NODE (id=7):(Active: 995.660ms, % non-child: 0.05%) -
    BytesReceived: 157.87 KB - ConvertRowBatchTime: 234.152us -
    DataArrivalWaitTime: 995.226ms - DeserializeRowBatchTimer: 1.127ms -
    FirstBatchArrivalWaitTime: 0ns - MemoryUsed: 0.00 - RowsReturned: 9.71K
    (9713) - RowsReturnedRate: 9.76 K/sec - SendersBlockedTimer: 0ns -
    SendersBlockedTotalTimer(*): 0ns HDFS_SCAN_NODE (id=0):(Active: 3m36s, %
    non-child: 10.79%) Hdfs split stats (<volume id>:<# splits>/<split
    lengths>): 0:36/17.22 GB 1:35/16.43 GB 2:37/15.84 GB File Formats:
    HFILE/NONE:108 ExecOption: Codegen enabled: 0 out of 56 -
    AverageHdfsReadThreadConcurrency: 3.46 -
    HdfsReadThreadConcurrencyCountPercentage=0: 0.00 -
    HdfsReadThreadConcurrencyCountPercentage=1: 3.57 -
    HdfsReadThreadConcurrencyCountPercentage=2: 15.61 -
    HdfsReadThreadConcurrencyCountPercentage=3: 11.78 -
    HdfsReadThreadConcurrencyCountPercentage=4: 69.05 -
    AverageIoMgrQueueCapcity: 256.00 - AverageIoMgrQueueSize: 1.61 -
    AverageScannerThreadConcurrency: 1.43 - BytesRead: 54.79 GB - MemoryUsed:
    0.00 - NumDisksAccessed: 4 - PerReadThreadRawHdfsThroughput: 28.90 MB/sec -
    RowsRead: 0 - RowsReturned: 202.70M (202695410) - RowsReturnedRate: 934.49
    K/sec - ScanRangesComplete: 108 - ScannerThreadsInvoluntaryContextSwitches:
    161.55K (161546) - ScannerThreadsTotalWallClockTime: 43m47s -
    MaterializeTupleTime(*): 0ns - ScannerThreadsSysTime: 22s175ms -
    ScannerThreadsUserTime: 8m55s - ScannerThreadsVoluntaryContextSwitches:
    87.97K (87970) - TotalRawHdfsReadTime(*): 32m21s - TotalReadThroughput:
    99.98 MB/sec Instance 810c777b56854165:8d05f16c9a909f85
    (host=nd1-rack0-cloud:22000):(Active: 9m14s, % non-child: 0.00%) Hdfs split
    stats (<volume id>:<# splits>/<split lengths>): 0:32/13.80 GB 1:38/18.04 GB
    2:36/17.64 GB - AverageThreadTokens: 4.79 - RowsProduced: 3.82M (3822199)
    CodeGen:(Active: 415.545ms, % non-child: 0.07%) - CodegenTime: 11.969ms -
    CompileTime: 406.929ms - LoadTime: 8.614ms - ModuleFileSize: 65.23 KB
    DataStreamSender (dst_id=8):(Active: 1m, % non-child: 10.89%) - BytesSent:
    89.00 MB - NetworkThroughput(*): 1.15 MB/sec - OverallThroughput: 1.47
    MB/sec - SerializeBatchTime: 1s713ms - ThriftTransmitTime(*): 1m17s -
    UncompressedRowBatchSize: 236.98 MB AGGREGATION_NODE (id=4):(Active: 8m14s,
    % non-child: 23.30%) ExecOption: Codegen Enabled - BuildBuckets: 4.19M
    (4194304) - BuildTime: 2m8s - GetResultsTime: 907.462ms - LoadFactor: 0.60
    - MemoryUsed: 382.19 MB - RowsReturned: 3.82M (3822199) - RowsReturnedRate:
    7.74 K/sec HASH_JOIN_NODE (id=3):(Active: 6m4s, % non-child: 23.73%)
    ExecOption: Build Side Codegen Enabled, Probe Side Codegen Enabled, Hash
    Table Built Asynchronously - BuildBuckets: 8.19K (8192) - BuildRows: 9.71K
    (9713) - BuildTime: 1.731ms - LoadFactor: 0.71 - MemoryUsed: 0.00 -
    ProbeRows: 145.83M (145829396) - ProbeTime: 2m11s - RowsReturned: 145.83M
    (145829396) - RowsReturnedRate: 399.69 K/sec EXCHANGE_NODE (id=7):(Active:
    1s034ms, % non-child: 0.19%) - BytesReceived: 157.87 KB -
    ConvertRowBatchTime: 232.49us - DataArrivalWaitTime: 1s034ms -
    DeserializeRowBatchTimer: 1.117ms - FirstBatchArrivalWaitTime: 0ns -
    MemoryUsed: 0.00 - RowsReturned: 9.71K (9713) - RowsReturnedRate: 9.38
    K/sec - SendersBlockedTimer: 0ns - SendersBlockedTotalTimer(*): 0ns
    HDFS_SCAN_NODE (id=0):(Active: 3m52s, % non-child: 41.88%) Hdfs split stats
    (<volume id>:<# splits>/<split lengths>): 0:32/13.80 GB 1:38/18.04 GB
    2:36/17.64 GB File Formats: HFILE/NONE:106 ExecOption: Codegen enabled: 0
    out of 40 - AverageHdfsReadThreadConcurrency: 2.75 -
    HdfsReadThreadConcurrencyCountPercentage=0: 0.00 -
    HdfsReadThreadConcurrencyCountPercentage=1: 14.31 -
    HdfsReadThreadConcurrencyCountPercentage=2: 36.85 -
    HdfsReadThreadConcurrencyCountPercentage=3: 8.32 -
    HdfsReadThreadConcurrencyCountPercentage=4: 40.51 -
    AverageIoMgrQueueCapcity: 256.00 - AverageIoMgrQueueSize: 4.10 -
    AverageScannerThreadConcurrency: 1.60 - BytesRead: 39.70 GB - MemoryUsed:
    0.00 - NumDisksAccessed: 4 - PerReadThreadRawHdfsThroughput: 29.98 MB/sec -
    RowsRead: 0 - RowsReturned: 145.83M (145829396) - RowsReturnedRate: 627.98
    K/sec - ScanRangesComplete: 106 - ScannerThreadsInvoluntaryContextSwitches:
    185.38K (185378) - ScannerThreadsTotalWallClockTime: 34m58s -
    MaterializeTupleTime(*): 0ns - ScannerThreadsSysTime: 18s135ms -
    ScannerThreadsUserTime: 6m29s - ScannerThreadsVoluntaryContextSwitches:
    90.97K (90971) - TotalRawHdfsReadTime(*): 22m35s - TotalReadThroughput:
    82.32 MB/sec Fragment 3: Instance 810c777b56854165:8d05f16c9a909f89
    (host=nd3-rack0-cloud:22000):(Active: 881.438ms, % non-child: 0.00%) -
    AverageThreadTokens: 1.00 - RowsProduced: 9.71K (9713) CodeGen:(Active:
    170.246ms, % non-child: 19.31%) - CodegenTime: 3.638ms - CompileTime:
    161.679ms - LoadTime: 8.564ms - ModuleFileSize: 65.23 KB DataStreamSender
    (dst_id=7):(Active: 11.158ms, % non-child: 1.27%) - BytesSent: 157.87 KB -
    NetworkThroughput(*): 4.99 MB/sec - OverallThroughput: 13.82 MB/sec -
    SerializeBatchTime: 4.571ms - ThriftTransmitTime(*): 30.879ms -
    UncompressedRowBatchSize: 332.03 KB AGGREGATION_NODE (id=6):(Active:
    873.934ms, % non-child: 5.15%) ExecOption: Codegen Enabled - BuildBuckets:
    8.19K (8192) - BuildTime: 19.721ms - GetResultsTime: 819.847us -
    LoadFactor: 0.69 - MemoryUsed: 640.91 KB - RowsReturned: 9.71K (9713) -
    RowsReturnedRate: 11.11 K/sec EXCHANGE_NODE (id=5):(Active: 828.550ms, %
    non-child: 94.00%) - BytesReceived: 157.99 KB - ConvertRowBatchTime:
    243.308us - DataArrivalWaitTime: 801.608ms - DeserializeRowBatchTimer:
    1.143ms - FirstBatchArrivalWaitTime: 0ns - MemoryUsed: 0.00 - RowsReturned:
    9.71K (9713) - RowsReturnedRate: 11.72 K/sec - SendersBlockedTimer: 0ns -
    SendersBlockedTotalTimer(*): 0ns Fragment 4: Instance
    810c777b56854165:8d05f16c9a909f8a (host=nd3-rack0-cloud:22000):(Active:
    509.388ms, % non-child: 0.00%) Hdfs split stats (<volume id>:<#
    splits>/<split lengths>): 1:1/19.15 MB - AverageThreadTokens: 1.50 -
    RowsProduced: 9.71K (9713) CodeGen:(Active: 306.115ms, % non-child: 60.09%)
    - CodegenTime: 8.821ms - CompileTime: 281.150ms - LoadTime: 24.963ms -
    ModuleFileSize: 65.23 KB DataStreamSender (dst_id=5):(Active: 4.496ms, %
    non-child: 0.88%) - BytesSent: 157.99 KB - NetworkThroughput(*): 44.46
    MB/sec - OverallThroughput: 34.31 MB/sec - SerializeBatchTime: 3.425ms -
    ThriftTransmitTime(*): 3.470ms - UncompressedRowBatchSize: 332.03 KB
    AGGREGATION_NODE (id=2):(Active: 508.683ms, % non-child: 11.22%)
    ExecOption: Codegen Enabled - BuildBuckets: 8.19K (8192) - BuildTime:
    29.794ms - GetResultsTime: 639.567us - LoadFactor: 0.69 - MemoryUsed:
    640.91 KB - RowsReturned: 9.71K (9713) - RowsReturnedRate: 19.09 K/sec
    HDFS_SCAN_NODE (id=1):(Active: 451.525ms, % non-child: 88.64%) Hdfs split
    stats (<volume id>:<# splits>/<split lengths>): 1:1/19.15 MB File Formats:
    TEXT/NONE:1 ExecOption: Codegen enabled: 1 out of 1 -
    AverageHdfsReadThreadConcurrency: 1.00 -
    HdfsReadThreadConcurrencyCountPercentage=0: 0.00 -
    HdfsReadThreadConcurrencyCountPercentage=1: 100.00 -
    HdfsReadThreadConcurrencyCountPercentage=2: 0.00 -
    HdfsReadThreadConcurrencyCountPercentage=3: 0.00 -
    HdfsReadThreadConcurrencyCountPercentage=4: 0.00 -
    AverageIoMgrQueueCapcity: 8.00 - AverageIoMgrQueueSize: 0.00 -
    AverageScannerThreadConcurrency: 1.00 - BytesRead: 19.15 MB - MemoryUsed:
    40.00 B - NumDisksAccessed: 1 - PerReadThreadRawHdfsThroughput: 65.53
    MB/sec - RowsRead: 278.79K (278790) - RowsReturned: 9.71K (9713) -
    RowsReturnedRate: 21.51 K/sec - ScanRangesComplete: 1 -
    ScannerThreadsInvoluntaryContextSwitches: 1.52K (1522) -
    ScannerThreadsTotalWallClockTime: 385.841ms - DelimiterParseTime: 276.955ms
    - MaterializeTupleTime(*): 35.787ms - ScannerThreadsSysTime: 3.999ms -
    ScannerThreadsUserTime: 302.953ms - ScannerThreadsVoluntaryContextSwitches:
    5 - TotalRawHdfsReadTime(*): 292.266ms - TotalReadThroughput: 8.00 MB/sec


    On Sat, Jun 22, 2013 at 2:42 AM, Skye Wanderman-Milne wrote:

    Hi Anty, can you provide the query profile for this query? This will give
    a breakdown of how much time was spent executing each part of the query.



    --
    Anty Rao

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupimpala-user @
categorieshadoop
postedJun 21, '13 at 2:40a
activeJun 22, '13 at 3:32a
posts2
users1
websitecloudera.com
irc#hadoop

1 user in discussion

Anty Rao: 2 posts

People

Translate

site design / logo © 2022 Grokbase