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

Problem with database connections timing out for long-running queries

From

"Rich Schaaf"

Date:

22 August 2015, 01:08:38

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

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

From

Adrian Klaver

Date:

22 August 2015, 01:23:41

On 08/21/2015 11:16 AM, Rich Schaaf 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.

Is there anything else in the log at this point in time that looks like
it might be related?

What is the actual disconnect message?

>
> 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
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Steve Crawford

Date:

22 August 2015, 01:25:07

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@commoninf.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

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

From

"Rich Schaaf"

Date:

24 August 2015, 17:33:08

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@pinpointresearch.com]
Sent: Friday, August 21, 2015 6:25 PM
To: Rich Schaaf
Cc: pgsql-general@postgresql.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@commoninf.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

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 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