FAQ
Thanks Greg, I'll reorder the select.

Does this explain the low network throughput? I ran a iperf test which showed 915Mib/sec.
________________________________
From: Greg Rahn
Sent: 03/09/2013 22:58
To: impala-user@cloudera.org
Subject: Re: Understanding IMPALA query plans

The first thing I see is that for all of the joins the build side of the
hash join contains many more rows than the probe side. This will result in
suboptimal performance.
Since impala uses a left deep tree, you will want to order your
tables/joins such that the relation that returns the most rows is listed
first, and then list them in order of most selective join to least (we want
to filter out the most rows as early as possible in the query execution
tree). W/o having the SQL and knowing the data, I can not offer a
semantically equivalent SQL.

I've included some "greps" to help you find different metadata about the
query to help point you in the right direction. Some of these have been
massaged, formatted, or trimmed to the relevant metrics.


*# of rows from each table, post table filter (first scan on bottom).*
*
*
$ egrep '(HDFS_SCAN_NODE|RowsReturned)' profile.txt

HDFS_SCAN_NODE (id=13) "g"
   - RowsReturned: 6,185,728
HDFS_SCAN_NODE (id=11) "e - Uppere"
   - RowsReturned: 14,235,321
HDFS_SCAN_NODE (id=9) "f"
   - RowsReturned: 5,048,208
HDFS_SCAN_NODE (id=7) "e - Lowere"
   - RowsReturned: 14,235,321
HDFS_SCAN_NODE (id=5) "d"
   - RowsReturned: 6,663,008
HDFS_SCAN_NODE (id=3) "c"
   - RowsReturned: 6,559,438
HDFS_SCAN_NODE (id=1) "b"
   - RowsReturned: 4,123,819
HDFS_SCAN_NODE (id=0) "a"
   - RowsReturned: 1024

*I've added the relation alias here to help demonstrate the issue. Table
"a" has the least number of rows, yet it's listed first in the join. *


*Types of joins (first join on bottom)*
*
*
$ grep "join op" profile.txt
join op: LEFT OUTER JOIN (BROADCAST)
join op: LEFT OUTER JOIN (BROADCAST)
join op: LEFT OUTER JOIN (BROADCAST)
join op: LEFT OUTER JOIN (BROADCAST)
join op: LEFT OUTER JOIN (BROADCAST)
join op: INNER JOIN (BROADCAST)
join op: INNER JOIN (BROADCAST)
*Note that each join is a broadcast join. This means each node ends up
with a copy of each table.*


*How many rows are on the build side and the probe side for each join
(first join on bottom)?*

$ egrep '(Averaged Fragment|HASH_JOIN_NODE|BuildRows|ProbeRows)'
profile.txt
     Averaged Fragment 1
       HASH_JOIN_NODE (id=14)
         - BuildRows: 24,742,914
         - ProbeRows: 10,240
         HASH_JOIN_NODE (id=12)
           - BuildRows: 56,941,286
           - ProbeRows: 10,240
           HASH_JOIN_NODE (id=10)
             - BuildRows: 30,289,251
             - ProbeRows: 10,240
             HASH_JOIN_NODE (id=8)
               - BuildRows: 56,941,286
               - ProbeRows: 10,240
               HASH_JOIN_NODE (id=6)
                  - BuildRows: 26,652,035
                 - ProbeRows: 10,240
                 HASH_JOIN_NODE (id=4)
                   - BuildRows: 65,594,382
                   - ProbeRows: 10,240
                   HASH_JOIN_NODE (id=2)
                     - BuildRows: 24,742,914
                     - ProbeRows: 1024

*Note that from the first join, the build side is the large side (not
optimal) as the BuildRows > ProbeRows. We want BuildRows < ProbeRows.*


Hope that helps.

On Sat, Aug 31, 2013 at 1:22 AM, Andrew Stevenson wrote:

Here's the profile, I've removed the actual select as it's confidential.

I would be good if there was a troubleshooting guide for analysing the
logs and query plans.

To unsubscribe from this group and stop receiving emails from it, send an
email to impala-user+unsubscribe@cloudera.org.
To unsubscribe from this group and stop receiving emails from it, send an email to impala-user+unsubscribe@cloudera.org.

To unsubscribe from this group and stop receiving emails from it, send an email to impala-user+unsubscribe@cloudera.org.

Search Discussions

  • Greg Rahn at Sep 4, 2013 at 8:09 pm
    Likely.
    It's worth noting that low network throughput may be a result of the
    consumer side of the data flow dynamics (what the receiver does after it
    receives data).

    On Tue, Sep 3, 2013 at 11:39 PM, Andrew Stevenson wrote:

    Thanks Greg, I'll reorder the select.

    Does this explain the low network throughput? I ran a iperf test which
    showed 915Mib/sec.
    ------------------------------
    From: Greg Rahn <grahn@cloudera.com>
    Sent: 03/09/2013 22:58
    To: impala-user@cloudera.org
    Subject: Re: Understanding IMPALA query plans

    The first thing I see is that for all of the joins the build side of the
    hash join contains many more rows than the probe side. This will result in
    suboptimal performance.
    Since impala uses a left deep tree, you will want to order your
    tables/joins such that the relation that returns the most rows is listed
    first, and then list them in order of most selective join to least (we want
    to filter out the most rows as early as possible in the query execution
    tree). W/o having the SQL and knowing the data, I can not offer a
    semantically equivalent SQL.

    I've included some "greps" to help you find different metadata about the
    query to help point you in the right direction. Some of these have been
    massaged, formatted, or trimmed to the relevant metrics.


    *# of rows from each table, post table filter (first scan on bottom).*
    *
    *
    $ egrep '(HDFS_SCAN_NODE|RowsReturned)' profile.txt

    HDFS_SCAN_NODE (id=13) "g"
    - RowsReturned: 6,185,728
    HDFS_SCAN_NODE (id=11) "e - Uppere"
    - RowsReturned: 14,235,321
    HDFS_SCAN_NODE (id=9) "f"
    - RowsReturned: 5,048,208
    HDFS_SCAN_NODE (id=7) "e - Lowere"
    - RowsReturned: 14,235,321
    HDFS_SCAN_NODE (id=5) "d"
    - RowsReturned: 6,663,008
    HDFS_SCAN_NODE (id=3) "c"
    - RowsReturned: 6,559,438
    HDFS_SCAN_NODE (id=1) "b"
    - RowsReturned: 4,123,819
    HDFS_SCAN_NODE (id=0) "a"
    - RowsReturned: 1024

    *I've added the relation alias here to help demonstrate the issue.
    Table "a" has the least number of rows, yet it's listed first in the join.
    *


    *Types of joins (first join on bottom)*
    *
    *
    $ grep "join op" profile.txt
    join op: LEFT OUTER JOIN (BROADCAST)
    join op: LEFT OUTER JOIN (BROADCAST)
    join op: LEFT OUTER JOIN (BROADCAST)
    join op: LEFT OUTER JOIN (BROADCAST)
    join op: LEFT OUTER JOIN (BROADCAST)
    join op: INNER JOIN (BROADCAST)
    join op: INNER JOIN (BROADCAST)
    *Note that each join is a broadcast join. This means each node ends up
    with a copy of each table.*


    *How many rows are on the build side and the probe side for each join
    (first join on bottom)?*

    $ egrep '(Averaged Fragment|HASH_JOIN_NODE|BuildRows|ProbeRows)'
    profile.txt
    Averaged Fragment 1
    HASH_JOIN_NODE (id=14)
    - BuildRows: 24,742,914
    - ProbeRows: 10,240
    HASH_JOIN_NODE (id=12)
    - BuildRows: 56,941,286
    - ProbeRows: 10,240
    HASH_JOIN_NODE (id=10)
    - BuildRows: 30,289,251
    - ProbeRows: 10,240
    HASH_JOIN_NODE (id=8)
    - BuildRows: 56,941,286
    - ProbeRows: 10,240
    HASH_JOIN_NODE (id=6)
    - BuildRows: 26,652,035
    - ProbeRows: 10,240
    HASH_JOIN_NODE (id=4)
    - BuildRows: 65,594,382
    - ProbeRows: 10,240
    HASH_JOIN_NODE (id=2)
    - BuildRows: 24,742,914
    - ProbeRows: 1024

    *Note that from the first join, the build side is the large side (not
    optimal) as the BuildRows > ProbeRows. We want BuildRows < ProbeRows.*


    Hope that helps.


    On Sat, Aug 31, 2013 at 1:22 AM, Andrew Stevenson wrote:

    Here's the profile, I've removed the actual select as it's confidential.

    I would be good if there was a troubleshooting guide for analysing the
    logs and query plans.

    To unsubscribe from this group and stop receiving emails from it, send an
    email to impala-user+unsubscribe@cloudera.org.


    To unsubscribe from this group and stop receiving emails from it, send
    an email to impala-user+unsubscribe@cloudera.org.

    To unsubscribe from this group and stop receiving emails from it, send an
    email to impala-user+unsubscribe@cloudera.org.
    To unsubscribe from this group and stop receiving emails from it, send an email to impala-user+unsubscribe@cloudera.org.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupimpala-user @
categorieshadoop
postedSep 4, '13 at 6:40a
activeSep 4, '13 at 8:09p
posts2
users2
websitecloudera.com
irc#hadoop

2 users in discussion

Greg Rahn: 1 post Andrew Stevenson: 1 post

People

Translate

site design / logo © 2022 Grokbase