PostgreSQL 教程: 检查长时间运行的事务

一月 10, 2024

摘要:在本教程中,您将学习如何在 PostgreSQL 中检查长时间运行的事务。

长事务产生的危险

在一个数据库连接上长时间运行的事务,对于非常活跃且频繁进行 vacuum 的表,会让表的访问速度变得非常慢,因为 vacuum 无法回收比那个长时间运行的事务更新的已删除行。

事务中更新的任何行都可能导致其他事务等待。这些事务也可能持有锁,现在它们停滞不前,这可能会导致它们阻止其他事务。此模式会形成阻塞链,并且在 OLTP 工作负载中,可能会大大降低性能。

推荐的做法也很简单:在尽可能短的时间内保持所需的锁。

列出长事务和慢查询

编写不当的查询或结构不佳的数据,可能会导致数据库的执行时间过长。通常是通过数据库响应缓慢或服务器 CPU 使用率的长时间增加来发现问题,pg_stat_activity视图可以帮助找出导致问题的查询。pg_stat_activity 视图包含所有当前正在运行的查询的详细信息,包括用户、连接和时间相关信息。

有时,应用程序可能会在打开的事务期间卡住/停顿,并且由于进程本身没有终止,数据库连接被泄露。这将留下一个长时间运行的事务,该事务在很长一段时间内未能提交/回滚,直到当 vacuum 在非常活跃的表上变得毫无意义时,就导致了数据库系统运行缓慢。

您可以使用以下查询列出 PostgreSQL 中长时间运行的事务和慢查询:

SELECT datname, pid, usename, application_name,
    client_addr, backend_start, xact_start, state_change,
    waiting, query
FROM pg_stat_activity
WHERE ( now() - xact_start ) > '30 minutes'
   OR ( now() - state_change ) > '10 minutes'
ORDER BY xact_start;

终止长事务

如果某些查询看上去不会很快完成,则可以使用 pg_stat_activity 或 pg_locks 视图中的pid(进程 ID)来终止正在运行的进程。

  • pg_cancel_backend(pid) 将尝试取消一个后端进程正在运行的查询。
  • pg_terminate_backend(pid) 将立即终止正在运行查询的进程,但可能会对数据库服务器上运行的其他查询产生副作用。运行 pg_terminate_backend 时可能会重置全部的连接,因此其他正在运行的查询可能会受到影响。应该将其作为最后的手段使用。