Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-3699

Print plan and query summary when memory limit is exceeded

    XMLWordPrintableJSON

    Details

    • Type: New Feature
    • Status: Resolved
    • Priority: Major
    • Resolution: Won't Fix
    • Affects Version/s: Impala 2.5.0
    • Fix Version/s: None
    • Component/s: Backend
    • Labels:

      Description

      When a query hits memory limit it would be useful to print the summary along with the explain plan to make facilitate root causing the reason for failure.

      The list of memory consumers doesn't contain the operators that are actually consuming memory.

      Query TPC-DS Q67

      : with results as
      (     select i_category ,i_class ,i_brand ,i_product_name ,d_year ,d_qoy ,d_moy ,s_store_id
                        ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
                  from store_sales ,date_dim ,store ,item
             where  ss_sold_date_sk=d_date_sk
                and ss_item_sk=i_item_sk
                and ss_store_sk = s_store_sk
      	and d_month_seq between 1217 and 1217+11
             group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id)
       ,
       results_rollup as
       (select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, s_store_id, sumsales
        from results
        union all
        select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, null s_store_id, sum(sumsales) sumsales
        from results
        group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy
        union all
        select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
        from results
        group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy
        union all
        select i_category, i_class, i_brand, i_product_name, d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
        from results
        group by i_category, i_class, i_brand, i_product_name, d_year
        union all
        select i_category, i_class, i_brand, i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
        from results
        group by i_category, i_class, i_brand, i_product_name
        union all
        select i_category, i_class, i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
        from results
        group by i_category, i_class, i_brand
        union all
        select i_category, i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
        from results
        group by i_category, i_class
        union all
        select i_category, null i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
        from results
        group by i_category
        union all
        select null i_category, null i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
        from results)
      
      select *
      from (select i_category
                  ,i_class
                  ,i_brand
                  ,i_product_name
                  ,d_year
                  ,d_qoy
                  ,d_moy
                  ,s_store_id
                  ,sumsales
                  ,rank() over (partition by i_category order by sumsales desc) rk
            from results_rollup) dw2
      where rk <= 100
      order by i_category
              ,i_class
              ,i_brand
              ,i_product_name
              ,d_year
              ,d_qoy
              ,d_moy
              ,s_store_id
              ,sumsales
              ,rk
      limit 100
      

      Error message

      WARNINGS: 
      Memory limit exceeded
      Failed to pin block for variable-length data needed for sorting. Reducing query concurrency or increasing the memory limit may help this query to complete successfully.
      

      Memory limit information

      Memory Limit Exceeded
      Query(5349fe64e3b9335f:3a9af1592376979e) Limit: Consumption=35.62 GB
        Fragment 5349fe64e3b9335f:3a9af159237697ac: Consumption=48.03 MB
          SORT_NODE (id=84): Consumption=4.00 KB
          SELECT_NODE (id=83): Consumption=0
          ANALYTIC_EVAL_NODE (id=82): Consumption=0
          SORT_NODE (id=81): Consumption=48.02 MB
          EXCHANGE_NODE (id=147): Consumption=0
          DataStreamRecvr: Consumption=0
        Block Manager: Limit=40.00 GB Consumption=35.62 GB
        Fragment 5349fe64e3b9335f:3a9af159237697cc: Consumption=344.00 KB
          UNION_NODE (id=0): Consumption=0
          AGGREGATION_NODE (id=89): Consumption=0
          EXCHANGE_NODE (id=88): Consumption=0
          AGGREGATION_NODE (id=96): Consumption=0
          EXCHANGE_NODE (id=95): Consumption=0
          AGGREGATION_NODE (id=103): Consumption=0
          EXCHANGE_NODE (id=102): Consumption=0
          AGGREGATION_NODE (id=110): Consumption=0
          EXCHANGE_NODE (id=109): Consumption=0
          AGGREGATION_NODE (id=117): Consumption=0
          EXCHANGE_NODE (id=116): Consumption=0
          AGGREGATION_NODE (id=124): Consumption=0
          EXCHANGE_NODE (id=123): Consumption=0
          AGGREGATION_NODE (id=131): Consumption=0
          EXCHANGE_NODE (id=130): Consumption=0
          AGGREGATION_NODE (id=138): Consumption=0
          EXCHANGE_NODE (id=137): Consumption=0
          EXCHANGE_NODE (id=146): Consumption=0
      Memory Limit Exceeded
      Query(5349fe64e3b9335f:3a9af1592376979e) Limit: Consumption=37.72 GB
        Fragment 5349fe64e3b9335f:3a9af159237697bc: Consumption=48.03 MB
          SORT_NODE (id=84): Consumption=4.00 KB
          SELECT_NODE (id=83): Consumption=0
          ANALYTIC_EVAL_NODE (id=82): Consumption=0
          SORT_NODE (id=81): Consumption=48.02 MB
          EXCHANGE_NODE (id=147): Consumption=0
          DataStreamRecvr: Consumption=0
        Block Manager: Limit=40.00 GB Consumption=37.72 GB
        Fragment 5349fe64e3b9335f:3a9af159237697dc: Consumption=344.00 KB
          UNION_NODE (id=0): Consumption=0
          AGGREGATION_NODE (id=89): Consumption=0
          EXCHANGE_NODE (id=88): Consumption=0
          AGGREGATION_NODE (id=96): Consumption=0
          EXCHANGE_NODE (id=95): Consumption=0
          AGGREGATION_NODE (id=103): Consumption=0
          EXCHANGE_NODE (id=102): Consumption=0
          AGGREGATION_NODE (id=110): Consumption=0
          EXCHANGE_NODE (id=109): Consumption=0
          AGGREGATION_NODE (id=117): Consumption=0
          EXCHANGE_NODE (id=116): Consumption=0
          AGGREGATION_NODE (id=124): Consumption=0
          EXCHANGE_NODE (id=123): Consumption=0
          AGGREGATION_NODE (id=131): Consumption=0
          EXCHANGE_NODE (id=130): Consumption=0
          AGGREGATION_NODE (id=138): Consumption=0
          EXCHANGE_NODE (id=137): Consumption=0
          EXCHANGE_NODE (id=146): Consumption=0
      Memory Limit Exceeded
      Query(5349fe64e3b9335f:3a9af1592376979e) Limit: Consumption=37.84 GB
        Fragment 5349fe64e3b9335f:3a9af159237697bf: Consumption=48.03 MB
          SORT_NODE (id=84): Consumption=4.00 KB
          SELECT_NODE (id=83): Consumption=0
          ANALYTIC_EVAL_NODE (id=82): Consumption=0
          SORT_NODE (id=81): Consumption=48.02 MB
          EXCHANGE_NODE (id=147): Consumption=0
          DataStreamRecvr: Consumption=0
        Block Manager: Limit=40.00 GB Consumption=37.84 GB
        Fragment 5349fe64e3b9335f:3a9af159237697df: Consumption=344.00 KB
          UNION_NODE (id=0): Consumption=0
          AGGREGATION_NODE (id=89): Consumption=0
          EXCHANGE_NODE (id=88): Consumption=0
          AGGREGATION_NODE (id=96): Consumption=0
          EXCHANGE_NODE (id=95): Consumption=0
          AGGREGATION_NODE (id=103): Consumption=0
          EXCHANGE_NODE (id=102): Consumption=0
          AGGREGATION_NODE (id=110): Consumption=0
          EXCHANGE_NODE (id=109): Consumption=0
          AGGREGATION_NODE (id=117): Consumption=0
          EXCHANGE_NODE (id=116): Consumption=0
          AGGREGATION_NODE (id=124): Consumption=0
          EXCHANGE_NODE (id=123): Consumption=0
          AGGREGATION_NODE (id=131): Consumption=0
          EXCHANGE_NODE (id=130): Consumption=0
          AGGREGATION_NODE (id=138): Consumption=0
          EXCHANGE_NODE (id=137): Consumption=0
          EXCHANGE_NODE (id=146): Consumption=0
      

        Attachments

        1. query67.sql.1.out
          10 kB
          Jim Apple
        2. query67.sql.1.out
          10 kB
          Mostafa Mokhtar

          Issue Links

            Activity

              People

              • Assignee:
                tarmstrong Tim Armstrong
                Reporter:
                mmokhtar Mostafa Mokhtar
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: