Facing issue on Impala while running a job

[#1]

Facing the below issue on Impala -

Failed to get minimum memory reservation of 429.94 MB on daemon hydwtbdn1.icicisecurities.corp:22000 for query b44fc02d59517c5b:40ea915f00000000 due to following error: Failed to increase reservation by 429.94 MB because it would exceed the applicable reservation limit for the "Process" ReservationTracker: reservation_limit=255.00 GB reservation=255.00 GB used_reservation=0 child_reservations=255.00 GB The top 5 queries that allocated memory under this tracker are: Query(2e4e69d2f55f0014:fe08e92f00000000): Reservation=153.90 GB ReservationLimit=240.00 GB OtherMemory=1.87 GB Total=155.77 GB Peak=155.85 GB Query(7b478b1891d4039d:8bb6141800000000): Reservation=101.08 GB ReservationLimit=240.00 GB OtherMemory=237.92 MB Total=101.31 GB Peak=101.32 GB Query(e5471eefc1b23d4c:36badde100000000): Reservation=0 ReservationLimit=240.00 GB OtherMemory=6.33 MB Total=6.33 MB Peak=925.69 MB Query(6046dfb9cc825b97:45bd0eef00000000): Reservation=0 ReservationLimit=240.00 GB OtherMemory=2.77 MB Total=2.77 MB Peak=1.66 GB Query(d04b18aee1a7fc1f:e41983ca00000000): Reservation=0 ReservationLimit=240.00 GB OtherMemory=664.13 KB Total=664.13 KB Peak=850.57 MB Memory is likely oversubscribed. Reducing query concurrency or configuring admission control may help avoid this error.

Could someone help in this please.

[#2]

The key part of error is this:

Failed to increase reservation by 429.94 MB because it would exceed the applicable reservation limit for the "Process" ReservationTracker: reservation_limit=255.00 GB reservation=255.00 GB

The Impala Daemon hydwtbdn1.icicisecurities.corp does not have enough process memory to reserve for the new query.

Basically too many queries are reserving too much memory concurrently at the same time. So this new query coming in could not run as not enough memory was available. Either reduce concurrency of queries to be able to run the query. Or set memory limits for queries so that each individual query reserves less memory.

https://docs.cloudera.com/runtime/7.3.1/impala-manage/topics/impala-admission.html

Or you can give Impala more process mem_limit in Configurations.

[#3]

@Scarface 

This error occurs because your Impala daemon has hit its global "Process" reservation limit of 255.00 GB. Two massive queries are monopolising all available memory reservations, preventing new queries from securing even a minimal 430 MB allocation.

Root Cause

• Culprits: Two running queries have locked up 100% of the daemon's reservation capacity:
  • 2e4e69d2f55f0014:fe08e92f00000000 is holding 153.90 GB.
  • 7b478b1891d4039d:8bb6141800000000 is holding 101.08 GB.
• Total Reserved: 153.90 GB + 101.08 GB = 254.98 GB (effectively the entire 255 GB limit).
• Impact: Your new query (b44fc02d59517c5b:...) is denied execution because child_reservations from the existing queries equal the reservation_limit.

Immediate Fixes

• Kill Dominant Queries: Terminate one or both of the top two memory-consuming queries to instantly free up reservation pool space. To kill queries, navigate to Cloudera Manager > Impala > Queries.
• Retry with Lower Limits: Force your failed query to use less memory by setting strict memory operational limits before execution.
  sql

  SET MEM_LIMIT = 2gb;
  SET BUFFER_POOL_LIMIT = 1gb;
  -- Re-run your query here
  

Long-Term Solutions"

1. Implement Impala Admission Control

• Prevent large queries from running concurrently and destroying cluster stability.
• Configure Resource Pools in Cloudera Manager with explicit memory caps.
• Set a hard limit on Maximum Running Queries for memory-heavy pools.
• Enable Queueing so massive queries wait for prior queries to finish instead of crashing the daemon.
• Refer documentation: Admission Control and Query Queuing

2. Optimize the Heavy Queries

• Compute Stats: Ensure COMPUTE STATS has run on all tables involved in the queries so the planner allocates memory accurately. Refer documentation: COMPUTE STATS statement 
• Fix Joins: Check the explain plans of the heavy queries; change large BROADCAST joins to HASH JOIN (shuffle) to distribute memory load across multiple nodes. Refer documentation: EXPLAIN statement and Understanding Performance using EXPLAIN Plan