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

Comments

  • SruthiASruthiA Vertica Employee Administrator

    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;

     

    namememorysizemaxmemorysizeexecutionparallelismpriorityruntimepriorityruntimeprioritythresholdqueuetimeoutplannedconcurrencymaxconcurrency
    general Special: 95%AUTO0MEDIUM2300AUTO<null>
    sysquery64M AUTO110HIGH0300AUTO<null>
    sysdata100M10%<null><null><null><null><null><null><null>
    wosdata0%25%<null><null><null><null><null>AUTO<null>
    tm200M AUTO105MEDIUM60300AUTO3
    refresh0% AUTO-10MEDIUM60300AUTO<null>
    recovery0% AUTO107MEDIUM60300AUTO3
    dbd0% AUTO0MEDIUM00AUTO<null>
    jvm0%10%AUTO0MEDIUM2300AUTO<null>

     

    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

     

  • SruthiASruthiA Vertica Employee Administrator

    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 ?

  • SruthiASruthiA Vertica Employee Administrator

    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!!

  • [Deleted User][Deleted User] Administrator
    edited June 2019

    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.

  • [Deleted User][Deleted User] Administrator

    Statement you may need to run: alter resource pool sysquery memorysize '1G';

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file