PLANNEDCONCURRENCY and MAXCONCURRENCY in resource_pools

esangrame

Hi,

Please help me understand below two parameters in resource pool

From vertica pages.
PLANNEDCONCURRENCY: The preferred number of queries that execute concurrently in this resource pool, specified by the pool's PLANNEDCONCURRENCY parameter.

MAXCONCURRENCY : The maximum number of concurrent execution slots available to the resource pool, specified by the poolMAXCONCURRENCY parameter.

Let's say

PLANNEDCONCURRENCY = 10
MAXCONCURRENCY =20
User : vertica_user
pool : vertica_pool

Since PLANNEDCONCURRENCY is 10, does this mean total of 10 queries can run at any point of time from vertica_user on the whole cluster and at max 20 queries can run at any point of time.

skeswani

It means, 10 queries are expected to run. So each query get an initial batch of memory/10 when it starts up.

However the system will continue to accept query 11, 12... and so on.
The 21st query will block for at least one of the previous 20 to finish before it starts executing

Vertica_Curtis

skeswani is right, but be careful with PlannedConcurrency. It's used a query budgeting tool. So, it divides that into the memory for the pool and grants that as a budget. So, if the pool has 64Gb, and a PlannedConcurrency of 8, then each query will get a budget of 8Gb. That might be too much. So, the higher you make PC, the less you'll budget.

Also, if 8 queries show in (in the above example), the resource manager might very well start rejecting queries since it believes that there is no more RAM to allocate - which might or might not be true.

This query will help you determine what the optimal PlannedConcurrency value should be. It compares, on average, what is budgeted, versus what is used in the resource pools. Adjust accordingly.

select resource_pool, sum(counter_value), counter_name
from SCHEMA.execution_engine_profiles
join SCHEMA.users using (user_name)
where counter_name ilike 'memory%'
group by resource_pool, counter_name
order by 1,2 ;

esangrame

Thanks a lot !!!

[Deleted User]

You may find this blog helpful : https://www.vertica.com/blog/understanding-vertica-query-budgets/