Product Documentation

Appearance

All platforms

Allocating Query Memory ​

The total memory available on each active worker node of the cluster is divided among active WLM resource pools and a flexible number of query slots within those pools. WLM rules influence how queries are assigned to resource pools and may prohibit or change query execution based on memory consumption.

Each new resource pool that you associate with a profile may cause an adjustment of the memory allocation among all of the pools associated with that profile. The same kind of adjustment may occur when you change an existing resource pool. Within a single resource pool, you can allocate (or restrict) memory in three different ways:

  • Request a fixed size (specify an exact amount, in MB)
  • Request a percentage
  • Set it to Unrestricted, which means the pool will accrue remaining memory when the fixed and percentage-based pools have taken their quota

Memory specifications are subject to the following WLM policies, which are applied in order:

  1. An initial minimum amount of memory is allocated to all pools.
  2. Memory is allocated to pools requesting fixed amounts.
  3. Remaining memory, if any, is allocated proportionally to pools requesting a percentage. (A percentage is calculated from the total memory available, not the memory minus fixed allocations.)
  4. Remaining memory, if any, is allocated equally to pools that were defined as Unrestricted.
  5. Remaining memory, if any, is allocated proportionally to pools requesting a percentage (repeat of step 3 where applicable).

Whether there is any remaining memory to use in steps 3, 4, and 5 depends on the pools that are currently defined and how they are defined (fixed, percentage-based, or unrestricted).

Memory allocation further depends on the minimum and maximum concurrency defined for the resource pool and the amount of memory on each worker node. For example, assume you have a small 2-node cluster with 58GB per node of available memory on each node. A resource pool with a maximum of 5 slots and a 25% memory allocation, assuming 25% is available, will accrue roughly 14.5GB per node and 2.9GB per slot.

Note: As a best practice, avoid allocating memory such that any query may be allocated less than 1GB. Memory requirements increase when spilling is enabled (see the discussion of spilling later in this section). A minimum of 512MB is enforced for resource pools with spilling disabled, and a minimum of 1GB for pools with spilling enabled.

When a query is planned, three useful pieces of memory information are calculated:

  • The minimum required amount of memory needed to set up the query (based on the query plan and calculated before any rows are actually processed into or out of memory)
  • The estimated total amount of memory to run the query (which is the minimum value plus some additional amount of memory, based on estimates of the data that will flow through the plan)
  • A confidence value, expressed as None (no confidence), Low, High, or Unknown. These values describe the degree to which the estimated memory is thought to be reliable.

Given the availability of this information, follow these basic guidelines when you are writing WLM rules that assign queries to resource pools and slots:

  • Never map a query to an execution slot whose size is less than a query's required memory
  • When using memory estimates for a query to match it to an execution slot, make sure the memory estimate confidence value is high
  • If the estimated memory for a query is low and the confidence value is high, choose a pool designated for queries that are expected to run fast and consume low memory.

For example, the following rule directs queries to a smallquery pool when the memory usage estimate is low (<1024) and the confidence in that estimate is High:

if (w.memoryEstimate < 1024 && w.memoryEstimateConfidence == 'High') 
{ w.resourcePool = 'smallquery'; }

This kind of rule is reliable for very simple queries. Memory consumption estimates are less predictable for more complex queries.

Queries That Spill Data to Disk ​

Some long-running, complex queries may run out of memory and fail to run. To reduce the frequency of out-of-memory conditions, some query steps are capable of "spilling," which means that data is written to disk during processing, then read back into memory to complete query execution.

Spilling is supported for queries that contain only these steps or a combination of these steps:

  • Joins
  • Aggregates
  • Sorts
  • UNION ALL

Spilling is not supported for queries that contain:

  • Cross-joins
  • The following set operations: UNION, EXCEPT, EXCEPT ALL, INTERSECT, INTERSECT ALL
  • Window functions
  • A WHERE clause condition of the following form:
WHERE column [ NOT ] IN (subquery) OR column [ NOT ] IN (subquery)

Queries for which spilling is possible effectively have an amplified slot size: the amount of memory available to them appears larger than the memory that is allocated. The memory required by a query may not prevent it from running in a given pool that does not have that much memory available. In this way, spilling prevents or reduces the frequency of out-of-memory conditions.

Every time a query operation spills some data to disk, some metadata has to be saved in memory to keep track of the spilled data and its location. Assume you have a memory allocation of 3GB for a resource pool with 1 slot, and a required memory of 1GB for an incoming query. This means that 2GB of memory is left for use during execution, as rows are processed. In general, Yellowbrick queries can spill up to 0.5TB of data per 1GB of memory. In this case, if needed, the query could safely spill up to 1TB of data without running out of memory.

Spilling may also be prohibited by a simple lack of temporary space. To make sure that complex long-running queries have the capacity to spill, set the temporary disk usage appropriately for your resource pools. If queries spill but run out of spill space to use, you may see "spill limit exceeded" errors.

You can allocate temporary space to specific resource pools by choosing one of the following options:

  • Any: by default, queries assigned to a pool use any spill space that is available.
  • Disabled: Queries cannot use any spill space.
  • Fixed percentage: A minimum reservation of 1 to 100% for the pool. For example, if a pool specifies 50%, that percentage of space will always be reserved for that pool, but queries going to that pool could use more. (In effect, this means that the total spill space for all other pools combined may not exceed 50%.)

Resource pools that do not specify a reserved spill percentage contend for all of the remaining spill space, not a proportional fraction. Remaining spill space is not divided evenly among the slots for those pools.

Note: The memory configuration is adjusted as needed to ensure a slot size of at least 1GB when spilling is enabled. Slots in a configuration with spilling disabled require a minimum slot size of 512MB.

Optimizing Queries to Reduce Spilling ​

Queries that spill may need to be optimized. Spilling introduces overhead that causes queries to run slower. Compared to when they run fully in memory, queries that spill may run 1.5x to 4x slower. Also, reducing spilling prolongs the lifetime of the flash storage media on the cluster.

The simplest way to reduce spilling is to allocate more memory to the resource pools where queries that spill are running. To identify queries that are spilling, go to Query Activity > Query Details in Yellowbrick Manager.

You can see where queries spill (and how much data they spill) because the plan nodes are marked with a warning. You can also use the EXPLAIN ANALYZE command or query the sys.log_query view, which has some spilling-related columns:

  • io_spill_write_bytes: data written to temporary space because of spilling.
  • io_spill_read_bytes: data read back from the spill area. This value could be less than what was originally written/spilled: for example, if there is a LIMIT clause on the query. (However, if it routinely occurs that io_spill_read_bytes is less than io_spill_write_bytes, you may need to rewrite the query or redesign a table in some way to avoid this "waste" of spill space.)
  • io_spill_space_bytes: maximum temporary space used at any given time on any worker.