Postgres tcp_keepalive_xxxx parameters.

[jlrando]

Hi.

We have an issue with postgres clients tha are being disconnected from
database server after some time. Client is a web application which creates a
pool of connections. Since client and server are on different VLANS I think
the problem is that the FW which is routing traffic is droping idle
connections after some time.

Os is CentOS 5.10 and kernel config is as follows:

***@itk-iv-mcs ~ # sysctl -a | grep keepalive
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 7200

tcp_keepalive_xxx in postgresql.conf were set to 0 all. Then we tried to set

tcp_keepalives_idle = 60 # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 60 # TCP_KEEPINTVL, in seconds;
#tcp_keepalives_count = 0 # TCP_KEEPCNT;

So effective values (I guess are) 60,60 and 9 (system default). Even with
this configuration the cliens are being disconnected as before.

At this point I am not sure if I have understood properly the way keepalive
is working or not, becase if PG will send a packet after 60 seconds of
inactivity and the client respond ok then it will not retry and wait for
"tcp_keepalives_idle" seconds again... and so on. Then "interval" and
"count" are used only in case the client does not respond. If this is true
and I am not wrong then we can discard this issue as FW issue regarding idle
connections... Is this the behaviour of the tcp_keepalives_xxx parameters?

Is there any more I have to setup or check to make keepalive work?
Do you know how can we sort out this issue?

Thanks in advance.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Postgres-tcp-keepalive-xxxx-parameters-tp5821282.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[Tom Lane]

Post by jlrando
We have an issue with postgres clients tha are being disconnected from
database server after some time. Client is a web application which creates a
pool of connections. Since client and server are on different VLANS I think
the problem is that the FW which is routing traffic is droping idle
connections after some time.

Probably. It might be asymmetric; have you tried enabling keepalives
from the client end, rather than the server? If using libpq, you can
set keepalive parameters in the connection string:
http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS

If that doesn't fix it, you might want to get out Wireshark or a similar
tool and verify that keepalive packets are actually getting sent.

regards, tom lane

[José Luis Rando Calvo]

Hi Tom,

First of all, thank you for your fast response!

I did not try to set up keepalives in client yet. This is a good point. We are using JDBC to connect to PG from JBoss application server.
I have seen the JDBC documentation and keepalive can be enabled when creating the connection to the database:

http://jdbc.postgresql.org/documentation/head/connect.html

Unfortunately seems to be tricky to enable it because I guess it implies to change the Java code. In that case it is not straight forward.
I do not know if there is a configuration file to set up this. I am going to look for it.

By the way, can you confirm me if keepalive behaves like I stated in my previous update...?

Thanks and best regards,

JL

-----Original Message-----
From: Tom Lane [mailto:***@sss.pgh.pa.us]
Sent: miércoles, 01 de octubre de 2014 17:04
To: José Luis Rando Calvo
Cc: pgsql-***@postgresql.org
Subject: Re: [GENERAL] Postgres tcp_keepalive_xxxx parameters.

Post by jlrando
We have an issue with postgres clients tha are being disconnected from
database server after some time. Client is a web application which
creates a pool of connections. Since client and server are on
different VLANS I think the problem is that the FW which is routing
traffic is droping idle connections after some time.

Probably. It might be asymmetric; have you tried enabling keepalives from the client end, rather than the server? If using libpq, you can set keepalive parameters in the connection string:
http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS

If that doesn't fix it, you might want to get out Wireshark or a similar tool and verify that keepalive packets are actually getting sent.

regards, tom lane

[Jonathan Vanasco]

Does anyone have a good solution for benching queries under various conditions, and collecting the EXPLAIN data ?

I looked at pgbench, but it doesn't seem to be what I want.

My situation is this-

- For a given query, there are 3-5 different ways that I can run it.
- Each form of the query has a completely different execution plan and query time, often using different indexes.
- The same query runs differently on first query, vs subsequent queries (when the indexes/tables are already in memory).

My goal is to find an overall balance of query time (cold-start vs in-memory) and indexes (number of).