FAQs

What does MEMORY_PERCENTAGE=50% in a resource pool mean?

The resource pool is given 50% of available query memory on the leaf. Note, this available query memory varies over time. The amount of memory on a leaf node currently available for query execution can be calculated as (Total_server_memory)  - (memory allocated currently on this node for .indexes/tables/internal data structures etc).

In a workspace of 4 aggregators, 10 leaves, 148 db partitions, and workload_management_max_connection_per_leaf=10K. The queries are on average using 300 connections. How many queries can run at a time?

10K max connections per leaf for all aggregators = 10K/4 =2.5K max connections per aggregator.

Assuming equal distribution of queries across all aggregators, WM evenly distributes the quotas across all aggregators.

As per the Queuing Policy there are two levels of queues in WM, each allocated 50% of resources. So one queue has 2.5/2 = 1.25K connections quota. Hence on any aggregator, only that number of queries can run such that they use at most 1.25K connections.

Assuming queries are on average using 300 connections, about 4 queries can run at a time and rest will be queued.

Note: In INFORMATION_SCHEMA.MV_PROCESSLIST, the column REASON_FOR_QUEUEING shows which component is responsible for queuing a query.

Why are queries using so many connections on a leaf?

The formulae for connections used by a query is:

Number of leaf connections by a query = 1 + numBroadcasts * numLeaves + numReshuffles * numPartitions

where numBroadcasts and numReshuffles depend on the type of query and query plan.

You can get the number of broadcasts and reshuffles from

SELECT QUERY_TEXT, OPTIMIZER_NOTES FROM INFORMATION_SCHEMA.PLANCACHE;	

You can also get more information about query statistics from

SELECT QUERY_TEXT, AVERAGE_MEMORY_USE, QUEUED_TIME, OPTIMIZER_NOTES FROM INFORMATION_SCHEMA.PLANCACHE;	

Is queuing by WM indicating a problem with the system?

It may or may not be. It may just be that WM is trying to ensure that leaf resources are not exhausted by queueing queries. Exhaustion of leaf resources can cause other issues like queries failing as nodes might not be able to open new network connections or spawn new threads.

The sum of a pool's CPU < 100% but > 95% . What could be a reason for an error while pool creation or alteration?

This occurs because system_optimizer_pool uses 5% CPU implicitly.