Обсуждение: Postgres tcp_keepalive_xxxx parameters.
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: root@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.
jlrando <jose.luis.rando.calvo@ericsson.com> writes: > 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
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 applicationserver. 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 notstraight 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:tgl@sss.pgh.pa.us] Sent: miércoles, 01 de octubre de 2014 17:04 To: José Luis Rando Calvo Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres tcp_keepalive_xxxx parameters. jlrando <jose.luis.rando.calvo@ericsson.com> writes: > 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 usinglibpq, 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 actuallygetting sent. regards, tom lane
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).