Timeouts connecting to a Postgres database on Amazon RDS from Azure

Question

I get the following exception in my application after leaving a database connection idle for some amount of time:

... An I/O error occured while sending to the backend.; nested exception is org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.] with root cause

    java.net.SocketException: Operation timed out
        at java.net.SocketInputStream.socketRead0(Native Method)

The same issue happens in psql AND I don't have issues connecting to a local database, so I'm pretty sure the problem is on RDS.

psql=> select 'ok';
SSL SYSCALL error: Operation timed out
psql=> select 'ok';
SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset: Succeeded.

I found this other question which suggests a work around that improved the situation (timeouts now take a lot longer) but didn't fix it.

I'm using Spring Boot with JDBC (tomcat connection pooling) and JDBCTemplate.

Is there a work around or a fix? Perhaps forcing the connection pool to test and reconnect? How do I do that in this environment?

EDIT: This is my connection string

jdbc:postgresql://myhost.c2estvxozjm3.eu-west-1.rds.amazonaws.com/dashboard?tcpKeepAlive=true

SOLUTION:

Edited the RDS server side TCP_KeepAlive parameters as suggested in the selected answer. The parameters I'm using are:

tcp_keepalives_count      5
tcp_keepalives_idle     200
tcp_keepalives_interval 200

score 24 · Accepted Answer · answered Mar 28 '14 at 00:28

24

It looks like something - maybe a NAT router on your end, maybe something on AWS's end - is connection tracking, and is forgetting about connections after a while.

I suggest enabling TCP keepalives. You might be able to enable them server side in the AWS RDS configuration; if not, you can request them client-side in the JDBC driver.

TCP keepalives are a lot better than a validation/test query, because they're much lower overhead, and they don't result in unnecessary log spam in the server query logs.

answered Mar 28 '14 at 00:28

Craig Ringer

307,061
76
688
778

1

This seems to have solved the issue, since I added these keepalives at AWS postgres side I haven't had timeouts anymore. I'll remove the other things I tried (TCP keepalives on the linux kernel, keepalive on the connection string and connection validation on the connection pool) and see if that holds by its own. – Cleber Goncalves Apr 03 '14 at 09:05
1

This fixed the issue, the parameters I used on RDS are on the edited question. – Cleber Goncalves Apr 07 '14 at 07:24

score 0 · Answer 2 · answered Mar 27 '14 at 22:05

0

Maybe try

spring.datasource.validation-query=SELECT 1
spring.datasource.test-on-borrow=true

(See AbstractDataSourceConfiguration for other options.)

answered Mar 27 '14 at 22:05

Dave Syer

56,583
10
155
143

This 'kind of solve' the issue, I don't have any problems in my development machine, but on my server (hosted on Azure) it takes 15min to get an response when I leave the connection idle. Subsequent responses are immediate, until I leave the connection idle for a length of time again. The difference between my dev machine and the server, besides specs, is the fact that I changed the TCP keepalive settings for the OS as described in [this question](http://stackoverflow.com/questions/20856599/mysql-ping-hangs-with-amazon-rds). Will try to set that on the server next. – Cleber Goncalves Mar 30 '14 at 10:00
No luck there, changed the TCPKeepAlive on the Azure server but still have the 15m delay issue after the connection becomes idle. – Cleber Goncalves Mar 31 '14 at 08:54
Validation queries also lead to lots of log spam and are generally not the ideal, though sometimes you don't get much alternative. – Craig Ringer Apr 03 '14 at 09:14

score 0 · Answer 3 · answered Mar 28 '14 at 13:58

0

In your connection string are you also including the port or just the endpoint? Try using the entire endpoint in your connection string. Also check to make sure the security group assigned to the RDS instance has the proper ports and Inbound CIDR defined.

answered Mar 28 '14 at 13:58

user2040074

634
1
5
16

1

Added my connection string to the question (changed the hostname). I'm able to connect to the DB using that string, issues only happen after I leave the connection idle for a while – Cleber Goncalves Mar 29 '14 at 17:37

Timeouts connecting to a Postgres database on Amazon RDS from Azure

3 Answers3

Linked