none
LLAP Interactive Query Cluster Slow Query Performance RRS feed

  • Question

  • Hi,

    We have HDInsight with interactive query running on our environment.
    The users reported that their queries running slow.

    We have tried to increase # of nodes but it's not improving.
    Even when there is only 1 query running at time, it slow.

    When I checked the Tez Timeline view from Ambari,

    I can see much time spend on "Submit Dag".
    15s for submit Dag out of 22s total time.

    Is there something we can do about it?

    cheers,
    Freddy Setiawan

    Wednesday, August 14, 2019 2:21 AM

Answers

  • Hi All,

    Microsoft support managed to fixed the issue.
    It was something to do with DNS resolve which keep failing and retrying.
    after adding entry on /etc/resolv.conf the submit time now reduced to 2 secs.

    Case Close

    Tuesday, September 17, 2019 8:00 AM

All replies

  • Hi Freddy,

    This article describes troubleshooting steps and possible resolutions for issues when using Interactive Query components in Azure HDInsight clusters.

    Hope this helps. Do let us know if you any further queries.

    Wednesday, August 14, 2019 6:30 AM
    Moderator
  • Thanks for the article.

    I have tried the combinations and sharing the result below.
    Seems settings "set hive.fetch.task.conversion=none;" works better for us.
    although more testing with more various queries.

    but still I have questions, the Submit plan is still very high.

    DEFAULT 
    INFO  : Status: DAG finished successfully in 3.41 seconds
    INFO  :
    INFO  : Query Execution Summary
    INFO  : ----------------------------------------------------------------------------------------------
    INFO  : OPERATION                            DURATION
    INFO  : ----------------------------------------------------------------------------------------------
    INFO  : Compile Query                           1.74s
    INFO  : Prepare Plan                            0.70s
    INFO  : Submit Plan                            15.23s
    INFO  : Start DAG                               0.21s
    INFO  : Run DAG                                 3.41s
    INFO  : ----------------------------------------------------------------------------------------------
    *** Time taken: 19.8 seconds



    set hive.fetch.task.conversion=none;
    INFO  : Status: DAG finished successfully in 3.62 seconds
    INFO  :
    INFO  : Query Execution Summary
    INFO  : ----------------------------------------------------------------------------------------------
    INFO  : OPERATION                            DURATION
    INFO  : ----------------------------------------------------------------------------------------------
    INFO  : Compile Query                           1.89s
    INFO  : Prepare Plan                            0.62s
    INFO  : Submit Plan                            10.23s
    INFO  : Start DAG                               0.22s
    INFO  : Run DAG                                 3.62s
    INFO  : ----------------------------------------------------------------------------------------------
    *** Time taken: 14.941 seconds



    set hive.llap.io.enabled=false; 
    set hive.optimize.index.filter=false; 
    set hive.exec.orc.split.strategy=BI; 
    set hive.fetch.task.conversion=none;
    INFO  : Status: DAG finished successfully in 24.67 seconds
    INFO  :
    INFO  : Query Execution Summary
    INFO  : ----------------------------------------------------------------------------------------------
    INFO  : OPERATION                            DURATION
    INFO  : ----------------------------------------------------------------------------------------------
    INFO  : Compile Query                           2.08s
    INFO  : Prepare Plan                            0.58s
    INFO  : Submit Plan                            10.25s
    INFO  : Start DAG                               0.21s
    INFO  : Run DAG                                24.67s
    INFO  : ----------------------------------------------------------------------------------------------
    ***  Time taken: 35.87 seconds


    Wednesday, August 14, 2019 7:13 AM
  • Hi Freddy,

    Could you please verify the results with increasing the number of worker nodes?

    Tuesday, August 20, 2019 6:42 AM
    Moderator
  • Hi,

    I tried 1,2,3 workers, the results about the same.

    the "Submit Plan" is very high.


    Thursday, August 22, 2019 4:37 AM
  • Hi Freddy,

    Note: Submit plan time depends on the query.

    To investigate further, query time investigation would normally need more information like (Dataset, Actual query, etc) which cannot be shared on the public forum and which requires a support case.

    Here are some general guidelines for query time investigation:

    1. Check how the query works in Hive and how long does it take to compile in Hive. Is it equally long?
    2. Does the query have too many AND/OR types statements? These can increase the compilation time.
    3. Look for partitioning and skew issues in the underlying dataset.
    Thursday, August 29, 2019 5:06 AM
    Moderator
  • Hi Freddy,

    Just checking in to see if the above answer helped. If this answers your query, do click “Mark as Answer” and Up-Vote for the same. And, if you have any further query do let us know.

    Thursday, September 5, 2019 6:54 AM
    Moderator
  • Hi All,

    Microsoft support managed to fixed the issue.
    It was something to do with DNS resolve which keep failing and retrying.
    after adding entry on /etc/resolv.conf the submit time now reduced to 2 secs.

    Case Close

    Tuesday, September 17, 2019 8:00 AM
  • Hi Freddy,

    Glad to know that your issue has resolved. And thanks for sharing the solution, which might be beneficial to other community members reading this thread. 

    Tuesday, September 17, 2019 8:05 AM
    Moderator