Search Postgresql Archives

Re: Problem with database connections timing out for long-running queries

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxxxxxx]
Sent: Friday, August 21, 2015 6:25 PM
To: Rich Schaaf
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxxxxxx> 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

 

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux