question Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im in Archives of Support Questions (Read Only)

Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in impala 1.1

nagendrarai — Fri, 16 Sep 2022 09:14:04 GMT

Hi, I have 2 node (Namenode + 2 Datanode) setup for Hadoop (CDH 5.2) , my servers are centos 6.5 64 bit (2 core with 4GB RAM). I also have one more cluster (CDH 4.2) having similar configuration.

Now my problem is that, I have one sql query which is running flawless on CDH 4.2 cluster but giving "Backend 6:Memory Limit Exceeded" in new CDH 5.2 cluster.

I have also increased datanode RAM upto 8GB and set MEM_LIMIT=6G in impala, but still getting same error.

I have also tried "explain" statement to check memory usage by query and it is showing per node memory 2.3G, then why I am getting this error.

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

Dimitris — Sat, 22 Nov 2014 17:19:17 GMT

Hi,

Is the query that is failing running in isolation or are other workloads running at the same time? Can you post the profile information for that query and any relevant sections from the impalad log?

Dimitris

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Mon, 24 Nov 2014 11:30:49 GMT

Hi Dimitris,

This query is running in isolation, there are no other workload. below are the query profile and impala logs.

[server2.mydomain.com:21000] > profile;
Query Runtime Profile:
Query (id=92476f3f27f301ac:a1a07af98ab08da5):
  Summary:
    Session ID: 2d4ede846bdcd77d:c587f0a88636d6be
    Session Type: BEESWAX
    Start Time: 2014-11-24 16:48:47.207538000
    End Time: 2014-11-24 16:49:02.334840000
    Query Type: QUERY
    Query State: EXCEPTION
    Query Status: Memory limit exceeded
Cannot perform hash aggregation. Partitioned input data too many times. This could mean there is too much skew in the data or the memory limit is set too low.
    Impala Version: impalad version 2.0.0-cdh5 RELEASE (build ecf30af0b4d6e56ea80297df2189367ada6b7da7)
    User: root
    Connected User: root
    Delegated User:
    Network Address: ::ffff:10.6.102.133:59128
    Default Db: default
    Sql Statement: select case when d.ip_location is null then 'internet' else d.ip_location end, c.access_date, c.usr_id from MST_LOCATION d right outer join (select distinct ip_address, a.access_date, a.usr_id,a.cnt from logs_sc1 a inner join (select access_date,a.usr_id from (select a.ip_address,case when a.ip_location is null then 'internet' else a.ip_location end ,access_date,b.usr_id,b.cnt FROM logs_sc1 B Left outer join MST_LOCATION A on SUBSTR(a.ip_address,1,5) = SUBSTR(b.ip_address,1,5) WHERE b.ip_address <> "10.6.1.230" and b.sp_used is not null and page_used like '%welcome.jsp%' group by a.ip_address, a.ip_location, access_date, b.usr_id,b.cnt) A group by access_date, a.usr_id, cnt having count(1) > 1)b on a.access_date =b.access_date and a.usr_id=b.usr_id )c on SUBSTR(c.ip_address,1,5) = SUBSTR(d.ip_address,1,5) group by d.ip_location, c.access_date , c.usr_id order by c.usr_id,c.access_date limit 10000
    Coordinator: server2:22000
    Plan:
----------------
Estimated Per-Host Requirements: Memory=2.59GB VCores=2
WARNING: The following tables are missing relevant table and/or column statistics.
default.logs_sc1, default.mst_location

F09:PLAN FRAGMENT [UNPARTITIONED]
  24:MERGING-EXCHANGE [UNPARTITIONED]
     order by: c.usr_id ASC, c.access_date ASC
     limit: 10000
     hosts=2 per-host-mem=unavailable
     tuple-ids=11 row-size=45B cardinality=10000

F08:PLAN FRAGMENT [HASH(d.ip_location,c.access_date,c.usr_id)]
  DATASTREAM SINK [FRAGMENT=F09, EXCHANGE=24, UNPARTITIONED]
  11:TOP-N [LIMIT=10000]
  |  order by: c.usr_id ASC, c.access_date ASC
  |  hosts=2 per-host-mem=439.45KB
  |  tuple-ids=11 row-size=45B cardinality=10000
  |
  23:AGGREGATE [FINALIZE]
  |  group by: d.ip_location, c.access_date, c.usr_id
  |  hosts=2 per-host-mem=128.00MB
  |  tuple-ids=10 row-size=45B cardinality=unavailable
  |
  22:EXCHANGE [HASH(d.ip_location,c.access_date,c.usr_id)]
     hosts=2 per-host-mem=0B
     tuple-ids=10 row-size=45B cardinality=unavailable

F07:PLAN FRAGMENT [HASH(substr(d.ip_address, 1, 5))]
  DATASTREAM SINK [FRAGMENT=F08, EXCHANGE=22, HASH(d.ip_location,c.access_date,c.usr_id)]
  10:AGGREGATE
  |  group by: d.ip_location, a.access_date, a.usr_id
  |  hosts=2 per-host-mem=128.00MB
  |  tuple-ids=10 row-size=45B cardinality=unavailable
  |
  09:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
  |  hash predicates: substr(d.ip_address, 1, 5) = substr(ip_address, 1, 5)
  |  hosts=2 per-host-mem=2.00GB
  |  tuple-ids=0N,8 row-size=77B cardinality=unavailable
  |
  |--21:EXCHANGE [HASH(substr(ip_address, 1, 5))]
  |     hosts=2 per-host-mem=0B
  |     tuple-ids=8 row-size=47B cardinality=unavailable
  |
  20:EXCHANGE [HASH(substr(d.ip_address, 1, 5))]
     hosts=2 per-host-mem=0B
     tuple-ids=0 row-size=30B cardinality=unavailable

F06:PLAN FRAGMENT [HASH(ip_address,a.access_date,a.usr_id,a.cnt)]
  DATASTREAM SINK [FRAGMENT=F07, EXCHANGE=21, HASH(substr(ip_address, 1, 5))]
  19:AGGREGATE [FINALIZE]
  |  group by: ip_address, a.access_date, a.usr_id, a.cnt
  |  hosts=2 per-host-mem=128.00MB
  |  tuple-ids=8 row-size=47B cardinality=unavailable
  |
  18:EXCHANGE [HASH(ip_address,a.access_date,a.usr_id,a.cnt)]
     hosts=2 per-host-mem=0B
     tuple-ids=8 row-size=47B cardinality=unavailable

F01:PLAN FRAGMENT [RANDOM]
  DATASTREAM SINK [FRAGMENT=F06, EXCHANGE=18, HASH(ip_address,a.access_date,a.usr_id,a.cnt)]
  08:AGGREGATE
  |  group by: ip_address, a.access_date, a.usr_id, a.cnt
  |  hosts=2 per-host-mem=128.00MB
  |  tuple-ids=8 row-size=47B cardinality=unavailable
  |
  07:HASH JOIN [INNER JOIN, BROADCAST]
  |  hash predicates: a.access_date = access_date, a.usr_id = a.usr_id
  |  hosts=2 per-host-mem=2.00GB
  |  tuple-ids=1,6 row-size=87B cardinality=unavailable
  |
  |--17:EXCHANGE [BROADCAST]
  |     hosts=2 per-host-mem=0B
  |     tuple-ids=6 row-size=40B cardinality=unavailable
  |
  01:SCAN HDFS [default.logs_sc1 a, RANDOM]
     partitions=1/1 size=968.40MB
     table stats: unavailable
     column stats: unavailable
     hosts=2 per-host-mem=352.00MB
     tuple-ids=1 row-size=47B cardinality=unavailable

F05:PLAN FRAGMENT [HASH(access_date,a.usr_id,cnt)]
  DATASTREAM SINK [FRAGMENT=F01, EXCHANGE=17, BROADCAST]
  16:AGGREGATE [FINALIZE]
  |  output: count:merge(1)
  |  group by: access_date, a.usr_id, cnt
  |  having: count(1) > 1
  |  hosts=2 per-host-mem=128.00MB
  |  tuple-ids=6 row-size=40B cardinality=unavailable
  |
  15:EXCHANGE [HASH(access_date,a.usr_id,cnt)]
     hosts=2 per-host-mem=0B
     tuple-ids=6 row-size=40B cardinality=unavailable

F04:PLAN FRAGMENT [HASH(a.ip_address,a.ip_location,access_date,b.usr_id,b.cnt)]
  DATASTREAM SINK [FRAGMENT=F05, EXCHANGE=15, HASH(access_date,a.usr_id,cnt)]
  06:AGGREGATE
  |  output: count(1)
  |  group by: access_date, b.usr_id, b.cnt
  |  hosts=2 per-host-mem=128.00MB
  |  tuple-ids=6 row-size=40B cardinality=unavailable
  |
  14:AGGREGATE [FINALIZE]
  |  group by: a.ip_address, a.ip_location, access_date, b.usr_id, b.cnt
  |  hosts=2 per-host-mem=128.00MB
  |  tuple-ids=4 row-size=62B cardinality=unavailable
  |
  13:EXCHANGE [HASH(a.ip_address,a.ip_location,access_date,b.usr_id,b.cnt)]
     hosts=2 per-host-mem=0B
     tuple-ids=4 row-size=62B cardinality=unavailable

F02:PLAN FRAGMENT [RANDOM]
  DATASTREAM SINK [FRAGMENT=F04, EXCHANGE=13, HASH(a.ip_address,a.ip_location,access_date,b.usr_id,b.cnt)]
  05:AGGREGATE
  |  group by: a.ip_address, a.ip_location, access_date, b.usr_id, b.cnt
  |  hosts=2 per-host-mem=128.00MB
  |  tuple-ids=4 row-size=62B cardinality=unavailable
  |
  04:HASH JOIN [LEFT OUTER JOIN, BROADCAST]
  |  hash predicates: substr(b.ip_address, 1, 5) = substr(a.ip_address, 1, 5)
  |  hosts=2 per-host-mem=2.00GB
  |  tuple-ids=2,3N row-size=107B cardinality=unavailable
  |
  |--12:EXCHANGE [BROADCAST]
  |     hosts=2 per-host-mem=0B
  |     tuple-ids=3 row-size=30B cardinality=unavailable
  |
  02:SCAN HDFS [default.logs_sc1 B, RANDOM]
     partitions=1/1 size=968.40MB
     predicates: b.ip_address != '10.6.1.230', b.sp_used IS NOT NULL, page_used LIKE '%welcome.jsp%'
     table stats: unavailable
     column stats: unavailable
     hosts=2 per-host-mem=480.00MB
     tuple-ids=2 row-size=77B cardinality=unavailable

F03:PLAN FRAGMENT [RANDOM]
  DATASTREAM SINK [FRAGMENT=F02, EXCHANGE=12, BROADCAST]
  03:SCAN HDFS [default.mst_location A, RANDOM]
     partitions=1/1 size=250B
     table stats: unavailable
     column stats: unavailable
     hosts=2 per-host-mem=32.00MB
     tuple-ids=3 row-size=30B cardinality=unavailable

F00:PLAN FRAGMENT [RANDOM]
  DATASTREAM SINK [FRAGMENT=F07, EXCHANGE=20, HASH(substr(d.ip_address, 1, 5))]
  00:SCAN HDFS [default.mst_location d, RANDOM]
     partitions=1/1 size=250B
     table stats: unavailable
     column stats: unavailable
     hosts=2 per-host-mem=32.00MB
     tuple-ids=0 row-size=30B cardinality=unavailable
----------------
    Estimated Per-Host Mem: 2785017856
    Estimated Per-Host VCores: 2
    Tables Missing Stats: default.logs_sc1,default.mst_location
    Admission result: Admitted immediately
    Request Pool: default-pool
    ExecSummary:
Operator                #Hosts   Avg Time   Max Time    #Rows  Est. #Rows   Peak Mem  Est. Peak Mem  Detail                   
------------------------------------------------------------------------------------------------------------------------------------
24:MERGING-EXCHANGE          1    29.64us    29.64us        0      10.00K          0        -1.00 B  UNPARTITIONED            
11:TOP-N                     1   16.211us   16.211us        0      10.00K    4.00 KB      439.45 KB                           
23:AGGREGATE                 1  248.979ms  248.979ms        0          -1    6.25 MB      128.00 MB  FINALIZE                 
22:EXCHANGE                  1        0ns        0ns        0          -1          0              0  HASH(d.ip_location,c.access...
10:AGGREGATE                 1  251.322ms  251.322ms        0          -1    6.25 MB      128.00 MB                           
09:HASH JOIN                 1        0ns        0ns        0          -1    8.00 KB        2.00 GB  RIGHT OUTER JOIN, PARTITIONED
|--21:EXCHANGE               1        0ns        0ns        0          -1          0              0  HASH(substr(ip_address, 1, 5))
|  19:AGGREGATE              2  229.878ms  252.185ms        0          -1    6.25 MB      128.00 MB  FINALIZE                 
|  18:EXCHANGE               2        0ns        0ns        0          -1          0              0  HASH(ip_address,a.access_da...
|  08:AGGREGATE              2  244.661ms  252.171ms        0          -1    6.25 MB      128.00 MB                           
|  07:HASH JOIN              2    5.549ms    6.881ms        0          -1    8.00 KB        2.00 GB  INNER JOIN, BROADCAST    
|  |--17:EXCHANGE            2        0ns        0ns        0          -1          0              0  BROADCAST                
|  |  16:AGGREGATE           2  220.740ms  288.538ms        0          -1    6.25 MB      128.00 MB  FINALIZE                 
|  |  15:EXCHANGE            2        0ns        0ns        0          -1          0              0  HASH(access_date,a.usr_id,cnt)
|  |  06:AGGREGATE           2  204.343ms  254.801ms        0          -1    6.25 MB      128.00 MB                           
|  |  14:AGGREGATE           2    3.274ms    3.331ms        0          -1    6.25 MB      128.00 MB  FINALIZE                 
|  |  13:EXCHANGE            2        0ns        0ns        0          -1          0              0  HASH(a.ip_address,a.ip_loca...
|  |  05:AGGREGATE           2        0ns        0ns        0          -1  569.32 MB      128.00 MB                           
|  |  04:HASH JOIN           2  134.457ms  215.946ms  904.78K          -1   21.29 MB        2.00 GB  LEFT OUTER JOIN, BROADCAST
|  |  |--12:EXCHANGE         2   12.323us   13.368us       11          -1          0              0  BROADCAST                
|  |  |  03:SCAN HDFS        1   251.71ms   251.71ms       11          -1   54.00 KB       32.00 MB  default.mst_location A   
|  |  02:SCAN HDFS           2    7s192ms    7s231ms  904.78K          -1  156.00 MB      480.00 MB  default.logs_sc1 B       
|  01:SCAN HDFS              2   115.86us  133.579us        0          -1          0      352.00 MB  default.logs_sc1 a       
20:EXCHANGE                  1  176.181us  176.181us        0          -1          0              0  HASH(substr(d.ip_address, 1...
00:SCAN HDFS                 1   282.55ms   282.55ms       11          -1   54.00 KB       32.00 MB  default.mst_location d   
    Query Timeline: 15s135ms
       - Start execution: 63.453us (63.453us)
       - Planning finished: 161.169ms (161.105ms)
       - Submit for admission: 161.444ms (274.910us)
       - Completed admission: 161.454ms (10.504us)
       - Ready to start remote fragments: 164.93ms (2.639ms)
       - Remote fragments started: 2s575ms (2s411ms)
       - Rows available: 14s726ms (12s150ms)
       - Unregister query: 15s127ms (401.62ms)

Impala Log:

==> /var/log/impala/impalad.INFO <==
W1124 16:49:29.641473 26908 HdfsScanNode.java:720] Per-host mem cost 528.00MB exceeded per-host upper bound 480.00MB.

==> /var/log/impala/impalad.server2.impala.log.INFO.20141124-155700.26843 <==
W1124 16:49:29.641473 26908 HdfsScanNode.java:720] Per-host mem cost 528.00MB exceeded per-host upper bound 480.00MB.

Regards,

Nagendra Rai

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

Dimitris — Mon, 24 Nov 2014 19:26:50 GMT

Hi,

Quick question. Do you have statistics in the tables you're querying? It doesn't look like it. Can you run compute stats and see if the problem persists. Also, the log messages you posted are just warnings. The problem is not with the scan nodes but with the computation of the hash join.

Dimitris

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Mon, 24 Nov 2014 20:02:29 GMT

Hi Dimitris,

I am using same table with same query in hive and getting the ouput. I will try compute stat and will let you know.

Thanks & Regards,

Nagendra Rai

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Tue, 25 Nov 2014 11:00:02 GMT

Hi Dimitris,

After running "compute stat logs_sc1" I was getting below mentioned error for all queries having "logs_sc1" table in it.

ERROR: AnalysisException: Failed to load metadata for table: default.logs_sc1
CAUSED BY: TableLoadingException: Failed to load metadata for table: logs_sc1
CAUSED BY: TTransportException: java.net.SocketException: Broken pipe
CAUSED BY: SocketException: Broken pipe

Then I set below mentioned property in hive-site.xml and restarted hive metastore to remove this error.

<property>
<name>hive.metastore.try.direct.sql</name>
<value>false</value>
</property>

But I was still getting "Memory Limit Exceeded" error, then I executed explain statement and was surprised to see that Estimated Per-Host Requirements: Memory becomes 14.43GB from 2.83GB.

Finally I was able to run this query by increasing impala node (server) memory up to 16GB and setting impala memory to 15GB "set MEM_LIMIT=15G;"

Now my query is why CDH 5.2 and impala 2 require that much memory for same data and same query which is running smooth on CDH 4.2 and impala 1.1.1 server having 4GB memory

Explain statement output from CDH 5 for table with compute stat:

Query: explain select case when d.ip_location is null then 'internet' else d.ip_location end, c.access_date, c.usr_id from MST_LOCATION d right outer join (select distinct ip_address, a.access_date, a.usr_id,a.cnt from logs_sc1 a inner join (select access_date,a.usr_id from (select a.ip_address,case when a.ip_location is null then 'internet' else a.ip_location end ,access_date,b.usr_id,b.cnt FROM logs_sc1 B Left outer join MST_LOCATION A on SUBSTR(a.ip_address,1,5) = SUBSTR(b.ip_address,1,5) WHERE b.ip_address <> "10.6.1.230" and b.sp_used is not null and page_used like '%welcome.jsp%' group by a.ip_address, a.ip_location, access_date, b.usr_id,b.cnt) A group by access_date, a.usr_id, cnt having count(1) > 1)b on a.access_date =b.access_date and a.usr_id=b.usr_id )c on SUBSTR(c.ip_address,1,5) = SUBSTR(d.ip_address,1,5) group by d.ip_location, c.access_date , c.usr_id order by c.usr_id,c.access_date limit 10000
+-------------------------------------------------------------------------------------------------------+
| Explain String                                                                                        |
+-------------------------------------------------------------------------------------------------------+
| Estimated Per-Host Requirements: Memory=14.43GB VCores=2                                              |
| WARNING: The following tables are missing relevant table and/or column statistics.                    |
| default.mst_location                                                                                  |
|                                                                                                       |
| 23:MERGING-EXCHANGE [UNPARTITIONED]                                                                   |
| |  order by: c.usr_id ASC, c.access_date ASC                                                          |
| |  limit: 10000                                                                                       |
| |                                                                                                     |
| 11:TOP-N [LIMIT=10000]                                                                                |
| |  order by: c.usr_id ASC, c.access_date ASC                                                          |
| |                                                                                                     |
| 22:AGGREGATE [FINALIZE]                                                                               |
| |  group by: d.ip_location, c.access_date, c.usr_id                                                   |
| |                                                                                                     |
| 21:EXCHANGE [HASH(d.ip_location,c.access_date,c.usr_id)]                                              |
| |                                                                                                     |
| 10:AGGREGATE                                                                                          |
| |  group by: d.ip_location, a.access_date, a.usr_id                                                   |
| |                                                                                                     |
| 09:HASH JOIN [LEFT OUTER JOIN, BROADCAST]                                                             |
| |  hash predicates: substr(ip_address, 1, 5) = substr(d.ip_address, 1, 5)                             |
| |                                                                                                     |
| |--20:EXCHANGE [BROADCAST]                                                                            |
| |  |                                                                                                  |
| |  00:SCAN HDFS [default.mst_location d]                                                              |
| |     partitions=1/1 size=250B                                                                        |
| |                                                                                                     |
| 19:AGGREGATE [FINALIZE]                                                                               |
| |  group by: ip_address, a.access_date, a.usr_id, a.cnt                                               |
| |                                                                                                     |
| 18:EXCHANGE [HASH(ip_address,a.access_date,a.usr_id,a.cnt)]                                           |
| |                                                                                                     |
| 08:AGGREGATE                                                                                          |
| |  group by: ip_address, a.access_date, a.usr_id, a.cnt                                               |
| |                                                                                                     |
| 07:HASH JOIN [INNER JOIN, BROADCAST]                                                                  |
| |  hash predicates: a.access_date = access_date, a.usr_id = a.usr_id                                  |
| |                                                                                                     |
| |--17:EXCHANGE [BROADCAST]                                                                            |
| |  |                                                                                                  |
| |  16:AGGREGATE [FINALIZE]                                                                            |
| |  |  output: count:merge(1)                                                                          |
| |  |  group by: access_date, a.usr_id, cnt                                                            |
| |  |  having: count(1) > 1                                                                            |
| |  |                                                                                                  |
| |  15:EXCHANGE [HASH(access_date,a.usr_id,cnt)]                                                       |
| |  |                                                                                                  |
| |  06:AGGREGATE                                                                                       |
| |  |  output: count(1)                                                                                |
| |  |  group by: access_date, b.usr_id, b.cnt                                                          |
| |  |                                                                                                  |
| |  14:AGGREGATE [FINALIZE]                                                                            |
| |  |  group by: a.ip_address, a.ip_location, access_date, b.usr_id, b.cnt                             |
| |  |                                                                                                  |
| |  13:EXCHANGE [HASH(a.ip_address,a.ip_location,access_date,b.usr_id,b.cnt)]                          |
| |  |                                                                                                  |
| |  05:AGGREGATE                                                                                       |
| |  |  group by: a.ip_address, a.ip_location, access_date, b.usr_id, b.cnt                             |
| |  |                                                                                                  |
| |  04:HASH JOIN [LEFT OUTER JOIN, BROADCAST]                                                          |
| |  |  hash predicates: substr(b.ip_address, 1, 5) = substr(a.ip_address, 1, 5)                        |
| |  |                                                                                                  |
| |  |--12:EXCHANGE [BROADCAST]                                                                         |
| |  |  |                                                                                               |
| |  |  03:SCAN HDFS [default.mst_location A]                                                           |
| |  |     partitions=1/1 size=250B                                                                     |
| |  |                                                                                                  |
| |  02:SCAN HDFS [default.logs_sc1 B]                                                                  |
| |     partitions=1/1 size=968.40MB                                                                    |
| |     predicates: b.ip_address != '10.6.1.230', b.sp_used IS NOT NULL, page_used LIKE '%welcome.jsp%' |
| |                                                                                                     |
| 01:SCAN HDFS [default.logs_sc1 a]                                                                     |
|    partitions=1/1 size=968.40MB                                                                       |
+-------------------------------------------------------------------------------------------------------+
Fetched 73 row(s) in 0.23s

Explain statement output from CDH 5 for table without compute stat (This is different table with same config and data), earlier I was geeting same memory error for this table too, but it is running fine without compute stat statement.

+----------------------------------------------------------------------------------------------------------+
| Explain String                                                                                           |
+----------------------------------------------------------------------------------------------------------+
| Estimated Per-Host Requirements: Memory=2.59GB VCores=2                                                  |
| WARNING: The following tables are missing relevant table and/or column statistics.                       |
| default.logs_sc, default.mst_location                                                                    |
|                                                                                                          |
| 24:MERGING-EXCHANGE [UNPARTITIONED]                                                                      |
| |  order by: c.usr_id ASC, c.access_date ASC                                                             |
| |  limit: 10000                                                                                          |
| |                                                                                                        |
| 11:TOP-N [LIMIT=10000]                                                                                   |
| |  order by: c.usr_id ASC, c.access_date ASC                                                             |
| |                                                                                                        |
| 23:AGGREGATE [FINALIZE]                                                                                  |
| |  group by: d.ip_location, c.access_date, c.usr_id                                                      |
| |                                                                                                        |
| 22:EXCHANGE [HASH(d.ip_location,c.access_date,c.usr_id)]                                                 |
| |                                                                                                        |
| 10:AGGREGATE                                                                                             |
| |  group by: d.ip_location, a.access_date, a.usr_id                                                      |
| |                                                                                                        |
| 09:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]                                                             |
| |  hash predicates: substr(d.ip_address, 1, 5) = substr(ip_address, 1, 5)                                |
| |                                                                                                        |
| |--21:EXCHANGE [HASH(substr(ip_address, 1, 5))]                                                          |
| |  |                                                                                                     |
| |  19:AGGREGATE [FINALIZE]                                                                               |
| |  |  group by: ip_address, a.access_date, a.usr_id, a.cnt                                               |
| |  |                                                                                                     |
| |  18:EXCHANGE [HASH(ip_address,a.access_date,a.usr_id,a.cnt)]                                           |
| |  |                                                                                                     |
| |  08:AGGREGATE                                                                                          |
| |  |  group by: ip_address, a.access_date, a.usr_id, a.cnt                                               |
| |  |                                                                                                     |
| |  07:HASH JOIN [INNER JOIN, BROADCAST]                                                                  |
| |  |  hash predicates: a.access_date = access_date, a.usr_id = a.usr_id                                  |
| |  |                                                                                                     |
| |  |--17:EXCHANGE [BROADCAST]                                                                            |
| |  |  |                                                                                                  |
| |  |  16:AGGREGATE [FINALIZE]                                                                            |
| |  |  |  output: count:merge(1)                                                                          |
| |  |  |  group by: access_date, a.usr_id, cnt                                                            |
| |  |  |  having: count(1) > 1                                                                            |
| |  |  |                                                                                                  |
| |  |  15:EXCHANGE [HASH(access_date,a.usr_id,cnt)]                                                       |
| |  |  |                                                                                                  |
| |  |  06:AGGREGATE                                                                                       |
| |  |  |  output: count(1)                                                                                |
| |  |  |  group by: access_date, b.usr_id, b.cnt                                                          |
| |  |  |                                                                                                  |
| |  |  14:AGGREGATE [FINALIZE]                                                                            |
| |  |  |  group by: a.ip_address, a.ip_location, access_date, b.usr_id, b.cnt                             |
| |  |  |                                                                                                  |
| |  |  13:EXCHANGE [HASH(a.ip_address,a.ip_location,access_date,b.usr_id,b.cnt)]                          |
| |  |  |                                                                                                  |
| |  |  05:AGGREGATE                                                                                       |
| |  |  |  group by: a.ip_address, a.ip_location, access_date, b.usr_id, b.cnt                             |
| |  |  |                                                                                                  |
| |  |  04:HASH JOIN [LEFT OUTER JOIN, BROADCAST]                                                          |
| |  |  |  hash predicates: substr(b.ip_address, 1, 5) = substr(a.ip_address, 1, 5)                        |
| |  |  |                                                                                                  |
| |  |  |--12:EXCHANGE [BROADCAST]                                                                         |
| |  |  |  |                                                                                               |
| |  |  |  03:SCAN HDFS [default.mst_location A]                                                           |
| |  |  |     partitions=1/1 size=250B                                                                     |
| |  |  |                                                                                                  |
| |  |  02:SCAN HDFS [default.logs_sc B]                                                                   |
| |  |     partitions=1/1 size=1.12GB                                                                      |
| |  |     predicates: b.ip_address != '10.6.1.230', b.sp_used IS NOT NULL, page_used LIKE '%welcome.jsp%' |
| |  |                                                                                                     |
| |  01:SCAN HDFS [default.logs_sc a]                                                                      |
| |     partitions=1/1 size=1.12GB                                                                         |
| |                                                                                                        |
| 20:EXCHANGE [HASH(substr(d.ip_address, 1, 5))]                                                           |
| |                                                                                                        |
| 00:SCAN HDFS [default.mst_location d]                                                                    |
|    partitions=1/1 size=250B                                                                              |
+----------------------------------------------------------------------------------------------------------+
Fetched 75 row(s) in 0.26s

Conclusion:

1) Without compute stat output of explain statement was not correct.

2) After compute stat I was able to know real memory requirement.

3) Still confused why CDH5.2 and impala 2 require 4 to 5 times more memory than CDH 4.2 and impala 1.1.1

Regards,

Nagendra Rai

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Tue, 25 Nov 2014 12:55:58 GMT

Hi Dimitris,

One more update, there seems to be an issue/bug with impala.

Now I am running single impala node with 16GB ram, as mentioned in previous post. But during execution of query my server memory utilization never goes beyond 4.6GB (with cache and buffer) and 2.3GB (without cache and buffer), then why it is asking for 14GB ram without even using it and crashing with “memory exceeded error” if not provided.

Below are server memory utilization details during query execution in impala.

***********
Tue Nov 25 18:06:05 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4589      11170          0         24       2303
-/+ buffers/cache:       2261      13498
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:06 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4579      11180          0         24       2295
-/+ buffers/cache:       2259      13500
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:07 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4580      11179          0         24       2295
-/+ buffers/cache:       2260      13499
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:08 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4583      11176          0         24       2295
-/+ buffers/cache:       2262      13496
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:09 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4583      11175          0         24       2295
-/+ buffers/cache:       2263      13496
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:10 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4583      11175          0         24       2295
-/+ buffers/cache:       2263      13495
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:11 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:12 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13493
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:13 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4586      11173          0         24       2295
-/+ buffers/cache:       2266      13493
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:14 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4586      11173          0         24       2295
-/+ buffers/cache:       2266      13493
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:15 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4586      11173          0         24       2295
-/+ buffers/cache:       2266      13493
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:16 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4586      11172          0         24       2295
-/+ buffers/cache:       2266      13493
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:17 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2266      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:18 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2266      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:19 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4583      11176          0         24       2295
-/+ buffers/cache:       2263      13496
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:20 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4583      11176          0         24       2295
-/+ buffers/cache:       2263      13496
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:21 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11174          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:22 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11174          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:23 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11174          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:24 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:25 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:26 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:27 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:28 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:29 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:30 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:31 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2267      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:32 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2267      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:33 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2267      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:34 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2267      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:35 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2267      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:36 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4591      11168          0         24       2302
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:37 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4602      11156          0         24       2303
-/+ buffers/cache:       2274      13485
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:38 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4603      11156          0         24       2303
-/+ buffers/cache:       2274      13484
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:39 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4603      11156          0         24       2303
-/+ buffers/cache:       2274      13484
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:40 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4603      11156          0         24       2303
-/+ buffers/cache:       2274      13484
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:41 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:42 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:43 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2275      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:44 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:45 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:46 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:47 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:48 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:49 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:50 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:51 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4606      11153          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:52 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4605      11153          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:53 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4605      11153          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:54 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4605      11153          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:55 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4605      11154          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:56 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4605      11154          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:57 IST 2014
*************
^C
[root@server2 ~]#

Regards,

Nagendra Rai

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Tue, 25 Nov 2014 13:04:10 GMT

Hi Dimitris,

One more update, there seems to be an issue/bug with impala.

***********
Tue Nov 25 18:06:05 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4589      11170          0         24       2303
-/+ buffers/cache:       2261      13498
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:06 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4579      11180          0         24       2295
-/+ buffers/cache:       2259      13500
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:07 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4580      11179          0         24       2295
-/+ buffers/cache:       2260      13499
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:08 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4583      11176          0         24       2295
-/+ buffers/cache:       2262      13496
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:09 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4583      11175          0         24       2295
-/+ buffers/cache:       2263      13496
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:10 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4583      11175          0         24       2295
-/+ buffers/cache:       2263      13495
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:11 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:12 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13493
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:13 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4586      11173          0         24       2295
-/+ buffers/cache:       2266      13493
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:14 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4586      11173          0         24       2295
-/+ buffers/cache:       2266      13493
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:15 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4586      11173          0         24       2295
-/+ buffers/cache:       2266      13493
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:16 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4586      11172          0         24       2295
-/+ buffers/cache:       2266      13493
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:17 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2266      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:18 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2266      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:19 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4583      11176          0         24       2295
-/+ buffers/cache:       2263      13496
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:20 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4583      11176          0         24       2295
-/+ buffers/cache:       2263      13496
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:21 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11174          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:22 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11174          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:23 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11174          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:24 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:25 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:26 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:27 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:28 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:29 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:30 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4585      11173          0         24       2295
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:31 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2267      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:32 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2267      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:33 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2267      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:34 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2267      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:35 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4587      11172          0         24       2295
-/+ buffers/cache:       2267      13492
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:36 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4591      11168          0         24       2302
-/+ buffers/cache:       2265      13494
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:37 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4602      11156          0         24       2303
-/+ buffers/cache:       2274      13485
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:38 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4603      11156          0         24       2303
-/+ buffers/cache:       2274      13484
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:39 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4603      11156          0         24       2303
-/+ buffers/cache:       2274      13484
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:40 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4603      11156          0         24       2303
-/+ buffers/cache:       2274      13484
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:41 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:42 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:43 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2275      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:44 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:45 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:46 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:47 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:48 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:49 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:50 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4604      11155          0         24       2303
-/+ buffers/cache:       2276      13483
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:51 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4606      11153          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:52 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4605      11153          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:53 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4605      11153          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:54 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4605      11153          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:55 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4605      11154          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:56 IST 2014
*************
             total       used       free     shared    buffers     cached
Mem:         15759       4605      11154          0         24       2303
-/+ buffers/cache:       2277      13482
Swap:         1023          0       1023
***********
Tue Nov 25 18:06:57 IST 2014
*************
^C
[root@server2 ~]#

Regards,

Nagendra Rai

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

Dimitris — Tue, 25 Nov 2014 17:29:23 GMT

Hi,

The explain is not really usefull for identifying the memory offenders. Can you post the output of profile (http://www.cloudera.com/content/cloudera/en/documentation/cloudera-impala/latest/topics/impala_explain_plan.html#perf_profile_unique_1) after you've run the query? That will shed some light on the actual memory requirements of each execution node in your query.

Thanks

Dimitris

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Tue, 25 Nov 2014 17:50:09 GMT

Hi,

In earlier post I have provided query profile details, I was not able to post whole output because it was crossing 50000 word msg limit and here we dont have any file attached option.

If you want I will provide the details again.

One more thing I would like to tell, when I am running this query my server RAM utilization never goes beyond 4.6 GB, then why impala is asking for 14GB memory and crashing if that much memory is not available.

Regards,

Nagendra Rai

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

Dimitris — Tue, 25 Nov 2014 18:03:41 GMT

I meant the query profile of query you run in Impala 2.0 by setting MEM_LIMIT=15G. Just post the section with the estimated/peak memory usage per node. Is it the same profile as the one you posted earlier?

Dimitris

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Tue, 25 Nov 2014 18:46:37 GMT

Hi Dimitris,

Query profile posted earlier was for failing query, I will post query profile of query with MEM_LIMIT=15G which was executed successfully.

Regards,

Nagendra Rai

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Wed, 26 Nov 2014 09:35:45 GMT

Hi Dimitris,

Below are the query profile details (some part of it) of query that is running successfully by increasing server memory. I have also checked PerHostPeakMemUsage requirement and it never goes beyond 1.39GB.

Estimated Per-Host Requirements: Memory=14.43GB VCores=2
WARNING: The following tables are missing relevant table and/or column statistics.
default.mst_location



Operator              #Hosts   Avg Time   Max Time    #Rows  Est. #Rows   Peak Mem  Est. Peak Mem  Detail                     
----------------------------------------------------------------------------------------------------------------------------------
23:MERGING-EXCHANGE        1   331.94us   331.94us    5.20K      10.00K          0        -1.00 B  UNPARTITIONED              
11:TOP-N                   1   12.100ms   12.100ms    5.20K      10.00K    1.00 MB      705.27 KB                             
22:AGGREGATE               1  255.443ms  255.443ms    5.20K      80.53M    6.38 MB      128.00 MB  FINALIZE                   
21:EXCHANGE                1  240.795us  240.795us    5.20K      80.53M          0              0  HASH(d.ip_location,c.access...
10:AGGREGATE               1  256.762ms  256.762ms    5.20K      80.53M    6.28 MB      128.00 MB                             
09:HASH JOIN               1   14.843ms   14.843ms   10.04K      80.53M    4.09 MB        2.00 GB  LEFT OUTER JOIN, BROADCAST 
|--20:EXCHANGE             1    7.663us    7.663us       11          -1          0              0  BROADCAST                  
|  00:SCAN HDFS            1  252.704ms  252.704ms       11          -1   54.00 KB       32.00 MB  default.mst_location d     
19:AGGREGATE               1      9.9ms      9.9ms   10.04K      80.53M    6.38 MB        7.22 GB  FINALIZE                   
18:EXCHANGE                1  317.121us  317.121us   10.04K      80.53M          0              0  HASH(ip_address,a.access_da...
08:AGGREGATE               1  449.591ms  449.591ms   10.04K      80.53M   13.18 MB        7.22 GB                             
07:HASH JOIN               1    1s008ms    1s008ms    1.39M      80.53M    7.75 MB        5.68 MB  INNER JOIN, BROADCAST      
|--17:EXCHANGE             1  809.569us  809.569us    2.42K      80.53K          0              0  BROADCAST                  
|  16:AGGREGATE            1  682.727ms  682.727ms    2.42K      80.53K  776.88 MB       10.00 MB  FINALIZE                   
|  15:EXCHANGE             1   25.278ms   25.278ms  413.62K      80.53K          0              0  HASH(access_date,a.usr_id,cnt)
|  06:AGGREGATE            1  543.432ms  543.432ms  413.62K      80.53K  776.88 MB       10.00 MB                             
|  14:AGGREGATE            1  335.863ms  335.863ms  416.05K      80.53K  776.88 MB      128.00 MB  FINALIZE                   
|  13:EXCHANGE             1   25.230ms   25.230ms  416.05K      80.53K          0              0  HASH(a.ip_address,a.ip_loca...
|  05:AGGREGATE            1    1s448ms    1s448ms  416.05K      80.53K  776.88 MB      128.00 MB                             
|  04:HASH JOIN            1   246.11ms   246.11ms    1.63M      80.53K   21.29 MB        2.00 GB  LEFT OUTER JOIN, BROADCAST 
|  |--12:EXCHANGE          1   10.731us   10.731us       11          -1          0              0  BROADCAST                  
|  |  03:SCAN HDFS         1  241.873ms  241.873ms       11          -1   54.00 KB       32.00 MB  default.mst_location A     
|  02:SCAN HDFS            1   27s538ms   27s538ms    1.63M      80.53K  156.00 MB      480.00 MB  default.logs_sc1 B         
01:SCAN HDFS               1   13s589ms   13s589ms   20.41M      80.53M   82.05 MB      352.00 MB  default.logs_sc1 a         
    Query Timeline: 49s402ms
       - Start execution: 67.955us (67.955us)
       - Planning finished: 216.759ms (216.691ms)
       - Submit for admission: 217.69ms (310.21us)
       - Completed admission: 217.79ms (10.25us)
       - Ready to start remote fragments: 219.667ms (2.588ms)
       - Remote fragments started: 2s337ms (2s117ms)
       - Rows available: 47s448ms (45s111ms)
       - First row fetched: 47s462ms (13.655ms)
       - Unregister query: 49s395ms (1s932ms)
  ImpalaServer:
     - ClientFetchWaitTimer: 1s934ms
     - RowMaterializationTimer: 10.625ms
  Execution Profile a34460384c0f44c8:c196fcdc2790d29d:(Total: 47s225ms, non-child: 0ns, % non-child: 0.00%)
    Fragment start latencies:  count: 8, last: 0.232499, min: 0.212138, max: 0.295248, mean: 0.26005, stddev: 0.0247054
    Per Node Peak Memory Usage: server2:22000(1.39 GB)
     - FinalizationTimer: 0ns
    Coordinator Fragment F08:(Total: 45s106ms, non-child: 2.303ms, % non-child: 0.01%)
      MemoryUsage(1s000ms): 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 8.00 KB, 465.84 KB, 220.01 KB
      ThreadUsage(500.0ms): 1, 1, 1, 1
       - AverageThreadTokens: 1.00
       - PeakMemoryUsage: 522.87 KB (535416)
       - PerHostPeakMemUsage: 0
       - PrepareTime: 33.461us
       - RowsProduced: 5.20K (5203)
       - TotalCpuTime: 49s146ms
       - TotalNetworkReceiveTime: 0ns
       - TotalNetworkSendTime: 0ns
       - TotalStorageWaitTime: 0ns
      BlockMgr:
         - BlockWritesOutstanding: 0
         - BlocksCreated: 980
         - BlocksRecycled: 879
         - BufferedPins: 6
         - BytesWritten: 8.26 MB (8656438)
         - MaxBlockSize: 8.00 MB (8388608)
         - MemoryLimit: 9.97 GB (10703688704)
         - PeakMemoryUsage: 1.28 GB (1372799152)
         - TotalBufferWaitTime: 0ns
         - TotalEncryptionTime: 0ns
         - TotalIntegrityCheckTime: 0ns
         - TotalReadBlockTime: 6.532ms
      EXCHANGE_NODE (id=23):(Total: 45s104ms, non-child: 331.94us, % non-child: 0.00%)
        BytesReceived(1s000ms): 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 233.90 KB, 233.90 KB
         - BytesReceived: 233.90 KB (239518)
         - ConvertRowBatchTime: 0ns
         - DeserializeRowBatchTimer: 701.309us
         - FirstBatchArrivalWaitTime: 45s103ms
         - MergeGetNext: 300.125us
         - MergeGetNextBatch: 30.200us
         - PeakMemoryUsage: 0
         - RowsReturned: 5.20K (5203)
         - RowsReturnedRate: 115.00 /sec
         - SendersBlockedTimer: 0ns
         - SendersBlockedTotalTimer(*): 0ns
    Averaged Fragment F07:(Total: 46s841ms, non-child: 0ns, % non-child: 0.00%)
      split sizes:  min: 0, max: 0, avg: 0, stddev: 0
      completion times: min:46s977ms  max:46s977ms  mean: 46s977ms  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
       - PeakMemoryUsage: 6.42 MB (6728752)
       - PerHostPeakMemUsage: 1.39 GB (1488882840)
       - PrepareTime: 253.281ms
       - RowsProduced: 5.20K (5203)
       - TotalCpuTime: 661.516ms
       - TotalNetworkReceiveTime: 46s569ms
       - TotalNetworkSendTime: 2.51us
       - TotalStorageWaitTime: 0ns
      CodeGen:(Total: 638.47ms, non-child: 638.47ms, % non-child: 100.00%)
         - CodegenTime: 3.817ms
         - CompileTime: 389.131ms
         - LoadTime: 247.316ms
         - ModuleFileSize: 1.94 MB (2032752)
      DataStreamSender (dst_id=23):(Total: 3.668ms, non-child: 3.668ms, % non-child: 100.00%)
         - BytesSent: 233.90 KB (239518)
         - NetworkThroughput(*): 134.03 MB/sec
         - OverallThroughput: 62.26 MB/sec
         - PeakMemoryUsage: 4.00 KB (4096)
         - SerializeBatchTime: 1.828ms
         - ThriftTransmitTime(*): 1.704ms
         - UncompressedRowBatchSize: 478.43 KB (489914)
      SORT_NODE (id=11):(Total: 46s837ms, non-child: 12.100ms, % non-child: 0.03%)
         - PeakMemoryUsage: 1.00 MB (1052672)
         - RowsReturned: 5.20K (5203)
         - RowsReturnedRate: 111.00 /sec
      AGGREGATION_NODE (id=22):(Total: 46s825ms, non-child: 255.443ms, % non-child: 0.55%)
         - BuildTime: 1.680ms
         - GetNewBlockTime: 116.349us
         - GetResultsTime: 269.927us
         - HashBuckets: 32.77K (32768)
         - LargestPartitionPercent: 3
         - MaxPartitionLevel: 0
         - NumRepartitions: 0
         - PartitionsCreated: 32
         - PeakMemoryUsage: 6.38 MB (6690520)
         - PinTime: 0ns
         - RowsRepartitioned: 0
         - RowsReturned: 5.20K (5203)
         - RowsReturnedRate: 111.00 /sec
         - SpilledPartitions: 0
         - UnpinTime: 10.232us
      EXCHANGE_NODE (id=21):(Total: 46s569ms, non-child: 46s569ms, % non-child: 100.00%)
         - BytesReceived: 190.35 KB (194914)
         - ConvertRowBatchTime: 108.155us
         - DeserializeRowBatchTimer: 656.948us
         - FirstBatchArrivalWaitTime: 46s563ms
         - PeakMemoryUsage: 0
         - RowsReturned: 5.20K (5203)
         - RowsReturnedRate: 111.00 /sec
         - SendersBlockedTimer: 0ns
         - SendersBlockedTotalTimer(*): 0ns

Also query summery details. It has big difference between "Est. Peak Mem" and "Peak Mem"

[server2.kotakseconline.com:21000] > summary;
+---------------------+--------+----------+----------+---------+------------+-----------+---------------+-------------------------------------------------------------+
| Operator            | #Hosts | Avg Time | Max Time | #Rows   | Est. #Rows | Peak Mem  | Est. Peak Mem | Detail                                                      |
+---------------------+--------+----------+----------+---------+------------+-----------+---------------+-------------------------------------------------------------+
| 23:MERGING-EXCHANGE | 1      | 331.09us | 331.09us | 5.20K   | 10.00K     | 0 B       | -1 B          | UNPARTITIONED                                               |
| 11:TOP-N            | 1      | 12.10ms  | 12.10ms  | 5.20K   | 10.00K     | 1.00 MB   | 705.27 KB     |                                                             |
| 22:AGGREGATE        | 1      | 255.44ms | 255.44ms | 5.20K   | 80.53M     | 6.38 MB   | 128.00 MB     | FINALIZE                                                    |
| 21:EXCHANGE         | 1      | 240.79us | 240.79us | 5.20K   | 80.53M     | 0 B       | 0 B           | HASH(d.ip_location,c.access_date,c.usr_id)                  |
| 10:AGGREGATE        | 1      | 256.76ms | 256.76ms | 5.20K   | 80.53M     | 6.28 MB   | 128.00 MB     |                                                             |
| 09:HASH JOIN        | 1      | 14.84ms  | 14.84ms  | 10.04K  | 80.53M     | 4.09 MB   | 2.00 GB       | LEFT OUTER JOIN, BROADCAST                                  |
| |--20:EXCHANGE      | 1      | 7.66us   | 7.66us   | 11      | -1         | 0 B       | 0 B           | BROADCAST                                                   |
| |  00:SCAN HDFS     | 1      | 252.70ms | 252.70ms | 11      | -1         | 54.00 KB  | 32.00 MB      | default.mst_location d                                      |
| 19:AGGREGATE        | 1      | 9.01ms   | 9.01ms   | 10.04K  | 80.53M     | 6.38 MB   | 7.22 GB       | FINALIZE                                                    |
| 18:EXCHANGE         | 1      | 317.12us | 317.12us | 10.04K  | 80.53M     | 0 B       | 0 B           | HASH(ip_address,a.access_date,a.usr_id,a.cnt)               |
| 08:AGGREGATE        | 1      | 449.59ms | 449.59ms | 10.04K  | 80.53M     | 13.18 MB  | 7.22 GB       |                                                             |
| 07:HASH JOIN        | 1      | 1.01s    | 1.01s    | 1.39M   | 80.53M     | 7.75 MB   | 5.68 MB       | INNER JOIN, BROADCAST                                       |
| |--17:EXCHANGE      | 1      | 809.57us | 809.57us | 2.42K   | 80.53K     | 0 B       | 0 B           | BROADCAST                                                   |
| |  16:AGGREGATE     | 1      | 682.73ms | 682.73ms | 2.42K   | 80.53K     | 776.88 MB | 10.00 MB      | FINALIZE                                                    |
| |  15:EXCHANGE      | 1      | 25.28ms  | 25.28ms  | 413.62K | 80.53K     | 0 B       | 0 B           | HASH(access_date,a.usr_id,cnt)                              |
| |  06:AGGREGATE     | 1      | 543.43ms | 543.43ms | 413.62K | 80.53K     | 776.88 MB | 10.00 MB      |                                                             |
| |  14:AGGREGATE     | 1      | 335.86ms | 335.86ms | 416.05K | 80.53K     | 776.88 MB | 128.00 MB     | FINALIZE                                                    |
| |  13:EXCHANGE      | 1      | 25.23ms  | 25.23ms  | 416.05K | 80.53K     | 0 B       | 0 B           | HASH(a.ip_address,a.ip_location,access_date,b.usr_id,b.cnt) |
| |  05:AGGREGATE     | 1      | 1.45s    | 1.45s    | 416.05K | 80.53K     | 776.88 MB | 128.00 MB     |                                                             |
| |  04:HASH JOIN     | 1      | 246.01ms | 246.01ms | 1.63M   | 80.53K     | 21.29 MB  | 2.00 GB       | LEFT OUTER JOIN, BROADCAST                                  |
| |  |--12:EXCHANGE   | 1      | 10.73us  | 10.73us  | 11      | -1         | 0 B       | 0 B           | BROADCAST                                                   |
| |  |  03:SCAN HDFS  | 1      | 241.87ms | 241.87ms | 11      | -1         | 54.00 KB  | 32.00 MB      | default.mst_location A                                      |
| |  02:SCAN HDFS     | 1      | 27.54s   | 27.54s   | 1.63M   | 80.53K     | 156.00 MB | 480.00 MB     | default.logs_sc1 B                                          |
| 01:SCAN HDFS        | 1      | 13.59s   | 13.59s   | 20.41M  | 80.53M     | 82.05 MB  | 352.00 MB     | default.logs_sc1 a                                          |
+---------------------+--------+----------+----------+---------+------------+-----------+---------------+-------------------------------------------------------------+

Regards,

Nagendra Rai

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

ipandis — Wed, 26 Nov 2014 17:48:03 GMT

Hi Nagendra,

Can you please send me (ipandis@cloudera) and Dimitris (dtsirogiannis@cloudera) a direct email with the entire profile log (impala log.INFO) in both configurations, the one that fails when mem_limit < 14GB and the one that succeeds?

We would like to understand why this is happenning, especially whether there is data skew.

Also, can you please try to run compute stats again on the tables that are missing stats (e.g. mst_location), and send us the logs, if compute stats fails?

Regards,

-Ippokratis.

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Thu, 27 Nov 2014 20:01:51 GMT

Hi Ippokratis,

I have sent mail, please check.

Regards,

Nagendra Rai

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Thu, 18 Dec 2014 11:26:30 GMT

Still waiting for reply 😞

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

ipandis — Thu, 18 Dec 2014 18:24:19 GMT

Hi Nagendra,

I replied to you over email. We are trying to understand and fix the 'Memory Limit Exceeded' errors. Can you please disable the partitioned_hash_join and partitioned_aggregation, by restarting Impala with those options "--enable_partitioned_aggregation=false --enable_partitioned_hash_join=false" and let me know if the queries run successfully?

Regards,
-Ippokratis.

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

nagendrarai — Tue, 23 Dec 2014 14:09:18 GMT

Hi Ippokratis,

I have added "--enable_partitioned_aggregation=false --enable_partitioned_hash_join=false" in /etc/default/impala and restarted impala service, now my query is running flawless on server having 4G memory.

Thank you for your support.

Regards,

Nagendra Rai

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

SID2 — Mon, 28 Dec 2015 06:45:02 GMT

Re: Backend 6:Memory Limit Exceeded" in impala 2 (chd 5.2) but same query is working fine in im

Tim Armstrong — Mon, 28 Dec 2015 03:23:13 GMT

The advice in this thread is out of date: memory usage for joins and aggregations has been improved a lot in CDH5.5.

Your issue is something different since the query doesn't have a join or group by (aggregation) in it.

The first step to understand this better is to look at the impalad logs: there is usually some information in there about why the memory limit was exceeded and what operators were consuming memory.