agora inbox for pgsql-interfaces@postgresql.org
help / color / mirror / Atom feedFrom: Bill Clay <william.b.clay@acm.org>
To: pgsql-interfaces@postgresql.org
Subject: libpq connect keepalive* parms: no effect!?
Date: Mon, 02 Mar 2015 17:31:39 -0500
Message-ID: <54F4E4CB.3040601@acm.org> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-interfaces>
I have searched fairly thoroughly and been unable to find a way to force
prompt client application session breaks when PostgreSQL
client-to-server transport fails.
I run a 7x24 PostgreSQL 9.1 "write-only" libpq client application
(solely INSERTs/COPYs running on Debian 7 "wheezy" OS) that communicates
with its PostgreSQL 9.0 DB server (Debian 6 "squeeze") via
less-than-perfect intercontinental TCP Internet/VPN transport. The
application has been running very reliably for over 4 years except for
communication breaks.
Unfortunately, in this environment, connectivity lapses of a minute or
two to an hour or two are common. To minimize the risk of data loss
when session recovery is attempted only AFTER the client queues data, I
want to promptly detect and attempt recovery of lost sessions even when
no transactions are pending. To this end, I have tried:
#define PGSQL_KEEPALIVE_QSECS "60"
char pstring[6];
snprintf(pstring, sizeof(pstring), "%i", cnt0->conf.pgsql_port);
PQconnectdbParams(
(const char *[]) {"dbname", "host",
"user", "password", "port", "sslmode",
"application_name", "connect_timeout", "keepalives",
"keepalives_idle", "keepalives_interval", "keepalives_count", NULL},
(const char *[]) {cnt0->conf.pgsql_db, cnt0->conf.pgsql_host,
cnt0->conf.pgsql_user, cnt0->conf.pgsql_password, pstring, "disable",
"motion", PGSQL_KEEPALIVE_QSECS, "1",
PGSQL_KEEPALIVE_QSECS, PGSQL_KEEPALIVE_QSECS, "3", NULL}, 0))
As a baseline comparison, I establish a psql session with an all-default
environment, break the VPN link, and then attempt a simple query (select
count(*) from ...). The query and psql session fail after about 17
minutes' wait. When testing the application -- even specifiying the
above connection parameters -- I get approximately the same 17 minute
timeout before a broken session is signalled at the application
(PQconsumeInput(conn); if (PQstatus(conn)!=PGSQL_CONNECTION_OK) ...)
when testing over the intentionally broken link. This is a far cry from
the maximum of 5 minutes I expected.
Based on postings elsewhere, I have also tried changing the relevant
Linux kernel defaults of:
/proc/sys/net/ipv4/tcp_keepalive_time=7200
/proc/sys/net/ipv4/tcp_keepalive_probes=9
/proc/sys/net/ipv4/tcp_keepalive_intvl=75
to:
/proc/sys/net/ipv4/tcp_keepalive_time=60
/proc/sys/net/ipv4/tcp_keepalive_probes=3
/proc/sys/net/ipv4/tcp_keepalive_intvl=15
... with no detectable effect; still a ca. 17 minute timeout. (Failure
of initial connection establishment IS indicated rapidly; ca. 20 sec.,
with or without any of the above measures, even connection_timeout=60.)
Any ideas how to achieve the keepalives as specified in
PQconnectdbParams when running on these platforms?
Thanks,
Bill Clay
--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces
view thread (2+ messages) latest in thread
Message-ID: <54F4E4CB.3040601@acm.org>
Permalink: ../54F4E4CB.3040601@acm.org/
Also on: postgresql.org/message-id/54F4E4CB.3040601@acm.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: william.b.clay@acm.org
Subject: Re: libpq connect keepalive* parms: no effect!?
In-Reply-To: <54F4E4CB.3040601@acm.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox