PostgreSQL中如何终止长时间运行的查询

作者：汉斯-于尔根·舍尼希（Hans-Jürgen Schönig）
Hans-Jürgen Schönig 自 90 年代以来一直与 PostgreSQL 合作。他是该领域市场领导者CYBERTEC PostgreSQL International的首席执行官和技术负责人。自 2000 年以来，他为全球无数客户提供服务。他还是广受好评的“Mastering PostgreSQL”系列书籍的作者，以及其他几本关于PostgreSQL复制和管理的书籍。

发表于 2023-10-17

前 言

人们可能是SQL的粉丝，也可能喜欢Kubernetes上的PostgreSQL，甚至可能喜欢数据库ORM（Object Relational Mapping）。但是你听说过一个喜欢慢查询的人吗？在 20+ 年的专业数据库工程中，我从未见过一个粉丝俱乐部尊重慢查询、糟糕的 SQL 和糟糕的用户体验。因此，让我们终止长时间运行的查询，并简单地将它们从我们的系统中删除。

以下为补充内容。
知识拓展：ORM是什么，如何使用（示例）？
文心一言回答：
ORM，即对象关系映射（Object Relational Mapping），是一种为了解决面向对象与关系数据库存在的互不匹配的现象的技术。简单的说，ORM是通过使用描述对象和数据库之间映射的元数据，将程序中的对象自动持久化到关系数据库中，实现了数据模型与数据库的解耦，从而简化了数据开发过程。
ORM的使用方式主要包括以下几个步骤：
定义数据模型：首先，需要根据数据库表的结构定义对应的数据模型，数据模型通常是一些类，类的属性对应数据库表的字段。
创建数据库连接：然后，需要创建与数据库的连接，以便进行后续的数据操作。
执行数据操作：通过ORM提供的API，可以执行增删改查等数据操作，而无需编写SQL语句。例如，可以通过调用数据模型的save方法将数据保存到数据库中，或者通过查询API从数据库中获取数据。

以下是一个使用Python的Django框架进行ORM操作的示例：
python复制代码如下：

from django.db import models

#定义数据模型
class Person(models.Model): 
name = models.CharField(max_length=100) 
age = models.IntegerField() 

#创建数据库连接
... 

#执行数据操作
person = Person(name='John', age=30) 
person.save() # 将数据保存到数据库

#查询数据
people = Person.objects.all() # 获取所有数据
for person in people: 
print(person.name, person.age)

以上示例中，首先定义了一个名为Person的数据模型，然后通过创建数据库连接，最后使用ORM提供的API执行数据操作。

识别PostgreSQL中的慢查询和不良性能

关于如何识别和修复慢查询的文章已经写了很多，我只想参考其中一些帖子来帮助人们：
1.快速检测慢查询
https://www.cybertec-postgresql.com/en/postgresql-detecting-slow-queries-quickly/
2.检测慢查询的 3 种方法
https://www.cybertec-postgresql.com/en/3-ways-to-detect-slow-queries-in-postgresql/
但是，在这篇博客中，我们希望重点介绍如何在系统中发现慢查询后杀死它。

杀死慢查询

基本上，有两种方法可以摆脱慢查询：
1.终止查询，但使数据库连接保持活动状态
2.终止整个数据库连接
为了确保我们有一个可以消除的慢查询，我们首先需要启动一个：

人为创建长时间运行的查询

1 test=# SELECT pg_sleep(1000000);
此查询只是休眠，在可预见的未来不会终止。那么我们如何才能找到并杀死它呢？

先找到长时间运行的查询：

test=# \x
Expanded display is on.

test=# SELECT * 
FROM pg_stat_activity
WHERE datname IS NOT NULL
  AND state = 'active'
ORDER BY query_start
LIMIT 1;

-[ RECORD 1 ]----+------------------------------
datid            | 16384
datname          | test
pid              | 1207
leader_pid       | 
usesysid         | 10
usename          | hs
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2023-08-10 15:24:13.980079+02
xact_start       | 2023-08-10 15:24:20.443691+02
query_start      | 2023-08-10 15:24:20.443691+02
state_change     | 2023-08-10 15:24:20.443697+02
wait_event_type  | Timeout
wait_event       | PgSleep
state            | active
backend_xid      | 
backend_xmin     | 899
query_id         | 
query            | SELECT pg_sleep(1000000);
backend_type     | client backend

PostgreSQL成功返回了在我的系统上运行时间最长的查询。这里重要的是：当 pg_stat_activity 查询 ，您需要确保只查找实际具有有效数据库的行，否则我们最终会得到系统进程。
那么：如何消除此查询？
我们要从中 pg_stat_activity 检索的重要信息是执行要终止的内容的数据库后端的进程ID（pid）。在我的例子中，这个PID = 1207。

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

1.pg_cancel_backend ：终止查询并保持连接处于活动状态
2.pg_terminate_backend ：终止数据库连接，包括查询
让我们尝试第一个函数：

test=# SELECT pg_cancel_backend(1207);

pg_cancel_backend
-------------------
 t
(1 row)

在第二个连接中执行此操作将确保 PostgreSQL 终止长时间运行的查询，这将在第一个连接中导致以下输出：
test=# SELECT pg_sleep(1000000);
ERROR: canceling statement due to user request
有时查询不会立即停止。这很好，当然建议耐心等待。

但是，有时我们想杀死整个后端。我们通过使用第二个函数来做到这一点：

test=# SELECT pg_terminate_backend(1207);
 pg_terminate_backend
----------------------
 t
(1 row)

使用这两种方法，我们有问题的查询将停止消耗宝贵的资源。第一种方法侵入性较小，因为它不会终止数据库会话。
防止将来出现错误的 SQL
虽然修复单个案例很容易，但关注整体情况并确保自动清理错误的 SQL 也很重要。

使用statement_timeout 自动删除慢速查询

一种方法是让PostgreSQL为您删除错误的查询。使用该 statement_timeout 变量（单位为毫秒）。以下是它的工作原理：

test=# SET statement_timeout TO 1000;
SET
test=# SELECT pg_sleep(1000000);
ERROR:  canceling statement due to statement timeout

PostgreSQL 在 1 秒后终止了查询。

重要的是：我们可以设置一个相当相对合理的值 postgresql.conf ，以确保该设置在 PostgreSQL 实例的所有数据库会话中都有效。

以下为补充内容。
知识点拓展：statement_timeout参数的作用与使用方法是什么？
Chatgpt 3.5回答：
PostgreSQL的参数statement_timeout用于设置一个查询的超时时间。当一个查询执行时间超过该超时时间时，查询将被自动终止。

使用statement_timeout参数可以避免长时间运行的查询对数据库性能和资源的消耗。它可以帮助限制查询的执行时间，防止查询占用过多的系统资源，并且可以防止查询无限期地运行。

要设置statement_timeout参数，可以按照以下步骤进行操作：
1.打开PostgreSQL配置文件 postgresql.conf，该文件通常位于数据库的数据目录下。
2.找到statement_timeout参数的行，如果没有则需要手动添加。
3.设置参数值，以毫秒为单位。例如，设置为10000表示10秒超时。
4.保存并关闭配置文件。
5.重启PostgreSQL服务器，以使更改生效。

另外，也可以通过使用SET命令在会话级别上设置statement_timeout参数。
例如：SET statement_timeout = 10000; — 设置为10秒超时
这将在当前会话中设置查询超时时间为10秒。

需要注意的是，statement_timeout参数是一个全局参数，对所有查询生效。
如果想要对特定的查询设置不同的超时时间，可以使用SET LOCAL命令在会话中临时更改超时时间。
例如：SET LOCAL statement_timeout = 5000; — 设置为5秒超时
这将仅在当前会话中设置查询超时时间为5秒，不会影响其他会话。

最后。。。

长时间运行的查询必须牢记各种含义。这不仅关乎资源和性能，还关乎确保始终控制表膨胀。查看有关Autovacuum，UPDATE以及更多内容的帖子，以了解有关此重要主题的更多信息。
有时，即使您尝试使用本文中显示的方法中断语句，语句也会继续运行。然后，您可能想阅读我们关于如何取消拒绝死亡的声明的文章，文章链接：https://www.cybertec-postgresql.com/en/cancel-hanging-postgresql-query/