vertica Insufficient resources to execute plan

Baki

When I execute my SQL , I get the error "Insufficient resources to execute plan on pool general [Request Too Large:Memory(KB) Exceeded: Requested = 1371544, Free = 847867 (Limit = 850091, Used = 2224)]". 

 

I have 3 nodes each with 3 GB of RAM. How can i restrict the query to use the avaibale memory and I am fineif the query takes longer time to execute.  

 

 

eli_revach

Hi
As the general statment
Vertica is a database that consumes large set of memory address space in order to provide fast querys response time , the idea is to minimize chance for on disk calaulcation (think about group by and sorting and etc)

Vertica production best practicrs for memory in server as around 4-8G per core .

 

On top of all this their is some ways to minimize memory allocation for querys using resource pools 

Thanks.

Baki

Thanks for your reply. Could you please let me know the ways to minimize memory allocation. I am ok if the query taken longer time to finish but doesn't want to get the error of insufficient memory.

eli_revach

Hi

The  idea is to  adjust the   PLANNEDCONCURRENCY parameter  of the of  the GENERAL  pool ,

Below is general guideline  that you can used  (I attach it from different thread on the forum )

 

 

The resource pool parameters of MEMORYSIZE and PLANNEDCONCURRENCY (CREATE RESOURCE POOL in the SQL Reference Manual) provide the options that let you tune the target memory allocated to queries. The query_budget_kb column in the V_MONITOR.RESOURCE_POOL_STATUS system table shows the target memory for queries executed on the associated pool. Normally, queries do not require any specific tuning, but if needed, the general formula for computing query_budget_kb is as follows:

- If MEMORYSIZE is set to 0, in which case the pool borrows all memory as needed from the GENERAL pool, the target amount of memory for the query is calculated using the Queueing Threshold of the GENERAL pool / PLANNEDCONCURRENCY.
- If the resource pool for the query has the MEMORYSIZE parameter set, and the pool is standalone (i.e. cannot borrow from General pool) then the target memory is to use the amount of memory in the Queueing Threshold of the pool / PLANNEDCONCURRENCY.
- Otherwise, if MEMORYSIZE is set but the pool is not standalone, the target memory is set to MEMORYSIZE / PLANNEDCONCURRENCY of the pool.
Therefore, by carefully tuning the MEMORYSIZE and PLANNEDCONCURRENCY parameters, it is possible to restrict the amount of memory used by a query to a desired size.

 

Thanks 

 

Baki

Thank you again.

 

After incresing the timeout parameter the query returns the result set in the console but the same query is giving insufficient memory if we run the query from client (we are using party tools Aqua Studio &

SQLuirrel SQL Client Version 3.7). Do you have suggestions.

---

eli_revach wrote:

Hi

The  idea is to  adjust the   PLANNEDCONCURRENCY parameter  of the of  the GENERAL  pool ,

Below is general guideline  that you can used  (I attach it from different thread on the forum )

 

 

The resource pool parameters of MEMORYSIZE and PLANNEDCONCURRENCY (CREATE RESOURCE POOL in the SQL Reference Manual) provide the options that let you tune the target memory allocated to queries. The query_budget_kb column in the V_MONITOR.RESOURCE_POOL_STATUS system table shows the target memory for queries executed on the associated pool. Normally, queries do not require any specific tuning, but if needed, the general formula for computing query_budget_kb is as follows:

- If MEMORYSIZE is set to 0, in which case the pool borrows all memory as needed from the GENERAL pool, the target amount of memory for the query is calculated using the Queueing Threshold of the GENERAL pool / PLANNEDCONCURRENCY.
- If the resource pool for the query has the MEMORYSIZE parameter set, and the pool is standalone (i.e. cannot borrow from General pool) then the target memory is to use the amount of memory in the Queueing Threshold of the pool / PLANNEDCONCURRENCY.
- Otherwise, if MEMORYSIZE is set but the pool is not standalone, the target memory is set to MEMORYSIZE / PLANNEDCONCURRENCY of the pool.
Therefore, by carefully tuning the MEMORYSIZE and PLANNEDCONCURRENCY parameters, it is possible to restrict the amount of memory used by a query to a desired size.

 

Thanks 

 

---

 

eli_revach

Hi ,

Yes , Timeout can help to  have less  concurrent running query’s   . I don’t see any   Vertica resource behaver relation with the client application .

Please provide the below input  :

• Do you connect with the same Vertica user for both tests ?
• What is your cluster setup , memory size and cores you have per machine ?
• What is the query you trying to run (send an example )
• What is your projection design ?  

Thanks