为应用程序设置硬性并发限制

场景

为了便于开票，分析员 Jane 希望对此应用程序的并发性实施硬性限制。如何做到这一点？

解决方案

最简单的解决方案是为该应用程序的用户创建一个单独的资源池，并将其 MAXCONCURRENCY 设置为所需的并发级别。任何超过 MAXCONCURRENCY 的查询都会进入队列。

示例

在本示例中，存在四位与开票池相关联的开票用户。目的是对资源池设置硬性限制，使一次最多可以执行三个并发查询。所有其他查询将排队并在资源释放后完成。

=> 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)

如果查询正在运行并且没有在规定时间内完成（默认超时设置为 5 分钟），请求的下一查询将收到一条类似于以下内容的错误：

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)]

下表显示在开票池中存在三个活动查询。

=> 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)