What does MEMORY_
The resource pool is given 50% of available query memory on the leaf.
In a cluster of 4 aggregators, 10 leaves, 148 db partitions, and workload_
10K max connections per leaf for all aggregators = 10K/4 =2.
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.
Assuming queries are on average using 300 connections, about 4 queries can run at a time and rest will be queued.
INFORMATION_, the column
REASON_ 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.
The sum of a pool's CPU < 100% but > 95% .
This occurs because system_
Last modified: April 26, 2023