1.       背景介绍

GaussDB(DWS)提供了丰富的视图用于SQL信息和状态监控，协助用户SQL运维及问题分析。其中负载管理也提供了专门的视图用于SQL运行状态监控，通过视图排查SQL排队原因，及时给出优化措施。本文重点介绍了负载管理常用视图，同时结合其他视图给出了视图应用案例。

2.       视图简介

负载管理监控视图：PG_SESSION_WLMSTAT（负载管理常用视图）

状态监控视图：PGXC_STAT_ACTIVITY（负载管理辅助视图），PG_SESSION_WLMSTAT中不包含query_id、query_start及coorname信息，可以通过关联该视图查看

TopSQL实时监控视图：PGXC_WLM_SESSION_STATISTICS（负载管理辅助视图），包含query_band、资源监控等信息

TopSQL历史监控视图：PGXC_WLM_SESSION_INFO（负载管理辅助视图）

其中负载管理监控视图当前只有单CN视图，如需查看所有CN上查询负载管理信息可通过创建相应FUNCTION实现。参考FUNCTION如下：

CREATE OR REPLACE FUNCTION pg_catalog.pgxc_session_wlmstat()
RETURNS setof pg_session_wlmstat
AS $$
DECLARE
        row_name record;
        row_data pg_session_wlmstat%rowtype;
        query_str text;
        query_str_nodes text;
        BEGIN
                query_str_nodes := 'SELECT node_name FROM pgxc_node WHERE node_type IN (''C'')';
                FOR row_name IN EXECUTE(query_str_nodes) LOOP
                         query_str := 'EXECUTE DIRECT ON (' || row_name.node_name || ') ''SELECT * from pg_session_wlmstat''';
                          FOR row_data IN EXECUTE(query_str) LOOP
                                  RETURN NEXT row_data;
                          END LOOP;
                END LOOP;
                return;
        END; $$
LANGUAGE 'plpgsql' NOT FENCED;

3.       视图应用

创建资源池，设置快慢车道并发上限都是2，其余参数使用默认参数：

CREATE RESOURCE POOL rp1 WITH (ACTIVE_STATEMENTS=2,MAX_DOP=2);

创建用户，关联资源池rp1

CREATE USER usr1 PASSWORD 'password' RESOURCE POOL 'rp1';

使用usr1创建表并运行作业，快慢车道各运行10个作业(视图查询过程中可能有作业结束，因此排队作业数可能减少)，查询视图查询作业信息：

PGXC_STAT_ACTIVITY视图

postgres=# select coorname,usename,query_start,enqueue,state,query_id from pgxc_stat_activity where usename='usr1' order by query_start;
   coorname   | usename |          query_start          |         enqueue          | state  |     query_id
--------------+---------+-------------------------------+--------------------------+--------+-------------------
 coordinator1 | usr1    | 2021-06-11 09:59:02.822459+08 |                          | active | 75153818781745720
 coordinator1 | usr1    | 2021-06-11 09:59:02.823087+08 |                          | active | 75153818781745723
 coordinator1 | usr1    | 2021-06-11 09:59:02.8231+08   | waiting in respool queue | active | 75153818781745724
 coordinator1 | usr1    | 2021-06-11 09:59:02.823715+08 | waiting in respool queue | active | 75153818781745726
 coordinator1 | usr1    | 2021-06-11 09:59:02.823928+08 | waiting in respool queue | active | 75153818781745727
 coordinator1 | usr1    | 2021-06-11 09:59:02.825433+08 | waiting in respool queue | active | 75153818781745728
 coordinator1 | usr1    | 2021-06-11 09:59:02.825918+08 | waiting in respool queue | active | 75153818781745730
 coordinator1 | usr1    | 2021-06-11 09:59:02.828873+08 | waiting in respool queue | active | 75153818781745732
 coordinator1 | usr1    | 2021-06-11 09:59:02.870048+08 | waiting in respool queue | active | 75153818781745739
 coordinator1 | usr1    | 2021-06-11 09:59:02.870726+08 | waiting in respool queue | active | 75153818781745740
 coordinator1 | usr1    | 2021-06-11 09:59:04.532598+08 |                          | active | 75153818781745779
 coordinator1 | usr1    | 2021-06-11 09:59:04.53264+08  |                          | active | 75153818781745778
 coordinator1 | usr1    | 2021-06-11 09:59:04.533594+08 | waiting in ccn queue     | active | 75153818781745782
 coordinator1 | usr1    | 2021-06-11 09:59:04.533626+08 | waiting in ccn queue     | active | 75153818781745783
 coordinator1 | usr1    | 2021-06-11 09:59:04.53382+08  | waiting in ccn queue     | active | 75153818781745784
 coordinator1 | usr1    | 2021-06-11 09:59:04.533834+08 | waiting in ccn queue     | active | 75153818781745787
 coordinator1 | usr1    | 2021-06-11 09:59:04.534021+08 | waiting in ccn queue     | active | 75153818781745786
 coordinator1 | usr1    | 2021-06-11 09:59:04.534788+08 | waiting in ccn queue     | active | 75153818781745788
 coordinator1 | usr1    | 2021-06-11 09:59:04.53515+08  | waiting in ccn queue     | active | 75153818781745789
 coordinator1 | usr1    | 2021-06-11 09:59:04.536193+08 | waiting in ccn queue     | active | 75153818781745790

enqueue字段可能取值：

• waiting in queue：表示语句在排队中。（向前兼容取值）

• waiting in global queue：表示语句在CN并发队列排队中。

• waiting in respool queue：表示语句在资源池排队中。

• waiting in ccn queue：表示作业在CCN排队中。

• 空：表示语句不在任何队列中，正在运行。

state字段可能取值：(该字段代表session当前状态，不能说明语句在排队/运行中)

• active：后台正在执行查询。(该取值代表session有活跃作业，但是作业可能处于排队/实际运行中)

• idle：后台正在等待新的客户端命令。

• idle in transaction：后端在事务中，但事务中没有语句在执行。

• idle in transaction (aborted)：后端在事务中，但事务中有语句执行失败。

• fastpath function call：后端正在执行一个fast-path函数。

• disabled：如果后端禁用track_activities，则报告此状态。

从视图中可看出usr1所有session状态均处于active状态，代表所有session都在运行查询；通过enqueue字段可以看出有8个作业在CCN队列排队(复杂作业/慢车道)，8个作业在资源池队列排队(简单作业/快车道)，其余四个作业enqueue字段为空代表正在运行，其中包含两个快车道作业，两个慢车道作业，这点在后面PG_SESSION_WLMSTAT视图查询结果中得到验证。

PG_SESSION_WLMSTAT视图

postgres=# select usename,processid,threadid,priority,attribute,lane,enqueue,status,block_time,elapsed_time,statement_mem from pg_session_wlmstat where usename='usr1';
 usename | processid |    threadid     | priority |  attribute  | lane |   enqueue    | status  | block_time | elapsed_time | statement_mem
---------+-----------+-----------------+----------+-------------+------+--------------+---------+------------+--------------+---------------
 usr1    |     46919 | 140323231098624 |        2 | Simple      | fast | Respool      | pending |          3 |            0 |             1
 usr1    |     46920 | 140322958997248 |        2 | Simple      | fast | None         | running |          0 |            3 |             1
 usr1    |     46922 | 140323509499648 |        2 | Simple      | fast | None         | running |          0 |            3 |             1
 usr1    |     46921 | 140323556685568 |        2 | Simple      | fast | Respool      | pending |          3 |            0 |             1
 usr1    |     46923 | 140323492718336 |        2 | Simple      | fast | Respool      | pending |          3 |            0 |             1
 usr1    |     46924 | 140323416160000 |        2 | Simple      | fast | Respool      | pending |          3 |            0 |             1
 usr1    |     46925 | 140323396757248 |        2 | Simple      | fast | Respool      | pending |          3 |            0 |             1
 usr1    |     46926 | 140323347470080 |        2 | Simple      | fast | Respool      | pending |          3 |            0 |             1
 usr1    |     46927 | 140323330688768 |        2 | Simple      | fast | Respool      | pending |          3 |            0 |             1
 usr1    |     46928 | 140323313907456 |        2 | Simple      | fast | Respool      | pending |          3 |            0 |             1
 usr1    |     46952 | 140323293980416 |        2 | Complicated | slow | CentralQueue | pending |          2 |            0 |           256
 usr1    |     46953 | 140323277199104 |        2 | Complicated | slow | CentralQueue | pending |          2 |            0 |           256
 usr1    |     46954 | 140323254695680 |        2 | Complicated | slow | None         | running |          0 |            2 |           256
 usr1    |     46956 | 140323191777024 |        2 | Complicated | slow | CentralQueue | pending |          2 |            0 |           256
 usr1    |     46955 | 140323208558336 |        2 | Complicated | slow | CentralQueue | pending |          2 |            0 |           256
 usr1    |     46958 | 140323113662208 |        2 | Complicated | slow | None         | running |          0 |            2 |           256
 usr1    |     46959 | 140323083773696 |        2 | Complicated | slow | CentralQueue | pending |          2 |            0 |           256
 usr1    |     46960 | 140322866190080 |        2 | Complicated | slow | CentralQueue | pending |          2 |            0 |           256
 usr1    |     46957 | 140323172374272 |        2 | Complicated | slow | CentralQueue | pending |          2 |            0 |           256
 usr1    |     46961 | 140322830010112 |        2 | Complicated | slow | CentralQueue | pending |          2 |            0 |           256
(20 rows)

attribute显示为Simple代表简单作业，显示Complicated代表复杂作业；lane显示fast代表语句运行在快车道，显示slow代表运行在慢车道；enqueue字段为None表示作业不在排队，显示Respool表示在资源池排队，显示CentralQueue表示在CCN排队，该字段与PGXC_STAT_ACTIVITY视图中enqueue字段相对应；status为pending表示查询排队/默认状态，显示running表示查询正在运行。

备注：pending作业不一定在排队，有可能还没走到负载管理，做到走过负载管理后状态才更新为running，显示pending作业需要结合enqueue字段判断作业是否排队；pending作业enqueue为None表示作业未走到负载管理。

4.  总结

        本文简单介绍了负载管理常用视图，同时着重介绍了PGXC_STAT_ACTIVITY和PG_SESSION_WLMSTAT在实际应用中的差异，实际应用过程中可以根据实际需求将两个视图进行关联查询，为性能分析、负载管理提供帮助。

        想了解GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的PB级数仓黑科技~