监控资源池
您可以使用以下内容查找有关资源池的信息：
-
RESOURCE_POOL_STATUS 从资源池返回当前数据 — 例如，当前的内存使用情况、通过各种请求所要求和获取的资源以及队列的状态。
-
RESOURCE_ACQUISITIONS 显示了授予当前正在运行的查询的所有资源。
-
SHOW SESSION 与其他会话级别参数一起显示 当前会话的资源池。
您还可以使用管理控制台获取有关资源池使用情况的运行时数据。
查看资源池状态
以下示例在 RESOURCE_POOL_STATUS 中查询内存大小数据：
=> SELECT pool_name poolName,
node_name nodeName,
max_query_memory_size_kb maxQueryMemSizeKb,
max_memory_size_kb maxMemSizeKb,
memory_size_actual_kb memSizeActualKb
FROM resource_pool_status WHERE pool_name='ceo_pool';
poolName | nodeName | maxQueryMemSizeKb | maxMemSizeKb | memSizeActualKb
----------+------------------+-------------------+--------------+-----------------
ceo_pool | v_vmart_node0001 | 12179388 | 13532654 | 1843200
ceo_pool | v_vmart_node0002 | 12191191 | 13545768 | 1843200
ceo_pool | v_vmart_node0003 | 12191170 | 13545745 | 1843200
(3 rows)
查看通过查询获取的资源
以下示例显示了授予当前正在运行的查询的所有资源。这里显示的信息存储在 RESOURCE_ACQUISITIONS 系统表中。您可以看到查询执行使用了 GENERAL 池中 708504 KB 的内存。
=> SELECT pool_name, thread_count, open_file_handle_count, memory_inuse_kb,
queue_entry_timestamp, acquisition_timestamp
FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001';
-[ RECORD 1 ]----------+------------------------------
pool_name | sysquery
thread_count | 4
open_file_handle_count | 0
memory_inuse_kb | 4103
queue_entry_timestamp | 2013-12-05 07:07:08.815362-05
acquisition_timestamp | 2013-12-05 07:07:08.815367-05
-[ RECORD 2 ]----------+------------------------------
...
-[ RECORD 8 ]----------+------------------------------
pool_name | general
thread_count | 12
open_file_handle_count | 18
memory_inuse_kb | 708504
queue_entry_timestamp | 2013-12-04 12:55:38.566614-05
acquisition_timestamp | 2013-12-04 12:55:38.566623-05
-[ RECORD 9 ]----------+------------------------------
...
您可以确定查询运行之前在队列中的等待时间。为此，您应使用以下示例中所示的查询，获取 acquisition_timestamp 和 queue_entry_timestamp 之差：
=> SELECT pool_name, queue_entry_timestamp, acquisition_timestamp,
(acquisition_timestamp-queue_entry_timestamp) AS 'queue wait'
FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001';
-[ RECORD 1 ]---------+------------------------------
pool_name | sysquery
queue_entry_timestamp | 2013-12-05 07:07:08.815362-05
acquisition_timestamp | 2013-12-05 07:07:08.815367-05
queue wait | 00:00:00.000005
-[ RECORD 2 ]---------+------------------------------
pool_name | sysquery
queue_entry_timestamp | 2013-12-05 07:07:14.714412-05
acquisition_timestamp | 2013-12-05 07:07:14.714417-05
queue wait | 00:00:00.000005
-[ RECORD 3 ]---------+------------------------------
pool_name | sysquery
queue_entry_timestamp | 2013-12-05 07:09:57.238521-05
acquisition_timestamp | 2013-12-05 07:09:57.281708-05
queue wait | 00:00:00.043187
-[ RECORD 4 ]---------+------------------------------
...
查询用户定义的资源池
系统表 RESOURCE_POOLS 和 RESOURCE_POOL_STATUS 中的布尔列 IS_INTERNAL 允许您仅获取有关用户定义的资源池的数据。例如：
SELECT name, subcluster_oid, subcluster_name, memorysize, maxmemorysize, priority, maxconcurrency
dbadmin-> FROM V_CATALOG.RESOURCE_POOLS where is_internal ='f';
name | subcluster_oid | subcluster_name | memorysize | maxmemorysize | priority | maxconcurrency
--------------+-------------------+-----------------+------------+---------------+----------+----------------
load_pool | 72947297254957395 | default | 0% | | 10 |
ceo_pool | 63570532589529860 | c_subcluster | 250M | | 10 |
ad hoc_pool | 0 | | 200M | 200M | 0 |
billing_pool | 45579723408647896 | ar_subcluster | 0% | | 0 | 3
web_pool | 0 | analytics_1 | 25M | | 10 | 5
batch_pool | 47479274633682648 | default | 150M | 150M | 0 | 10
dept1_pool | 0 | | 0% | | 5 |
dept2_pool | 0 | | 0% | | 8 |
dashboard | 45035996273843504 | analytics_1 | 0% | | 0 |
(9 rows)