腾讯云数据仓库 TCHouse-P（ Tencent Cloud House-P，TCHouse-P ）为您提供简单、快速、经济高效的 PB 级云端数据仓库解决方案。云数据仓库兼容 PostgreSQL 开源数据仓库，是一种基于 MPP（大规模并行处理）架构的数仓服务。官方文档入口：

https://cloud.tencent.com/document/product/878

1背景介绍

本文主要介绍一个常用的统计信息视图（pg_stat_cluster_activity），该视图通过内部消息将所有节点的运行信息统一展示，并可以通过不同的过滤条件来定位相关的问题。每行显示一个服务器进程，同时详细描述与之关联的用户会话和查询，可以有效帮助用户分析排查当前运行的SQL任务以及异常问题。TCHouse作为分布式数据库，执行query需要多个CN和DN交互运行，对于复杂查询，甚至会出现多层调用的情况。该视图是一个很好用的工具，可以在运维过程中短时间内定位相关问题，例如死锁、程序挂起、节点报错等情况。适用于以下场景：

定位问题 Query 所在的 CN 节点
通过错误 ID 定位对应的 DN 节点
出现进程挂起的状况，定位相关的问题节点

2集群架构

数据仓库 TCHouse-P 的总体架构，包括上层的协调节点 CN、GTM 事务管理器、中间的数据交互总线 FN、以及下方的数据节点 DN。采用 MPP 架构，其特性是 share-nothing，数据分散在多个 DN 上，按照不同的分布键分布，并且不同的表可以自定义不同的分布键。如果 CN 收到了一条查询，它会将这个任务分散到多个 DN 上并行执行，从而提高执行效率，最后 CN 获得 DN 并行执行的最后结果，汇总之后再返回给客户端。

CN 节点：CN 节点不存储业务数据，只存储数据字典，负责生成、分发 SQL 执行计划到每个 DN 节点，同时负责与客户端的交互及权限认证。

DN 节点：DN 节点负责存储业务数据并执行由 CN 节点分发的 SQL 语句，同时为了保证每一个 DN 服务在同一个性能水平上，每一个 DN 节点机器有相同的资源配置，扩容时不作机型的改变。

3视图介绍

（1）全局视图 pg_stat_cluster_activity 由以下各列构成：

postgres=# \dv pg_stat_cluster_activity
                  List of relations
   Schema   |           Name           | Type | Owner 
------------+--------------------------+------+-------
 pg_catalog | pg_stat_cluster_activity | view | tbase


postgres=# \d pg_stat_cluster_activity
                              View "pg_catalog.pg_stat_cluster_activity"
      Column      |           Type           | Storage  | Description 
------------------+--------------------------+----------+-------------
 queryid          | text                     | extended | Query编号
 nodename         | text                     | extended | 节点名称
 datid            | oid                      | plain    | 数据库ID
 datname          | text                     | extended | 数据库名称
 pid              | integer                  | plain    | 后端进程的进程ID
 usesysid         | oid                      | plain    | 用户ID
 usename          | text                     | extended | 用户名
 application_name | text                     | extended | 应用名称
 client_addr      | inet                     | main     | 客户端IP地址
 client_hostname  | text                     | extended | 客户端主机名
 client_port      | integer                  | plain    | 客户端和后端通信的TCP端口号
 backend_start    | timestamp with time zone | plain    | 当前后端进程的开始时间
 xact_start       | timestamp with time zone | plain    | 当前事务的启动时间
 query_start      | timestamp with time zone | plain    | 当前活动查询开始执行的时间
 state_change     | timestamp with time zone | plain    | 后端的当前状态
 wait_event_type  | text                     | extended | 等待事件类型
 wait_event       | text                     | extended | 等待事件
 wait_event_info  | text                     | extended | 等待事件信息
 local_fid        | integer                  | plain    | 本地fid
 state            | text                     | extended | 后端的当前状态
 backend_xid      | xid                      | plain    | 后端进程当前的事务ID
 backend_xmin     | xid                      | plain    | 后端的xmin范围
 query            | text                     | extended | 最近查询的文本
 backend_type     | text                     | extended | 后端类型
 rsgname          | text                     | extended | 资源组名称

从事PostgreSQL数据库运维的朋友经常会使用到pg_stat_activity统计信息视图来监控数据库的活动和性能信息。全局视图的字段信息与这个视图基本一样，等待事件类型、等待事件和等待事件信息的具体信息可以查看官方文档：

https://www.postgresql.org/docs/current/monitoring-stats.html

（2）全局视图的视图定义

postgres=# \dv+ pg_stat_cluster_activity
                                       List of relations
   Schema   |           Name           | Type | Owner |  Size   | Allocated Size | Description 
------------+--------------------------+------+-------+---------+----------------+-------------
 pg_catalog | pg_stat_cluster_activity | view | tbase | 0 bytes | 0 bytes        | 
(1 row)


postgres=# \d+ pg_stat_cluster_activity
View definition:
 SELECT s.queryid,
    s.nodename,
    s.datid,
    s.datname,
    s.pid,
    s.usesysid,
    s.usename,
    s.application_name,
    s.client_addr,
    s.client_hostname,
    s.client_port,
    s.backend_start,
    s.xact_start,
    s.query_start,
    s.state_change,
    s.wait_event_type,
    s.wait_event,
    s.wait_event_info,
    s.local_fid,
    s.state,
    s.backend_xid,
    s.backend_xmin,
    s.query,
    s.backend_type,
    s.rsgname
   FROM pg_stat_get_cluster_activity(NULL::text, false) s(queryid, nodename, datid, datname, pid, usesysid, usename, appli
cation_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_t
ype, wait_event, wait_event_info, local_fid, state, backend_xid, backend_xmin, query, backend_type, rsgname);

4常见场景

（1）从全局角度查看连接信息，包括：连接数据库、连接用户、客户端地址和端口。

SELECT datname,usename,client_addr,client_port
FROM pg_stat_cluster_activity
where client_addr is not null;

（2）从全局角度查看当前用户执行的SQL信息

SELECT queryid, nodename, datname,pid,query
FROM pg_stat_cluster_activity
where query <> ''
order by queryid, nodename;

其中同一个 Query 在不同的 CN 以及 DN 上有相同的 queryid，其中列 query 可以表示 CN 或 DN 上正在执行的查询或者查询片段。

（3）查看当前运行中的耗时较长的SQL语句

select current_timestamp - query_start as runtime,
queryid, nodename, datname, state, query
from pg_stat_cluster_activity
where state != 'idle' and query <> ''
order by runtime desc;

其中 runtime 表示查询执行的时间，可以根据目前查询执行的时间进行排序，找出目前执行时间最长的查询。

（4）Query执行过程中挂起

Query在执行过程中挂起，定位出现问题的节点信息。利用前面的办法定位到挂起的Query的PID，然后利用下面的Query，把有问题Query的PID填到？处。

select queryid, nodename, datid AS datid, datname AS datname, pid,
wait_event_type, wait_event, wait_event_info, local_fid, state, query
from pg_stat_cluster_activity
where queryid = (select queryid
from pg_stat_cluster_activity
where pid= ? )

（5）其他查询语句

--查询transaction时间比较长的语句及运行时间
select pid,query,xact_start,now() - xact_start as duration from pg_stat_cluster_activity
 where state like '%transaction%'
 order by 3 desc;
  
--查询状态为 active 和 idle之间的连接进行比对
  select  datname,
         count(*) as open,
         count(*) filter (where state = 'active') as active,
         count(*) filter (where state = 'idle') as idle,
         count(*) filter (where state = 'idle in transaction') as idle_in_transaction
         from pg_stat_cluster_activity
         where backend_type='client backend'
         group by rollup(1);

PostgreSQL提供了两个函数来杀死慢查询

1.pg_cancel_backend ：终止查询并保持连接处于活动状态

2.pg_terminate_backend ：终止数据库连接，包括查询

5参考资料

https://www.postgresql.org/docs/current/monitoring-stats.html
https://blog.csdn.net/cloudbigdata/article/details/126775224
https://cloud.tencent.com/document/product/878/36781

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

分布式数据库TCHouse|推荐一个常用的监控数据库活动和性能的视图

1背景介绍

2集群架构

3视图介绍

4常见场景

5参考资料

评论