Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

Explorer

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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

Cloudera Employee

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.

19 REPLIES 19

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

Contributor

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

Explorer

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

 

 

Highlighted

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

Contributor

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

Explorer

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

Explorer

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

Explorer

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

Explorer

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.

 

***********
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

Contributor

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_expla... 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

Explorer

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