使用 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;

根据见解的原因，我们建议采取不同的操作。

主题

结束事务
终止连接
配置 idle_in_transaction_session_timeout 参数
检查 AUTOCOMMIT 状态
检查应用程序代码中的事务逻辑

结束事务

当您在交互式会话中使用 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 命令。
检查错误处理逻辑，看看它是否会在错误后关闭事务。
在事务打开时，检查您的应用程序处理查询所返回的行是否花费了很长时间。如果是，请考虑在处理行之前对应用程序进行编码以关闭事务。
检查事务是否包含许多长时间运行的操作。如果是，请将单个事务分成多个事务。