在PostgreSQL上列出长期运行的和缓慢的查询的教程

在EdgeTier，我们使用PostgreSQL数据库来存储我们几乎所有的关系数据。PostgreSQL是开源的，速度快，有有趣的文本搜索功能，有不错的JSON解析功能，在亚马逊AWS上运行良好。然而，对于WatchTower（一种联络中心监控工具）这样的复杂应用，我们有时会发现自己处于SQL优化的状态--努力加快API响应、查询结果或应用进程。

PostgreSQL是一个免费和开源的高性能关系型数据库引擎。在撰写本文时，PostgreSQL已经有30多年的发展历史，第一个版本 在1996年发布。

对于主动查询，PostgreSQL有一个"统计收集器 " 子系统，可以整理关于表、服务器、查询、索引和连接活动的数据。数据库通过一些 "pg_stat "预定义视图和一些低级别的统计功能向高级用户公开信息。

此外，还有几个系统信息函数，可以提取当前正在进行的进程和查询的会话和系统信息。

我发现有用的关键表和函数是：

• pg_stat_activity。 一个表，每个服务器进程有一个条目，显示每个进程的运行查询的细节。
• pg_locks。 关于当前数据库中由开放事务持有的锁的信息，每个可锁对象有一行。
• pg_blocking_pids()。一个可以找到阻挡PostgreSQL服务器进程的会话的进程ID（PID）的函数。
• pg_cancel_backend()。通过向一个进程ID发送SIGINT来取消当前运行的查询的函数。
• pg_terminate_backend()。在数据库上完全终止一个后端进程（查询和通常的连接）（使用SIGTERM而不是SIGINT）。

在PostgreSQL上列出长期运行的和缓慢的查询

如果PostgreSQL中的查询写得不好，或者没有利用连接列和分组列上的各种索引，就会运行缓慢。pg_stat_activity视图允许你看到所有正在运行的查询，以及它们在你的PostgreSQL数据库中花费的时间。照片：Veri IvanovaonUnsplash

写得不好的查询或结构不好的数据会导致你的数据库执行时间非常长。通常是通过缓慢的响应或数据库CPU的长期增长来发现的，这个 [pg_stat_activity](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW)视图可以帮助找出导致问题的查询。pg_stat_activity视图包含所有当前运行的查询的细节，包括用户、连接和时间细节。

一个简单的select * from pg_stat_activity ，将提供你的PostgreSQL数据库正在发生的事情的快照，每个当前事务有一行，以及关键列。

• datname：查询所运行的数据库名称。
• pid。运行中的查询的后台的进程ID。PID可以与pg_terminate_backend()或pg_cancel_backend()单独使用。
• usename：正在运行/连接到该后端的用户名称
• client_addr：连接到该后端的IP地址。如果为空/无，则该行可能指的是一个内部连接或内部进程。
• backend_start:后台进程开始的时间，或者客户端连接的时间。
• query_start:当前活动的查询开始的时间。
• 状态。后台的当前状态，有选项。
  • active：当前正在执行查询列中的查询。
  • idle：没有执行任何东西，在等待新的命令。
  • idle in transaction：后端在一个事务中，但当前没有做任何工作。这与 "idle in transaction (error) "相同，只是其中一个语句有错误。
  • fastpath函数调用。正在执行一个fast-path函数。
  • disabled: track_activities在这个后端已经被禁用。
• wait_event&wait_event_type:后台正在等待的事件类型。这可能是一个广泛的列表。注意这里有数据库对象的锁。
• query。后台最近的或当前活动的查询文本。根据文档，它被截断为1024字节，可通过服务器配置中的 "track_activity_query_size"改变。
• backend_type。有几种不同的类型，但对于大多数来自外部客户的连接，这将是 "客户后端"。

查找运行时间超过5分钟的查询

一个有用的补充是显示所有运行时间超过5分钟的查询，以便于调试。

SELECT
  pid,
  user,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS query_time,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

杀死长期运行的PostgreSQL查询进程

如果一些查询看起来不会完成，你可以使用pg_stat_activity或pg_locks视图中的pid （进程ID）来终止运行中的进程。

• pg_cancel_backend(pid) 将尝试优雅地杀死一个正在运行的查询进程。
• pg_terminate_backend(pid) 将立即杀死正在运行的查询进程，但可能会对在数据库服务器上运行的其他查询产生副作用。当运行pg_terminate_backend时，完整的连接可能被重置，所以其他正在运行的查询可能会受到影响。作为最后手段使用。

寻找阻塞的进程和阻塞的查询

pg_blocking_pids() 函数是一个有用的捷径，可以找到阻断另一个会话的数据库连接/会话。pg_blocking_pids()函数返回一个*postgreSQL的PID数组*，这些PID正在阻塞你提供给查询的指定服务器进程PID。通常情况下，如果一个服务器进程持有第二个进程需要的锁，它就会阻塞另一个进程。

一个有用的函数（来自 "如何检测Postgres中持有锁的查询？"Stackoverflow答案）来显示数据库中被阻塞的进程，以及阻塞它们的实际查询。

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

查看表的锁 - pg_lock

pg_lock视图为PostgreSQL数据库的每个活动锁提供了一行，你可以查看被锁定的对象，以及什么在持有或等待该锁。(Photo byJohn SalvinoonUnsplash)

pg_lock 视图提供了数据库中活动进程所持有的锁的信息，每个可锁对象（可以是表、页、交易ID和其他数据库对象）都有一个条目。锁是作为事务的一部分被获取的，通常用于表或数据库对象中的数据在整个事务完成之前不被改变的情况。

一个简单的select * from pg_locks ，将立即为你提供（关键列）。

• 锁类型。被锁定的对象的类型--（见这里）关系、页面、对象、tranasctionid、用户锁等。
• 关系。锁所针对的关系的OID（对象ID）。连接到pg_class OID，以获得这里的表的名称。
• page：关系中锁所针对的页数。
• transactionidxid。锁所针对的交易的ID。
• pid。持有/等待这个锁的服务器进程的进程ID。将这一列连接到pg_stat_activity ，可以提供上述所有关于查询/用户/时间等信息。
• granted:如果锁被持有，则为真，如果进程正在等待，则为假。

查看带有表名和查询的锁

将pg_lock 视图与pg_stat_activity 查询和pg_class 表名相连接，可以收集更多关于在任何时间点在数据库上锁定的信息（更多信息请参见 StackOverflow 上的"PostgreSQL 查找包括表名的锁"）。

select 
    relname as relation_name, 
    query, 
    pg_locks.* 
from pg_locks
join pg_class on pg_locks.relation = pg_class.oid
join pg_stat_activity on pg_locks.pid = pg_stat_activity.pid