Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Subject: Re: Connections Increasing Slowly
From: "Bee.Lists" <bee.lists@gmail.com>
In-Reply-To: <2114531.1592878045@sss.pgh.pa.us>
Date: Mon, 22 Jun 2020 22:32:56 -0400
Cc: Submit Postgresql Novice <pgsql-novice@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <889C9020-88AF-4254-B9D2-D515C4298DAD@gmail.com>
References: <8A1234E2-D4C3-451F-8F60-901D0D5E0CA5@gmail.com>
 <2088230.1592856708@sss.pgh.pa.us>
 <0345AEA7-346C-452F-B3AE-13ED57818F5F@gmail.com>
 <2114531.1592878045@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk


> On Jun 22, 2020, at 10:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> Hmm.  If the client thinks the server dropped the connection, but the
> server thinks the connection is still live, that smells strongly of
> network-level disconnects.  Does the client let the connection sit =
idle
> for more than a minute or two at a time?  If so I'd bet that some =
firewall
> in between is deciding that the connection is dead and dropping it.
> Again, setting the server's tcp_keepalives_* settings could help fix =
that,
> by ensuring that the network connection sees some traffic regularly.

The gem says it used something in the connection pool.  Then it says the =
server dropped it.  Like I said, I=E2=80=99m not clear who owns the =
connections or the connection pool, as the pool can be outdated.  =
There=E2=80=99s a check in the gem to check the connection pool for =
valid connections, over a set amount of time (it=E2=80=99s variable and =
I can change it, but it does add overhead).  I think that default is 60 =
minutes. =20

The client (this gem) throws an error.  I don=E2=80=99t think any =
firewall is on this server that I know of. =20

Note: This error shows up first thing in the morning sometimes.  I=E2=80=99=
ve not had it in about a week now.  I have reduced the max_connections =
to 30, and the connections are now at 4.  I have a bunch of queries on =
the top page (it=E2=80=99s a dashboard) and it just increased from 2 to =
4.

I just set the tcp_keepalives_* to around 3, so we=E2=80=99ll see =
what=E2=80=99s going on.  Another page request has connections up to 8:

select datid, datname, numbackends, xact_commit, stats_reset from =
pg_stat_database where datname in (=E2=80=98mydatabase')

Cheers, Bee