resource pool

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';