Setting a Hard Limit on Concurrency for an Application
Scenario
For billing purposes, analyst Jane would like to impose a hard limit on concurrency for this application. How can she achieve this?
Solution
The simplest solution is to create a separate resource pool for the users of that application and set its MAXCONCURRENCY to the desired concurrency level. Any queries beyond MAXCONCURRENCY are queued.
Tip: OpenText recommends leaving PLANNEDCONCURRENCY to the default level so the queries get their maximum amount of resources. The system as a whole thus runs with the highest efficiency.
Example
In this example, there are four billing users associated with the billing pool. The objective is to set a hard limit on the resource pool so a maximum of three concurrent queries can be executed at one time. All other queries will queue and complete as resources are freed.
=> CREATE RESOURCE POOL billing_pool MAXCONCURRENCY 3 QUEUETIMEOUT 2; => CREATE USER bill1_user RESOURCE POOL billing_pool; => CREATE USER bill2_user RESOURCE POOL billing_pool; => CREATE USER bill3_user RESOURCE POOL billing_pool; => CREATE USER bill4_user RESOURCE POOL billing_pool; => \x Expanded display is on. => select maxconcurrency,queuetimeout from resource_pools where name = 'billing_pool'; maxconcurrency | queuetimeout ----------------+-------------- 3 | 2 (1 row) > SELECT reason, resource_type, rejection_count FROM RESOURCE_REJECTIONS WHERE pool_name = 'billing_pool' AND node_name ilike '%node0001'; reason | resource_type | rejection_count ---------------------------------------+---------------+----------------- Timedout waiting for resource request | Queries | 16 (1 row)
If queries are running and do not complete in the allotted time (default timeout setting is 5 minutes), the next query requested gets an error similar to the following:
ERROR: Insufficient resources to initiate plan on pool billing_pool [Timedout waiting for resource request: Request exceeds limits: Queries Exceeded: Requested = 1, Free = 0 (Limit = 3, Used = 3)]
The table below shows that there are three active queries on the billing pool.
=> SELECT pool_name, thread_count, open_file_handle_count, memory_inuse_kb FROM RESOURCE_ACQUISITIONS
WHERE pool_name = 'billing_pool';
pool_name | thread_count | open_file_handle_count | memory_inuse_kb
--------------+--------------+------------------------+-----------------
billing_pool | 4 | 5 | 132870
billing_pool | 4 | 5 | 132870
billing_pool | 4 | 5 | 132870
(3 rows)