MEM_LIMIT Query Option

The MEM_LIMIT query option defines the maximum amount of memory a query can allocate on each node. The total memory that can be used by a query is the MEM_LIMIT times the number of nodes.

There are two levels of memory limit for Impala. The ‑‑mem_limit startup option sets an overall limit for the impalad process (which handles multiple queries concurrently). That process memory limit can be expressed either as a percentage of RAM available to the process such as ‑‑mem_limit=70% or as a fixed amount of memory, such as 100gb. The memory available to the process is based on the host’s physical memory and, since Impala 3.2, memory limits from Linux Control Groups. E.g. if an impalad process is running in a Docker container on a host with 100GB of memory, the memory available is 100GB or the Docker container’s memory limit, whichever is less.

The MEM_LIMIT query option, which you set through impala-shell or the SET statement in a JDBC or ODBC application, applies to each individual query. The MEM_LIMIT query option is usually expressed as a fixed size such as 10gb, and must always be less than the impalad memory limit.

If query processing approaches the specified memory limit on any node, either the per-query limit or the impalad limit, then the SQL operations will start to reduce their memory consumption, for example by writing the temporary data to disk (known as spilling to disk). The result is a query that completes successfully, rather than failing with an out-of-memory error. The tradeoff is decreased performance due to the extra disk I/O to write the temporary data and read it back in. The slowdown could potentially be significant. Thus, while this feature improves reliability, you should optimize your queries, system parameters, and hardware configuration to make this spilling a rare occurrence.

Type: numeric

Units: A numeric argument represents memory size in bytes; you can also use a suffix of m or mb for megabytes, or more commonly g or gb for gigabytes. If you specify a value with unrecognized formats, subsequent queries fail with an error.

Default: 0 (unlimited)

Usage notes:

The MEM_LIMIT setting is primarily useful for production workloads. Impala’s Admission Controller can be configured to automatically assign memory limits to queries and limit memory consumption of resource pools. See Concurrent Queries and Admission Control and Memory Limits and Admission Control for more information on configuring the resource usage through admission control.

Use the output of the SUMMARY command in impala-shell to get a report of memory used for each phase of your most heavyweight queries on each node, and then set a MEM_LIMIT somewhat higher than that. See Using the SUMMARY Report for Performance Tuning for usage information about the SUMMARY command.

Examples:

The following examples show how to set the MEM_LIMIT query option using a fixed number of bytes, or suffixes representing gigabytes or megabytes.

  2. [localhost:21000] > set mem_limit=3000000000;
  3. MEM_LIMIT set to 3000000000
  4. [localhost:21000] > select 5;
  5. Query: select 5
  6. +---+
  7. | 5 |
  8. +---+
  9. | 5 |
  10. +---+
  12. [localhost:21000] > set mem_limit=3g;
  13. MEM_LIMIT set to 3g
  14. [localhost:21000] > select 5;
  15. Query: select 5
  16. +---+
  17. | 5 |
  18. +---+
  19. | 5 |
  20. +---+
  22. [localhost:21000] > set mem_limit=3gb;
  23. MEM_LIMIT set to 3gb
  24. [localhost:21000] > select 5;
  25. +---+
  26. | 5 |
  27. +---+
  28. | 5 |
  29. +---+
  31. [localhost:21000] > set mem_limit=3m;
  32. MEM_LIMIT set to 3m
  33. [localhost:21000] > select 5;
  34. +---+
  35. | 5 |
  36. +---+
  37. | 5 |
  38. +---+
  39. [localhost:21000] > set mem_limit=3mb;
  40. MEM_LIMIT set to 3mb
  41. [localhost:21000] > select 5;
  42. +---+
  43. | 5 |
  44. +---+

The following examples show how unrecognized MEM_LIMIT values lead to errors for subsequent queries.

  2. [localhost:21000] > set mem_limit=3pb;
  3. MEM_LIMIT set to 3pb
  4. [localhost:21000] > select 5;
  5. ERROR: Failed to parse query memory limit from '3pb'.
  7. [localhost:21000] > set mem_limit=xyz;
  8. MEM_LIMIT set to xyz
  9. [localhost:21000] > select 5;
  10. Query: select 5
  11. ERROR: Failed to parse query memory limit from 'xyz'.

The following examples shows the automatic query cancellation when the MEM_LIMIT value is exceeded on any host involved in the Impala query. First it runs a successful query and checks the largest amount of memory used on any node for any stage of the query. Then it sets an artificially low MEM_LIMIT setting so that the same query cannot run.

  2. [localhost:21000] > select count(*) from customer;
  3. Query: select count(*) from customer
  4. +----------+
  5. | count(*) |
  6. +----------+
  7. | 150000   |
  8. +----------+
  10. [localhost:21000] > select count(distinct c_name) from customer;
  11. Query: select count(distinct c_name) from customer
  12. +------------------------+
  13. | count(distinct c_name) |
  14. +------------------------+
  15. | 150000                 |
  16. +------------------------+
  18. [localhost:21000] > summary;
  19. +--------------+--------+--------+----------+----------+---------+------------+----------+---------------+---------------+
  20. | Operator     | #Hosts | #Inst  | Avg Time | Max Time | #Rows   | Est. #Rows | Peak Mem | Est. Peak Mem | Detail        |
  21. +--------------+--------+--------+----------+----------+---------+------------+----------+---------------+---------------+
  22. | 06:AGGREGATE | 1      | 1      | 230.00ms | 230.00ms | 1       | 1          | 16.00 KB | -1 B          | FINALIZE      |
  23. | 05:EXCHANGE  | 1      | 1      | 43.44us  | 43.44us  | 1       | 1          | 0 B      | -1 B          | UNPARTITIONED |
  24. | 02:AGGREGATE | 1      | 1      | 227.14ms | 227.14ms | 1       | 1          | 12.00 KB | 10.00 MB      |               |
  25. | 04:AGGREGATE | 1      | 1      | 126.27ms | 126.27ms | 150.00K | 150.00K    | 15.17 MB | 10.00 MB      |               |
  26. | 03:EXCHANGE  | 1      | 1      | 44.07ms  | 44.07ms  | 150.00K | 150.00K    | 0 B      | 0 B           | HASH(c_name)  |
  27. | 01:AGGREGATE | 1      | 1      | 361.94ms | 361.94ms | 150.00K | 150.00K    | 23.04 MB | 10.00 MB      |               |
  28. | 00:SCAN HDFS | 1      | 1      | 43.64ms  | 43.64ms  | 150.00K | 150.00K    | 24.19 MB | 64.00 MB      | tpch.customer |
  29. +--------------+--------+--------+----------+----------+---------+------------+----------+---------------+---------------+
  31. [localhost:21000] > set mem_limit=15mb;
  32. MEM_LIMIT set to 15mb
  33. [localhost:21000] > select count(distinct c_name) from customer;
  34. Query: select count(distinct c_name) from customer
  35. ERROR:
  36. Rejected query from pool default-pool: minimum memory reservation is greater than memory available to the query
  37. for buffer reservations. Memory reservation needed given the current plan: 38.00 MB. Adjust either the mem_limit
  38. or the pool config (max-query-mem-limit, min-query-mem-limit) for the query to allow the query memory limit to be
  39. at least 70.00 MB. Note that changing the mem_limit may also change the plan. See the query profile for more
  40. information about the per-node memory requirements.

Parent topic: Query Options for the SET Statement