Impala query failed

[#1]

After running Impala query 

select distinct(partition_date) parts from mddb_servt;

I am getting below error:

Execution time 10 seconds

2) java.sql.SQLException: [Cloudera][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:ExecQueryFInstances rpc query_id=937d334667doe010:4967d4b900000111 failed: Failed to get minimum memory reservation of 68.00 MB on daemon ec2-3-128-13.us-east-2:22000 for query 937d334667doe010:4967d4b900000111 because it would exceed an applicable memory limit. Memory is likely oversubscribed. Reducing query concurrency or configuring admission control may help avoid this error. Memory usage:

Process: Limit=60.00 GB Total=50.68 GB Peak=50.70 GB

  Buffer Pool: Free Buffers: Total=0

  Buffer Pool: Clean Pages: Total=2.31 GB

  Buffer Pool: Unused Reservation: Total=-2.29 GB

  Free Disk IO Buffers: Total=1.10 GB Peak=1.15 GB

  RequestPool=root.default: Total=48.28 GB Peak=48.37 GB

    Query(78befceb1eef47:d33db5f200030000): Reservation=47.49 GB ReservationLimit=48.00 GB OtherMemory=293.93 MB Total=47.78 GB Peak=47.81 GB

    Query(e12345ed0a094d14:f4616fb90030000): Reservation=238.00 MB ReservationLimit=48.00 GB OtherMemory=4.27 MB Total=242.27 MB Peak=303.22 MB

    Query(be7896564af6f2c:1e675bb00000000): Reservation=272.00 MB ReservationLimit=48.00 GB OtherMemory=4.23 MB Total=276.23 MB Peak=314.22 MB

    Query(914d001522ce0e10:264bd4b900000000): Reservation=0 ReservationLimit=48.00 GB OtherMemory=0 Total=0 Peak=0

  RequestPool=root.anp: Total=0 Peak=536.50 MB

  Untracked Memory: Total=1.28 GB

Please note: Impala Daemon Memory Limit mem_limit = 60 GiB

Please let me know what could be the reason.

[#2]

This query is using up most of the memory on the impala daemon and there is not enough headroom to start your other query.

    Query(78befceb1eef47:d33db5f200030000): Reservation=47.49 GB ReservationLimit=48.00 GB OtherMemory=293.93 MB Total=47.78 GB Peak=47.81 GB

You can restrict memory usage of a query by setting the mem_limit option for that query.

If you want to do that globally for all queries in cluster, impala admission control can do that - https://docs.cloudera.com/documentation/enterprise/latest/topics/impala_admission.html

E.g. you could set up memory-based admission control with a min memory limit of 2GB and a max memory limit of 20GB to prevent any one query from taking up all the memory on a node.

[#3]

Hi Tim,

Thanks for your reply I can only see two parameters for the memory limit.

one is Single

Pool Mem Limit (default_pool_mem_limit) = -1 B

the second one is

Impala Daemon memory limit (mem_limt) = 60 GB

So how do I set now min memory limit and max memory limit.

[#4]

Hi Tim,

Can you explain in more detail how can I do this?

E.g. you could set up memory-based admission control with a min memory limit of 2GB and a max memory limit of 20GB to prevent anyone query from taking up all the memory on a node.

[#5 · Accepted solution]

The docs have a better and more complete explanation of Impala admission control than I could give in a reply here - https://docs.cloudera.com/documentation/enterprise/latest/topics/impala_admission.html.

There's also an example in the same section - https://docs.cloudera.com/documentation/enterprise/latest/topics/impala_rm_example.html

Min/max memory limits are only available in CDH6.1 and up.

if you don't want to or aren't able to fully implement Impala admission control, a partway solution to mitigate against a query using all the memory is to leave max memory unset (so that memory-based admission control is not enabled) and set the default query memory limit on the pool. That just limits the amount of memory any one query can use up.

[#6]

Hi Tim,

Your suggestion was very helpful. I have a good understanding now. I am accepting as a solution. I just have one more thing to ask, to fix the issue of the query utilizing the resources it is better to increase the Impala Daemon Memory Limit (mem_limit).

what do you suggest?