Is there a way to increase DB connection time for an AWS RDS during a large SQL query?

user_10108404 0
Viewed 2932

When I first setup my Postgresql database I was able to run a large query that took approximately 5 minutes and 30 seconds to complete. I switched my AWS RDS instance to a lower tier to see what the performance difference would be, but the query was unsuccessful so I switched it back to the original one I had picked. However, the query is still not working even now that the instance is back on the original AWS tier. Looking at the instance in AWS CloudWatch monitoring, it appears as though the DB connection is only staying active for 5 minutes. Is there a way to extend this time?

I'm using DBeaver, AWS RDS, and Postgresql.

dbeaver amazon-rds postgresql
2 Answers

You can increase the "tcp_keepalives_ilde" in RDS Parameter Group, set it between 30 - 60 seconds will do. According to Postgres Documentation, Specifies the number of seconds of inactivity after which TCP should send a keepalive message to the client.

Setting statement_timeout in your scenario is NOT advisable according to here. By default the value is 0 and A value of zero (the default) disables the timeout.

In AWS RDS, "tcp_keepalives_idle" is a Dynamic Parameter, which means it will take effect immediately after apply without needing to reboot the instances. The actual wording from AWS Documentation is as follow: "When you change a dynamic parameter and save the DB parameter group, the change is applied immediately regardless of the Apply Immediately setting"

For the benefit of other, can refers to AWS Documentation on create new parameter group, then back to RDS to Modify the RDS Instance to use the newly created parameter group.

user_22974380

I seem to have resolved my issue by enabling a TCP keepalive parameter for every 60 seconds.

Go to Amazon RDS > Parameter Groups > Create a Postgres parameter group > Select the new parameter group > Click edit parameters > Filter for "alive" > Set tcp_keepalives_idle to 60 > Save. I also filtered for "timeout" and set statement_timeout to 3600000, though this might have been unnecessary.

Then modify your database and apply the new parameter group to it. You may need to reboot the instance to get it to apply.

user_101084040
Related