User defined resource pool

abhishek_1

I want to know resource pool memory allocation behaviour. Lets say I have three users A, Band C. I have created three user defined pools and allocated maxmemory 80% to each. Now what happens when users A and B are executing their task but C is idle. Does the memory allocated to C is reserved or it will be used by A and B if required. Is it a good approach to define separate pool for every users? If not, then how to overcome this? Suppose the query run by ETL sometimes fail with the error "Insufficient resource to execute on general pool". Next time they run, it gets executed. So I am bit surprised and confused.

Navin_C

Hi Abhishek,

Firstly, Vertica comes with prebuilt General Pool consuming most of the memory and resources depending on the hardware you are running on.
The maxmemory size of General Pool is 95% by default

According to your scenario,
A,B,C have three user defined resource pools each with 80% max memory.
So when you are allocating this max memory size and resource to your user defined resource pool the resources are borrowed from the General pool of Vertica

Now what happens when users A and B are executing their task but C is idle. Does the memory

allocated to C is reserved or it will be used by A and B

Say query by User A consumes 50% of 80% max memory size, then 30% remaining will still be with General pool and the User B is allowed to use 45 % of memory from General pool still,
If user B's query executes at same time and uses 30 % of memory then the general pool is still left with 15 % memory.
Now when C executes at same time and query needs more memory resource then the left over .i.e. 15 % then it needs to wait until the resources are released by other resource pool, only then it will start executing.

So everything is borrowed from General pool
.
 Is it a good approach to define separate pool for every users? If not, then how to overcome this?

Not really, in a big production system the approach followed is creating and allocating resource pools for different roles. Roles are assigned to different users categorized under different activities ( ETL / BI )

So if you have 10 ETL users create a Role ETL and assign a new resource pool for Role ETL.

Hope this helps.