Execution time exceeded run time cap of

sergey_h

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?

Jim_Knicely

Hi,

What is the runtime cap of the secondary pool test_slow_pool?

sergey_h

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_Knicely

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_h

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_Knicely

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_h

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

result no rows

Jim_Knicely

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_h

post comment not work
working

sergey_h

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_h

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_Knicely

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_h

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_Knicely

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_h

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_Knicely

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_h

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