Examples of Flex Pools

Queries admitted to a flex pool have their memory allocated dynamically, based on minimum and maximum concurrency boundaries and the total memory available to the pool. A resource pool with concurrency set to 8/4 will divide memory among a minimum of 4 concurrent queries when its workload is light, but the same resource pool will start up to 8 queries concurrently when the workload is heavy, adjusting memory allocation accordingly. By default, any query in this pool will be limited to a maximum of ¼ of the available memory, making room for three more queries to run with the same amount of memory. On the other hand, if 8 queries join this pool at once and no other queries are running in it, they will each accrue 1/8 of the available memory (and no more).

Memory that is allocated to queries when they start running does not change during execution; therefore, queries that join the pool while other queries are still running must divide up the remaining memory. If insufficient memory is available, new queries cannot start and must be queued (or possibly moved). (Insufficient memory in this context is the amount of memory available divided by some number between the minimum and maximum concurrency values.)

See CREATE WLM RESOURCE POOL for examples of the syntax for flex pools. Alternatively, you can create resource pools in the SMC.

Example with concurrency of 8/4, 80GB memory

For example, say the system has 80GB available for queries in a given pool defined with concurrency of 8/4:
  • Query 1 comes in and takes 20GB of available memory (¼).
  • While query 1 is running, queries 2 and 3 come in. Each one also takes 20GB of available memory (¼). 20GB of memory remains.
  • While queries 1, 2, and 3 are running, queries 4, 5, and 6 come in. Only two of these can start, and they will take the remaining 10GB each (1/8). (In order to accommodate the maximum number of queries, the pool now uses 8 as the divisor instead of 4.)

    Query 6 will wait in the queue until the minimum required memory per query (1/8, or 10GB) becomes available in the pool. Under a high concurrent workload, most queries in this flex pool would be given only 1/8 of the memory.

Example with concurrency of 8/1, 80GB memory

This example is similar to the first example, except that the entire resource pool is available to a single query when there is no queue. This kind of pool is more appropriate for a sequential reporting workload (but not so useful for a mixed workload). User-defined throttling would help allow a query mix to continue to run, except that any long-running query would starve new queries, whether throttling is used or not.

In this case, the system has 80GB available for queries in a pool defined with concurrency of 8/1:
  • Query 1 comes in and takes all 80GB of available memory.
  • While query 1 is running, queries 2 and 3 come in. They wait in the queue.
  • While query 1 is running, and queries 2 and 3 are waiting, queries 4, 5, and 6 come in. All five queries wait in the queue.
  • Query 1 completes with five queries queued. They all start running with 16GB of memory each.

Example with concurrency of 32/8, 80GB memory

In this high-concurrency example, the system has 80GB available for queries in a pool defined with concurrency of 32/8:
  • Query 1 comes in and takes 10GB of available memory.
  • While query 1 is running, queries 2 and 3 come in. They also take 10GB each. 50GB remains in the pool.
  • While queries 1, 2, and 3 are running, queries 4, 5, and 6 come in. They also take 10GB each. 20GB remains in the pool.
  • Queries 1, 2, and 3 complete. 50GB remains in the pool.
  • Ten more queries come in. They all start with 5GB each.

This pool will always allow up to 8 concurrent queries at 10GB per query, but can subdivide resources to supply up to 32 concurrent queries. At the highest concurrency levels for this pool, 1/32 of the resources is given to each query (approximately 2.5GB).

Default Flex Profile

On a system under heavy concurrency with the flex profile activated, each query that runs in the mix pool automatically gets 1/24 of the resources (max/min concurrency = 24/4).



In the example shown here, memory per query/per worker typically flexes between 1.6GB and 9.5GB (1/24 of the available memory up to ¼ of the available memory).

Although minimum and maximum concurrency settings are automatically applied by the system, queries are not necessarily restricted by the minimum setting. In this 24/4 example, four slots' worth of resources do not need to be kept available all the time. When queries complete and slots become available in the pool (busy slots cannot be hijacked when queries are mid-stream), a rule may accrue any specified percentage (or number of MB) of the entire pool's memory allocation. For example, a rule may request 100% of the memory in the mix pool, wait until no queries are running in the pool, take all of the memory, and block all other queries from starting in the pool. Alternatively, a query may request 50% of the pool's memory, wait until 12 slots are open, take half the memory, and allow up to 12 more queries to start in the pool.

The following rule requests 50% of the memory when user bobr runs ybload operations in the mix pool:
if ((String(w.application).indexOf('ybload') >= 0) &&
    w.user === 'bobr' &&
    w.resourcePool === 'mix') {
	w.requestedMemoryPercent = 50;
}