MIME-Version: 1.0
References: <CAKgGyB91QVCOFp+3vAcMDAauAPQ+TOVWrJxwcVAtYxaLxXaz4Q@mail.gmail.com>
 <202410061837.u5y5zwsxcpfb@alvherre.pgsql>
In-Reply-To: <202410061837.u5y5zwsxcpfb@alvherre.pgsql>
From: KK CHN <kkchn.in@gmail.com>
Date: Mon, 7 Oct 2024 17:00:47 +0530
Message-ID: <CAKgGyB8zMUzJSMQL8ryc-73aG3YEhWj4hGkqtOBT_yywesyCHQ@mail.gmail.com>
Subject: Re: CLOSE_WAIT pileup and Application Timeout
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003a2f9b0623e15a67"
Archived-At: <https://www.postgresql.org/message-id/CAKgGyB8zMUzJSMQL8ryc-73aG3YEhWj4hGkqtOBT_yywesyCHQ%40mail.gmail.com>
Precedence: bulk

--0000000000003a2f9b0623e15a67
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Oct 7, 2024 at 12:07=E2=80=AFAM Alvaro Herrera <alvherre@alvh.no-ip=
.org>
wrote:

> On 2024-Oct-04, KK CHN wrote:
>
> > The mobile tablets are installed with the android based vehicle
> > tracking app which updated every 30 seconds its location fitted inside
> the
> > vehicle ( lat long coordinates) to the PostgreSQL DB through the java
> > backend application to know the latest location of the vehicle and its
> > movement which will be rendered in a map based front end.
> >
> > The vehicles on the field communicate  via 443 to   8080 of the Wildfly
> > (version 27 ) deployed with the vehicle tracking application developed
> with
> > Java(version 17).
>
> It sounds like setting TCP keepalives in the connections between the
> Wildfly and the vehicles might help get the number of dead connections
> down to a reasonable level.  Then it's up to Wildfly to close the
> connections to Postgres in a timely fashion.  (It's not clear from your
> description how do vehicle connections to Wildfly relate to Postgres
> connections.)
>
>
Where do I have to introduce the TCP keepalives ? in the OS level or
application code level ?

[root@dbch wildfly-27.0.0.Final]# cat /proc/sys/net/ipv4/tcp_keepalive_time
7200
[root@dbch wildfly-27.0.0.Final]# cat /proc/sys/net/ipv4/tcp_keepalive_intv=
l
75
[root@dbch wildfly-27.0.0.Final]# cat
/proc/sys/net/ipv4/tcp_keepalive_probes
9
[root@dbch wildfly-27.0.0.Final]#

These are the default values in the OS level.   Do I need to reduce all the
above three values to  say 600, 20, 5  ?   Or need to be handled in the
application backend code ?

 Any hints much appreciated..

>
> I wonder if the connections from Wildfly to Postgres use SSL?  Because
> there are reported cases where TCP connections are kept and accumulate,
> causing problems -- but apparently SSL is a necessary piece for that to
> happen.
>
No SSL in between   Wildfly (8080 ) to    PGSQL(5432).  Both the machines
internal lan VMs  in the same network.    Only the devices on the field
(fitted on the  vehicles) communicate to the application backend via a
public URL :443  port  then  it connectes  to the 8080 of wildfly then the
java code connects the  database server running on 5432 on the internal LAN
network.

>

> --
> =C3=81lvaro Herrera               48=C2=B001'N 7=C2=B057'E  =E2=80=94
> https://www.EnterpriseDB.com/
> Thou shalt study thy libraries and strive not to reinvent them without
> cause, that thy code may be short and readable and thy days pleasant
> and productive. (7th Commandment for C Programmers)
>

--0000000000003a2f9b0623e15a67
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Mon, Oct 7, 2024 at 12:07=E2=80=AF=
AM Alvaro Herrera &lt;<a href=3D"mailto:alvherre@alvh.no-ip.org">alvherre@a=
lvh.no-ip.org</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex">On 2024-Oct-04, KK CHN wrote:<br>
<br>
&gt; The mobile tablets are installed with the android based vehicle<br>
&gt; tracking app which updated every 30 seconds its location fitted inside=
 the<br>
&gt; vehicle ( lat long coordinates) to the PostgreSQL DB through the java<=
br>
&gt; backend application to know the latest location of the vehicle and its=
<br>
&gt; movement which will be rendered in a map based front end.<br>
&gt; <br>
&gt; The vehicles on the field communicate=C2=A0 via 443 to=C2=A0 =C2=A0808=
0 of the Wildfly<br>
&gt; (version 27 ) deployed with the vehicle tracking application developed=
 with<br>
&gt; Java(version 17).<br>
<br>
It sounds like setting TCP keepalives in the connections between the<br>
Wildfly and the vehicles might help get the number of dead connections<br>
down to a reasonable level.=C2=A0 Then it&#39;s up to Wildfly to close the<=
br>
connections to Postgres in a timely fashion.=C2=A0 (It&#39;s not clear from=
 your<br>
description how do vehicle connections to Wildfly relate to Postgres<br>
connections.)<br>
<br></blockquote><div><br></div><div>Where do I have to introduce the TCP k=
eepalives ? in the OS level or application code level ?</div><div><br></div=
><div>[root@dbch wildfly-27.0.0.Final]# cat /proc/sys/net/ipv4/tcp_keepaliv=
e_time<br>7200<br>[root@dbch wildfly-27.0.0.Final]# cat /proc/sys/net/ipv4/=
tcp_keepalive_intvl<br>75<br>[root@dbch wildfly-27.0.0.Final]# cat /proc/sy=
s/net/ipv4/tcp_keepalive_probes<br>9<br>[root@dbch wildfly-27.0.0.Final]#<b=
r></div><div><br></div><div>These are the default values in the OS level.=
=C2=A0 =C2=A0Do I need to reduce all the above three values to=C2=A0 say 60=
0, 20, 5=C2=A0 ?=C2=A0 =C2=A0Or need to be handled in the application backe=
nd code ?=C2=A0</div><div><br></div><div>=C2=A0Any hints much appreciated..=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
I wonder if the connections from Wildfly to Postgres use SSL?=C2=A0 Because=
<br>
there are reported cases where TCP connections are kept and accumulate,<br>
causing problems -- but apparently SSL is a necessary piece for that to<br>
happen.<br></blockquote><div>No SSL in between=C2=A0 =C2=A0Wildfly (8080 ) =
to=C2=A0 =C2=A0 PGSQL(5432).=C2=A0 Both the machines internal lan VMs=C2=A0=
 in the same network.=C2=A0 =C2=A0 Only the devices on the field (fitted on=
 the=C2=A0 vehicles) communicate to the application backend via a public UR=
L :443=C2=A0 port=C2=A0 then=C2=A0 it connectes=C2=A0 to the 8080 of wildfl=
y then the java code connects the=C2=A0 database server running on 5432 on =
the internal LAN network.<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"></blockquote><div>=C2=A0</div><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex">
-- <br>
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A04=
8=C2=B001&#39;N 7=C2=B057&#39;E=C2=A0 =E2=80=94=C2=A0 <a href=3D"https://ww=
w.EnterpriseDB.com/" rel=3D"noreferrer" target=3D"_blank">https://www.Enter=
priseDB.com/</a><br>
Thou shalt study thy libraries and strive not to reinvent them without<br>
cause, that thy code may be short and readable and thy days pleasant<br>
and productive. (7th Commandment for C Programmers)<br>
</blockquote></div></div>

--0000000000003a2f9b0623e15a67--