[译] 如何在 PostgreSQL 上查找和停止正在运行的查询

原创 tinge 2022-06-26

4724

原文地址：How to Find and Stop Running Queries on PostgreSQL
原文作者：Adam Johnson

您的 PostgreSQL 服务器正在阻塞，一些顽皮的查询消耗了太多资源或阻塞了其他查询。不要恐慌！您可以停止这些问题查询并稳定您的系统。

在这篇文章中，我们将介绍通过 SQL 停止查询、查找有问题的查询的技术，以及通过操作系统工具取消偶尔有用的功能。

分两步通过 SQL 停止查询

这是查找和停止查询的基本过程。请注意，您需要以具有足够权限的用户身份进行连接，例如管理员帐户。

1.找到pid

PostgreSQL 为每个连接创建一个进程，并使用其操作系统进程 ID 或pid标识每个进程。为了取消查询，您需要知道它正在运行的连接的 pid。

找出这一点的一种方法是使用pg_stat_activityview，它提供有关实时查询的信息。例如，试试这个查询：

SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;
复制

该substr调用将显示的 SQL 限制为 100 个字符，以避免长查询占用大量屏幕空间。过滤器backend_type避免显示后台服务器进程，例如autovacuum启动器。排序方式backend_start首先显示运行时间最长的连接，这通常显示有问题的长时间运行的事务。

这是在我的开发服务器上运行的示例：

stagingi_inventev=# SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;
 pid | state  |         backend_start         |                                                  q
-----+--------+-------------------------------+-----------------------------------------------------------------------------------------------------
  73 | active | 2022-06-17 18:57:15.850803+00 | SELECT pid, state, backend_start, substr(query, 0, 100) q FROM pg_stat_activity WHERE backend_type
  77 | idle   | 2022-06-17 18:57:33.567574+00 | SELECT /* long-running query */ pg_sleep(10000);
(2 rows)
复制

另一种查找 pid 的方法是通过操作系统，使用htop之类的工具。如果您有权访问 PostgreSQL 服务器并希望查找消耗最多资源的查询，这将非常有用。

好的，你有一个 pid，现在让我们停止那个查询！

2. 终止或取消流程

PostgreSQL 有两个查询停止函数，不同之处将在下面讨论。

“苛刻”的是pg_terminate_backend，您可以像这样使用它：

SELECT pg_terminate_backend(pid);
复制

选择pg_terminate_backend ( pid );
我默认使用这个，原因如下。

“kinder”函数是pg_cancel_backend，你可以像这样使用它：

SELECT pg_cancel_backend(pid);
复制

选择pg_cancel_backend ( pid );
使用任一时，请替换pid为您在步骤 1 中找到的 pid。例如，要从上面终止长时间运行的查询：

SELECT pg_terminate_backend(77);
复制

选择pg_terminate_backend ( 77 );
繁荣，它消失了。

有两个区别使pg_terminate_backend“更严厉”。

首先，pg_terminate_backend完全停止进程，导致连接关闭。这会回滚连接上的任何打开事务，释放它持有的所有锁。

相反，pg_cancel_backend仅中断正在运行的查询，使连接保持打开状态。当前事务或保存点被中止。因此，如果连接使用保存点，它仍然可以保持周围事务的打开状态，并带有待处理的数据更改和锁定。

其次，pg_terminate_backend立即应用*，同时pg_cancel_backend可以在后端流程生命周期的某些点推迟。所以有时候，你可能会跑pg_cancel_backend一会儿，然后什么也没发生。具体来说，当进程从客户端读取输入（例如传入查询）时，可能会发生这种情况。后端进程推迟处理取消，直到所有输入都被读取，否则连接无法保持打开和正常运行。

(* Ackshuallly ,pg_terminate_backend 也可能无法立即应用，但可能性要小得多。代码的小部分也推迟处理它。理论上，这些部分不会花费太多时间执行，但永远不要说永远。如果你有兴趣在深入研究源代码时，请从ProcessInterruptsin开始src/backend/tcop/postgres.c。）

我默认使用pg_terminate_backend. 通常，当我需要停止查询时，我想停止启动它的整个应用程序进程，回滚所有数据更改，并释放所有锁。使用时pg_cancel_backend，应用程序的错误处理代码可能会回滚事务/保存点，并继续运行类似的查询。它可能会继续持有有问题的锁。

发现无理取闹的运行查询

如果您想停止多个查询，则pg_terminate_backend()逐个运行它们可能会很麻烦。您可以使用 SQL 查找错误查询并生成终止语句，以便轻松摆脱它们。这里有几个例子。

阻塞特定进程的查询

如果你正在执行一个ALTER TABLE并且发现它被阻塞，等待一个表锁，你可能想要终止在那个表上持有锁的连接。这将允许ALTER TABLE继续。

例如，我最近在一个应用程序中工作，其中包含一些阻止数据库迁移的长时间运行的事务。这些长时间运行的查询可以安全地终止，因为负责的应用程序进程稍后会重新运行并填补任何空白。

ALTER TABLE您可以通过这样的查询找到被阻止的 pid pg_stat_activity：

SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND query LIKE 'ALTER TABLE%'
ORDER BY backend_start;
复制

使用阻塞的 pid，您可以使用此查询pg_blocking_pids来生成 SQL 以终止阻塞进程：

SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE pid = ANY (pg_blocking_pids(blockedpid));
复制

要运行此查询，请替换blockedpid为被阻止进程的 pid。然后复制粘贴输出行并运行它们。

这是一个使用这些查询来取消阻止的示例会话ALTER TABLE：

mydb=# SELECT pid, state, backend_start, substr(query, 0, 100) q
  FROM pg_stat_activity
  WHERE backend_type = 'client backend' AND query LIKE 'ALTER TABLE%'
  ORDER BY backend_start;
 pid | state  |         backend_start         |                       q
-----+--------+-------------------------------+------------------------------------------------
 613 | active | 2022-06-17 20:25:06.604326+00 | ALTER TABLE auth_user ADD COLUMN age int null;
(1 row)

mydb=# SELECT 'SELECT pg_terminate_backend(' || pid || ');'
  FROM pg_stat_activity
  WHERE pid = ANY (pg_blocking_pids(613));
             ?column?
----------------------------------
 SELECT pg_terminate_backend(77);
(1 row)

mydb=# SELECT pg_terminate_backend(77);
 pg_terminate_backend
----------------------
 t
(1 row)

mydb=# SELECT pid, state, backend_start, substr(query, 0, 100) q
  FROM pg_stat_activity
  WHERE backend_type = 'client backend' AND query LIKE 'ALTER TABLE%'
  ORDER BY backend_start;
 pid | state |         backend_start         |                       q
-----+-------+-------------------------------+------------------------------------------------
 613 | idle  | 2022-06-17 20:25:06.604326+00 | ALTER TABLE auth_user ADD COLUMN age int null;
(1 row)
复制

针对特定表的查询

有时您可能只想终止针对给定表运行的所有查询。这可能适用于阻止特定行为不当的应用程序进程的过载。

此查询将生成 SQL 以终止所有正在运行的查询，这些查询看起来像是在使用名为的特定表auth_user：

SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE query LIKE '%auth_user%'
  AND pid != pg_backend_pid();
复制

LIKE '%auth_user%'要使用此查询，请在运行之前更改匹配的表名。然后复制粘贴输出行并运行它们。

匹配有点生硬，因为它query有LIKE误报的机会，但它很简单。需要进行比较pg_backend_pid以避免匹配当前连接。

这是使用此查询终止对名为的表的所有查询的示例library_book：

mydb=# SELECT 'SELECT pg_terminate_backend(' || pid || ');'
  FROM pg_stat_activity
  WHERE query LIKE '%library_book%'
    AND pid != pg_backend_pid();
           ?column?
-------------------------------
 SELECT pg_terminate_backend(123);
 SELECT pg_terminate_backend(124);
(1 row)

mydb=# SELECT pg_terminate_backend(123);
SELECT pg_terminate_backend(124);
 pg_terminate_backend
-------------------
 t
(1 row)

 pg_terminate_backend
-------------------
 t
(1 row)
复制

连接打开时间超过 N 秒

最后一个例子：如何过滤掉那些打开时间超过 N 秒的连接。这是相当钝的锤子，但您可以在紧急情况下尝试一下。

SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE backend_type = 'client backend'
      AND pid != pg_backend_pid()
      AND backend_start < NOW() - '10 seconds'::interval;
复制

酌情调整’10 seconds’。

例如：

mydb=# SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE backend_type = 'client backend'
      AND backend_start < now() - '10 seconds'::interval
      AND pid != pg_backend_pid();
              ?column?
------------------------------------
 SELECT pg_terminate_backend(2675);
 SELECT pg_terminate_backend(2676);
(2 rows)

mydb=# SELECT pg_terminate_backend(2675);
SELECT pg_terminate_backend(2676);
 pg_terminate_backend
----------------------
 t
(1 row)

WARNING:  PID 2676 is not a PostgreSQL server process
 pg_terminate_backend
----------------------
 f
(1 row)
复制

😅 请注意，在这种情况下，似乎 pid 2676 在生成 SQL 和运行它之间完成。

通过操作系统停止查询

如果您有权访问 PostgreSQL 服务器，您还可以通过向相关操作系统进程发出信号来停止查询。这要求您拥有对 PostgreSQL 服务器的 shell 访问权限，而随着托管数据库平台的兴起，如今这并不常见。

您可以使用htop之类的工具来检查正在运行的后端进程。您可能能够根据高 CPU 或内存使用率找到有问题的问题。

后端进程显示为主 PostgreSQL 服务器进程的子进程。小心你选择正确的pid，好像杀死主进程，整个PostgreSQL服务器关闭。

在 Linux/macOS/Unix 上
在 Unix 操作系统上，您可以通过发送进程（终止信号）来终止正在运行的查询：SIGTERMkill

$ kill -SIGTERM pid
复制

替换pid为后端进程 pid。

您可以通过发送（中断信号）来取消进程：SIGINT

$ kill -SIGINT pid
复制

……再见查询！

取消查询进程

使用操作系统，我们还可以通过“杀死”它来保证立即停止进程。这就是“kill”实用程序名称的来源。（以及它过时的、暴力的术语。）在进程终止后继续消耗资源的极少数情况下，这样做可能很有用，这可能是由于 PostgreSQL 中的错误。

然而，这个动作是非常激烈的。立即终止立即停止该过程，没有任何机会进行清理。PostgreSQL 的设计应该防止数据丢失（已提交的行），但您可能会错过其他附带数据，例如日志消息。

要取消一个进程，向它发送KILL信号。

在 Unix 上：

$ pg_ctl kill TERM pid
复制

在 Windows 上：

$ pg_ctl kill杀死 pid
复制

写在最后：
愿你很少用到这些知识

postgresql 墨力翻译

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者