Execution time exceeded run time cap of

Hi,

What is the runtime cap of the secondary pool test_slow_pool?

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

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

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?

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

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

result no rows

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;

post comment not work
working

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]

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.

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 = ?;

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

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?

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.

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?

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