使用 Amazon DevOps Guru 主动见解优化 RDS for PostgreSQL
DevOps Guru 主动见解可检测 RDS for PostgreSQL 数据库实例上可能导致问题的情况,并在问题发生之前告知您。主动的洞察可以提醒您具有长时间运行的事务空闲连接。有关排查长时间运行的事务空闲连接的更多信息,请参阅数据库具有长时间运行的事务空闲连接。
DevOps Guru 可以执行以下操作:
-
通过对照常见的建议设置交叉检查数据库配置,可以防止许多常见的数据库问题。
-
提醒您注意实例集中的关键问题,如果不加以检查,以后可能会导致更大的问题。
-
提醒您注意新发现的问题。
每项主动见解都包含对问题原因的分析和纠正措施建议。
有关适用于 Amazon RDS 的 Amazon DevOps Guru 的更多信息,请参阅使用适用于 Amazon RDS 的 Amazon DevOps Guru 分析性能异常。
数据库具有长时间运行的事务空闲连接
与数据库的连接处于 idle in transaction
状态的时间已超过 1800 秒。
支持的引擎版本
RDS for PostgreSQL 的所有版本都支持这些见解信息。
上下文
处于 idle in transaction
状态的事务可能持有旨在阻止其他查询的锁。它还可以防止 VACUUM
(包括 autovaum)清理死行,从而导致索引或表膨胀或事务 ID 重叠。
这个问题的可能原因
在使用 BEGIN 或 START TRANSATION 的交互式会话中启动的事务尚未通过使用 COMMIT、ROLLBACK 或 END 命令结束。这会导致事务移至 idle in
transaction
状态。
操作
您可以通过查询 pg_stat_activity
来查找空闲的事务。
在 SQL 客户端中,运行以下查询以列出所有处于 idle in transaction
状态的连接,并按持续时间对它们进行排序:
SELECT now() - state_change as idle_in_transaction_duration, now() - xact_start as xact_duration,* FROM pg_stat_activity WHERE state = 'idle in transaction' AND xact_start is not null ORDER BY 1 DESC;
根据见解的原因,我们建议采取不同的操作。
结束事务
当您在交互式会话中使用 BEGIN 或 START TRANSACTION 启动事务时,它会转入 idle in transaction
状态。它会一直保持此状态,直到您通过发出 COMMIT、ROLLBACK、END 命令结束事务,或完全断开连接以回滚事务。
终止连接
使用以下查询终止与空闲事务的连接:
SELECT pg_terminate_backend
(pid)
;
pid 是连接的进程 ID。
配置 idle_in_transaction_session_timeout 参数
在参数组中配置 idle_in_transaction_session_timeout
参数。配置此参数的优势在于,它不需要手动干预即可终止长时间空闲的事务。有关此参数的更多信息,请参阅 PostgreSQL 文档
当事务处于 idle_in_transaction 状态超过指定时间时,连接终止后,PostgreSQL 日志文件中将报告以下消息。
FATAL: terminating connection due to idle in transaction timeout
检查 AUTOCOMMIT 状态
原定设置情况下,AUTOCOMMIT 处于开启状态。但是,如果在客户端中意外将其关闭,请确保将其重新开启。
-
在 psql 客户端中,运行以下命令:
postgres=>
\set AUTOCOMMIT on
-
在 pgadmin 中,通过从向下箭头中选择 AUTOCOMMIT 选项将其开启。
检查应用程序代码中的事务逻辑
调查应用程序逻辑中可能存在的问题。请考虑以下操作:
-
检查应用程序中 JDBC 自动提交是否设置为 true。另外,可以考虑在代码中使用显式
COMMIT
命令。 -
检查错误处理逻辑,看看它是否会在错误后关闭事务。
-
在事务打开时,检查您的应用程序处理查询所返回的行是否花费了很长时间。如果是,请考虑在处理行之前对应用程序进行编码以关闭事务。
-
检查事务是否包含许多长时间运行的操作。如果是,请将单个事务分成多个事务。
相关指标
以下 PI 指标与此见解相关:
-
idle_in_transaction_count - 处于
idle in transaction
状态的会话数。 -
idle_in_transaction_max_time - 处于
idle in transaction
状态的运行时间最长的事务的持续时间。