resource pool
Hi,
I am getting below error while executing a query in vertica.
Error: [Vertica][VJDBC](3587) ERROR: Insufficient resources to execute plan on pool sysquery [Timedout waiting for resource request:
General cannot supply required overflow resources: Memory(KB) Exceeded: Requested = 32290, Free = 0 (Limit = 25556576, Used = 25556576) (queueing threshold)]
I have done some steps to resolve this but that didn't help.
1.alter RESOURCE POOL general PLANNEDCONCURRENCY 16
2.alter RESOURCE POOL sysquery PLANNEDCONCURRENCY 10
3.alter RESOURCE POOL general maxmemorysize '20GB'
4.ALTER USER verticadba MEMORYCAP '10G';
Note : The system configuration is 32 GB RAM & 8 core.
Kindly suggest.
Regards,
Deb
0
Comments
Hi,
Can you check if all the tables used in the query has statistics?
-Regards,
Sruthi
Hi
In addition to this you can also increase the queuetimeout value of the sysquery pool to a higher value if this is oftening too often & see if it helps:
vsql=> ALTER RESOURCE POOL sysquery QUEUETIMEOUT 600;
Regards
Rahul
Hi Deb,
The issues seems to be with the sysquery pool. There is where all the query from system tables are executed.
You should add more memory to that pool.
One note, I DO NOT recommed you to change the general pool parameters. I see that you changed the Plannedconcurrency and Maxmemroysize. In addition, the changes in the general pool you will need to restart the cluster to take effect.
Changing the Planned Concurrency will affect the query budget. You can see the budget in :
select node_name, pool_name, query_budget_kb from resource_pool_status;
Normally the query budget is calculated as the memsize/plannedconcurrency ( if there is not memsize, vertica uses other variables but you can find that in the docs). So if you want to increase the budget decrease the planned concurrency, increase the memsize or both.
Hope this helps.
Eugenia
Hi Eugenia,
I found the main issue is in the sqls that are running in parallel and each taking 2 to 3 GB memory.
Now we are doing some tuning.
Still as per your suggestion I reverted the general pool settings .
sql >> select * from resource_pools;
You told to add more memory to add in the sysquery pool.
What are the criteria and limitations for adding memory in sysquery pool ? Means upto how much I can increase & decrease the value for memsize/plannedconcurrency?
sql >> select node_name, pool_name, query_budget_kb from resource_pool_status;
node_name pool_name query_budget_kb
v_pmdb_node0001 general 3194572
v_pmdb_node0001 sysquery 8192
@Sruthi : All the tables dont have statistics , then I executed analyze_statistics('table_name');
What else I can do ?
Regards,
Deb
Hi,
After executing analyze statistics, did you execute the query again? Are you facing same error again?
-Regards,
Sruthi
Yes Sruthi,
Same issue. Analyze_statistic doesnt help me here.
Any other suggestions ?
Hi,
Can you share the values of the following parameters
cat /sys/kernel/mm/redhat_transparent_hugepage/defrag
cat /sys/kernel/mm/redhat_transparent_hugepage/khugepaged/defrag
-Regards,
Sruthi
Hi,
What is exaclty the query that you want to execute? If it is running in the syspool, it should not be a query that can benefit from statistics.
Can you share the query and if you still get the error message?
Eugenia
Hi Sruthi,
We are getting the below message from the error_message system table. It shows as: "Insufficient resources to execute plan on pool sysquery [Timedout waiting for resource request: General cannot supply required overflow resources: Memory(KB) Exceeded: Requested = 74789, Free = 0 (Limit = 46244920, Used = 47117392) (queueing threshold)]"
Can you put some light into this?
Our is a 3 node cluster and 16 core processor with 96GB RAM.
Thanks!!
sysqyery is resource pool used for running system table queries.
Out of the box, sysquery has 64M of reserved memory and it borrows from general pool .
If sysquery resource pool does not have sufficient available memory because of other concurrent queries and memory in general pool is exhausted by other queries then your query on system table will wait and possibly timeout.
You can reserve 1G of memory for sysquery resource pool to avoid this issue.
Statement you may need to run: alter resource pool sysquery memorysize '1G';