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 ... View more

Still waiting for reply 😞 ... View more

Hi Ippokratis,   I have sent mail, please check.   Regards, Nagendra Rai ... View more

  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 ... View more

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 ... View more

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 ... View more

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 ... View more

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     ... View more

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 ... View more

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 ... View more