Please explain how to investigate methods for setting the threshold value for idle_in_transaction_session_timeout.
Question
I am operating RDS for PostgreSQL. To prevent situations where the "idle in transaction" state continues for a long time due to application bugs, causing table locks to be held, I'm considering setting the idle_in_transaction_session_timeout in the parameter group.
However, I'm having trouble determining what the appropriate timeout value should be.
I enabled log_statement and investigated the logs, but while I could track the overall session time, I couldn't accurately isolate just the "idle time within transactions where nothing is happening."
Please advise me on how to investigate idle times to set a safe and effective timeout value that won't erroneously disconnect normal batch processes.
Answer
Please use the Performance Insights counter metric idle_in_transaction_max_time.
Performance Insights counter metrics - Amazon Relational Database Service
| Counter | Type | Unit | Metric | Description |
|---|---|---|---|---|
| idle_in_transaction_max_time | State | [seconds] | db.state.idle_in_transaction_max_time | Represents the time in seconds of the longest running transaction in the idle in transaction state. |
This metric directly shows the "maximum duration of transactions in the idle in transaction state" in seconds, allowing you to understand the actual maximum idle time occurring in your database.
Monitor this metric for a certain period (such as one week, which should include your normal business cycle) and check the maximum value. This will be the measured value of the maximum idle time that can occur during normal operations.
Set the parameter group idle_in_transaction_session_timeout to this measured value plus a safety margin.
Summary
When struggling with the idle_in_transaction_session_timeout setting, check the idle_in_transaction_max_time metric in Performance Insights.
I hope this article is helpful to someone.
![[アップデート] CloudWatchでネットワークモニタリングでリージョン間のフローも見れるようになりました！](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-d6a3bc85cad6419960fbda152152cd8c/ba5841cea06b96f4933878762028b090/amazon-cloudwatch)
