Execution time exceeded run time cap of

sergey_hsergey_h Registered User

Hi,
I have resource pool:

Name | test_pool
Internal | f
Memory Size | 4G
Max Mem Size |
Execution Parallelism | AUTO
Priority | 50
Runtime Priority | MEDIUM
Runtime Priority Threshold | 0
Queue Timeout | 00:05
Planned Concurrency | 4
Max Concurrency | 4
Runtime Cap | 00:00:02
Single Initiator | f
CASCADE TO | test_slow_pool

Sametimes I view error:
"Execution time exceeded run time cap of 00:00:02"
This pool use for one user, 99% query - without errors and normaly cascaded to slow pool.
Why does the error occur?

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Hi,

    What is the runtime cap of the secondary pool test_slow_pool?

  • sergey_hsergey_h Registered User
    edited October 2

    secondary pool without runtime cap

    **Name | test_slow_pool
    Internal | f
    Memory Size | 2G
    Max Mem Size | 6G
    Execution Parallelism | AUTO
    Priority | 50
    Runtime Priority | LOW
    Runtime Priority Threshold | 0
    Queue Timeout | 00:05
    Planned Concurrency | 2
    Max Concurrency | 4
    Runtime Cap |
    Single Initiator | f
    CASCADE TO |
    **

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Hi,

    How do you set the resource pool for the user? At the user level or session?

    select * from users where user_name = '????';

    Also, check the result_reason column in the dc_resource_pool_move data collector table for clues on why a query did not cascade successfully:

    select * from dc_resource_pool_move where user_name = '????' and result_reason not ilike '%successfully%';

  • sergey_hsergey_h Registered User

    I set on user level:

    user_id | 45035996607951920
    user_name | test
    is_super_user | f
    profile_name | default
    is_locked | f
    lock_time |
    resource_pool | test_pool
    memory_cap_kb | unlimited
    temp_space_cap_kb | unlimited
    run_time_cap | unlimited
    all_roles |
    default_roles |
    search_path | openx, v_catalog, v_monitor, v_internal

    I check dc_resource_pool, result_reason:
    "Target pool does not have sufficient resources. [Queries]"

    I do not understand what resource was not enough for the request?
    Can this be determined?

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Probably memory since you set the Max Mem Size to 6G. Although, you should see the error "ERROR 3587: Insufficient resources to execute plan on pool X",

    Check the resource_rejections table.

    select * from resource_rejections where pool_name in ('test_pool', 'test_slow_pool');

  • sergey_hsergey_h Registered User

    select * from resource_rejections where pool_name in ('test_pool', 'test_slow_pool');

    result no rows

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Hmm. Make sure that the queries are actually cascading:

    select time, user_name, source_pool_name, move_cause, source_cap, target_cap, success, result_reason from dc_resource_pool_move where target_pool_name = 'test_slow_pool' order by time desc limit 5;

  • sergey_hsergey_h Registered User
    edited October 3

    post comment not work :(
    working :smile:

  • sergey_hsergey_h Registered User

    last error

    time user_name source_pool_name move_cause source_cap target_cap success result_reason

    2017-10-03 06:30:18 test test_pool RunTimeCap Exceeded 2000000 (null) false Target pool does not have sufficient resources. [Queries]
    2017-10-03 06:30:18 test test_pool RunTimeCap Exceeded 2000000 (null) false Target pool does not have sufficient resources. [Queries]

  • sergey_hsergey_h Registered User

    actually cascading

    time user_name source_pool_name move_cause source_cap target_cap success result_reason

    2017-10-03 10:31:05 test test_pool RunTimeCap Exceeded 2000000 (null) true Statement successfully moved to target pool.
    2017-10-03 10:31:05 test test_pool RunTimeCap Exceeded 2000000 (null) true Statement successfully moved to target pool.

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Hi,

    If the target pool does not have sufficient resources on at-least one node then the query is retried/re-planned on the target pool. On retry, the query might be queued till resources become available on the pool.

    In the dc_resource_pool_move data collector table there is a transaction_id. Grab that for that last error @ time "2017-10-03 06:30:18". Use the transaction_id to query the dc_requests_retried table.

    select time, node_name, request, reason_for_retry from dc_requests_retried where transaction_id = ?;

  • sergey_hsergey_h Registered User

    Hi,

    Yes, I have row in table dc_requests_retried:
    reason_for_retry | Move Resource Pool failure: Execution time exceeded run time cap of 00:00:02

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Ok. The "Execution time exceeded run time cap of 00:00:02" msg is odd in that I think it should say "Target pool does not have sufficient resources". We might be capturing the wrong msg in dc_requests_retried.

    So the query retried. We need to figure out if your query succeeded on the retry.

    Can you take a look at the dc_requests_issued table?

    select * from dc_requests_issued where transaction_id = ?;

    Pay attention to the is_retry column. Is it True?

  • sergey_hsergey_h Registered User

    last error

    time user_name source_pool_name move_cause source_cap target_cap success result_reason


    2017-10-03 06:30:18 test test_pool RunTimeCap Exceeded 2000000 (null) false Target pool does not have sufficient resources. [Queries]
    2017-10-03 06:30:18 test test_pool RunTimeCap Exceeded 2000000 (null) false Target pool does not have sufficient resources. [Queries]
    2017-10-03 06:30:18 test test_pool RunTimeCap Exceeded 2000000 (null) false Target pool does not have sufficient resources. [Queries]
    2017-10-03 06:30:18 test test_pool RunTimeCap Exceeded 2000000 (null) false Target pool does not have sufficient resources. [Queries]
    2017-10-03 06:30:18 test test_pool RunTimeCap Exceeded 2000000 (null) false Target pool does not have sufficient resources. [Queries]

    actually cascading

    time user_name source_pool_name move_cause source_cap target_cap success result_reason


    2017-10-03 10:31:05 test test_pool RunTimeCap Exceeded 2000000 (null) true Statement successfully moved to target pool.
    2017-10-03 10:31:05 test test_pool RunTimeCap Exceeded 2000000 (null) true Statement successfully moved to target pool.
    2017-10-03 10:31:05 test test_pool RunTimeCap Exceeded 2000000 (null) true Statement successfully moved to target pool.
    2017-10-03 10:31:05 test test_pool RunTimeCap Exceeded 2000000 (null) true Statement successfully moved to target pool.
    2017-10-03 10:31:05 test test_pool RunTimeCap Exceeded 2000000 (null) true Statement successfully moved to target pool.

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Did you check the DC_REQUESTS_ISSUED table yet?

    select * from dc_requests_issued where transaction_id = ?;

    Pay attention to the is_retry column. Is it True?

  • sergey_hsergey_h Registered User

    Jim_Knicely , Thank you very much for your help!
    Yes, is_retry - True