From: | "Rich Schaaf" <rschaaf(at)commoninf(dot)com> |
---|---|
To: | "'Steve Crawford'" <scrawford(at)pinpointresearch(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problem with database connections timing out for long-running queries |
Date: | 2015-08-22 11:28:55 |
Message-ID: | 003b01d0dccd$c0e628e0$42b27aa0$@commoninf.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am using the default TIMEOUTidle setting in stunnel so that seems plausible.
Thanks very much for pointing me in what sounds like the right direction!
Kind regards,
Rich
From: Steve Crawford [mailto:scrawford(at)pinpointresearch(dot)com]
Sent: Friday, August 21, 2015 6:25 PM
To: Rich Schaaf
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Problem with database connections timing out for long-running queries
You might check the stunnel settings. A quick search of "stunnel 12-hours" indicates that this is the stunnel default for idle connections.
Cheers,
Steve
On Fri, Aug 21, 2015 at 11:16 AM, Rich Schaaf <rschaaf(at)commoninf(dot)com> wrote:
I’m running into a problem where the connection between application its database is timing out after 12 hours.
Here’s the context:
· Two servers (one running the application and another hosting the database) in the Amazon AWS environment on different subnets within a virtual private cloud
· The server hosting the application is running Ubuntu 14.04.2 LTS
· The server hosting the database is running Ubuntu 14.04.2 LTS and PostgreSQL version 9.4.4
· An stunnel connection is provisioned from the client to the database. The app server is running stunnel version 4.53 with the following stunnel.conf settings:
; PID is created inside the chroot jail
pid = /stunnel4.pid
[postgres-<host>]
client = yes
protocol = pgsql
accept = 5432
connect = <host>:5432
options = NO_TICKET
retry = yes
· The database is provisioned to only accept “hostssl” type connections from the application server
I’ve enabled the logging of PostgreSQL connections and disconnections and I see that for connections that terminate before the associated query completes, the session time shown in the disconnect message is 12 hours plus three or four seconds.
I see this problem with both of the default tcp_keepalives settings of:
#tcp_keepalives_idle = 0
#tcp_keepalives_interval = 0
#tcp_keepalives_count = 0
And with the following settings:
tcp_keepalives_idle = 120
tcp_keepalives_interval = 120
tcp_keepalives_count = 5
I don’t see the disconnect problem for long-running queries that use a local database connection.
Any advice on what might be causing remote database connections to drop after 12 hours (and how to work around the issue)?
Kind regards,
Rich
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-08-22 13:51:49 | Re: Problem with pl/python procedure connecting to the internet |
Previous Message | Igor Sosa Mayor | 2015-08-22 10:24:01 | Re: Problem with pl/python procedure connecting to the internet |