Query runs from Console but not from Client - Memory issue ?

KMehta

I am new to vertica (7.2.x)  and working on POC. When we tried to run Query from vertica Console it comes immediately but when we run the same query from the client tools like SQLuirrel SQL Client Version 3.7 or Aquastudio it throws some memory exception after timeout.

 

We also tried to change resource pool , session settings but nothing is working. It looks like client doesn’t have enough memory to render output back to client screen so we tried to increase memory on client side but still its same issue. We are now clueless so your help will be appreciated. 

 

 

 

Error: [Vertica][VJDBC](3587) ERROR: Insufficient resources to execute plan on pool general [Timedout waiting for resource request: Request exceeds limits: Memory(KB) Exceeded: Requested = 1280241, Free = 25856 (Limit = 4476676, Used = 4450820) (queueing threshold)]
SQLState: 53000
ErrorCode: 3587

 

- KMehta

Rahul_Choudhary

Hi

 

ERROR Timeout waiting for resource requests is mainly due the number of queries already running on that system and utilizing all the available memory. When a new query issued and there are no sufficient resources to execute then it goes into wait queue waiting for memory.

 

As per the default setting queuetimeout is 300 seconds. So any query which is in wait queue waits for max 300seconds, If it acquires memory within 300seconds, then that query continues execution. If it fails to acquire the memory with in 300 seconds, then fails with the ERROR Timedout waiting for resources.

 

Therefore increasing the timeout means the query waiting long time before it timeout and more possibilities of acquiring memory to execute. 

 

Using below query to increase the queuetimeout value:

 

vsql => ALTER RESOURCE POOL GENERAL QUEUETIMEOUT 600;

 

Let me know if it helps

 

Thanks

Rahul Choudhary

KMehta

Thanks Rahul for the reply. We have already altered general pool and changed time out to 3000 but now SQL fails from client tool and gives same error. When we run same query from console it comes immediately. 

We tried to create custom pool and assigned user and session to it but it also gave same issue. We increased client memory as well and changed config files to support it but it seems nothing is working for us.

SruthiA

HI, 

 

You can also increase the plannedconcurrency of the resource pool and see how it goes.

  ALTER RESOURCE POOL GENERAL PLANNEDCONCURRENCY 24;

 

And also increase the queuetimeout parameter to 900 seconds.

 

Thank you,

Sruthi

KMehta

Sruthi , 

Thanks for your help . Is there any way to call you and discuss some issues related to vertica. We need to complete our DAR on vertica ASAP.

SruthiA

Hi,

 

 

   Please open a support case. I will be happy to take it.

 

 

Thank you,

 Sruthi