Tuning RDS for PostgreSQL with Amazon DevOps Guru proactive insights
DevOps Guru proactive insights detects conditions on your RDS for PostgreSQL DB instances that can cause problems, and lets you know about them before they occur. Proactive insights can alert you to a long running idle in transaction connection. For more information about troubleshooting long running idle in transaction connections, see Database has long running idle in transaction connection
DevOps Guru can do the following:
-
Prevent many common database issues by cross-checking your database configuration against common recommended settings.
-
Alert you to critical issues in your fleet that, if left unchecked, can lead to larger problems later.
-
Alert you to newly discovered problems.
Every proactive insight contains an analysis of the cause of the problem and recommendations for corrective actions.
For more information about Amazon DevOps Guru for Amazon RDS, see Analyzing performance anomalies with Amazon DevOps Guru for Amazon RDS.
Database has long running idle in transaction connection
A connection to the database has been in the idle in transaction
state for more than 1800 seconds.
Supported engine versions
This insight information is supported for all versions of RDS for PostgreSQL.
Context
A transaction in the idle in transaction
state can hold locks that block
other queries. It can also prevent VACUUM
(including autovacuum) from
cleaning up dead rows, leading to index or table bloat or transaction ID wraparound.
Likely causes for this issue
A transaction initiated in an interactive session with BEGIN or START TRANSACTION
hasn't ended by using a COMMIT, ROLLBACK, or END command. This causes the transaction to move to idle in
transaction
state.
Actions
You can find idle transactions by querying pg_stat_activity
.
In your SQL client, run the following query to list all connections in
idle in transaction
state and to order them by duration:
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;
We recommend different actions depending on the causes of your insight.
Topics
End transaction
When you initiate a transaction in an interactive session with BEGIN or START
TRANSACTION, it moves to idle in transaction
state. It remains in
this state until you end the transaction by issuing a COMMIT, ROLLBACK, END
command or disconnect the connection completely to roll back the
transaction.
Terminate the connection
Terminate the connection with an idle transaction using the following query:
SELECT pg_terminate_backend
(pid)
;
pid is the process ID of the connection.
Configure the idle_in_transaction_session_timeout parameter
Configure the idle_in_transaction_session_timeout
parameter in the parameter group. The advantage of configuring this
parameter is that it does not require a manual intervention to terminate the long idle in transaction. For more information on this parameter, see
the PostgreSQL documentation
The following message will be reported in the PostgreSQL log file after the connection is terminated, when a transaction is in the idle_in_transaction state for longer than the specified time.
FATAL: terminating connection due to idle in transaction timeout
Check the AUTOCOMMIT status
AUTOCOMMIT is turned on by default. But if it is accidentally turned off in the client ensure that you turn it back on.
-
In your psql client, run the following command:
postgres=>
\set AUTOCOMMIT on
-
In pgadmin, turn it on by choosing the AUTOCOMMIT option from the down arrow.
Check the transaction logic in your application code
Investigate your application logic for possible problems. Consider the following actions:
-
Check if the JDBC auto commit is set true in your application. Also, consider using explicit
COMMIT
commands in your code. -
Check your error handling logic to see whether it closes a transaction after errors.
-
Check whether your application is taking long to process the rows returned by a query while the transaction is open. If so, consider coding the application to close the transaction before processing the rows.
-
Check whether a transaction contains many long-running operations. If so, divide a single transaction into multiple transactions.
Relevant metrics
The following PI metrics are related to this insight:
-
idle_in_transaction_count - Number of sessions in
idle in transaction
state. -
idle_in_transaction_max_time - The duration of the longest running transaction in the
idle in transaction
state.