Thread: Connections Increasing Slowly

Hi folks.  My PostgreSQL server has a growing number of connections.

Currently it’s at 115.  max_connections set to 300.  It’s being accessed from public (web application), server cron
oncea day, and two clients on the LAN, a handful of times a day.   

I’m a bit surprised that the connection pool isn’t purged during this time.

 datid |   datname    | numbackends | xact_commit |          stats_reset
-------+--------------+-------------+-------------+-------------------------------
 16385 | my_db_name   |         115 |      128981 | 2020-05-17 06:48:18.191103-04

Is there any way I can troubleshoot this?  Is this normal?  I’m having connection issues in another client, so I’m
sniffingout connection issues and monitoring this number grow over days.   

Any help appreciated.


Cheers, Bee

"Bee.Lists" <bee.lists@gmail.com> writes:
> Hi folks.  My PostgreSQL server has a growing number of connections.
> Currently it’s at 115.  max_connections set to 300.  It’s being accessed from public (web application), server cron
oncea day, and two clients on the LAN, a handful of times a day.   

> I’m a bit surprised that the connection pool isn’t purged during this time.

Postgres doesn't really think that killing connections is part of its
charter.  (There is idle_in_transaction_session_timeout, but that's
there to guard against a specific performance issue, not to kill
non-misbehaving sessions.)

You should probably think about putting a connection pooler such as
pgbouncer in front of your server.  That's a better idea for lots of
low-resource-demand clients than giving them direct server connections.
And I think you're more likely to find features for killing idle
connections there, too.

Another idea, if you suspect that the idle connections are caused
by firewall timeouts or the like, is to enable more aggressive
TCP keepalive checking, to ensure the server notices if a client
isn't there at all anymore.  See the tcp_keepalives_* settings.

            regards, tom lane

> On Jun 22, 2020, at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Postgres doesn't really think that killing connections is part of its
> charter.  (There is idle_in_transaction_session_timeout, but that's
> there to guard against a specific performance issue, not to kill
> non-misbehaving sessions.)
>
> You should probably think about putting a connection pooler such as
> pgbouncer in front of your server.  That's a better idea for lots of
> low-resource-demand clients than giving them direct server connections.
> And I think you're more likely to find features for killing idle
> connections there, too.
>
> Another idea, if you suspect that the idle connections are caused
> by firewall timeouts or the like, is to enable more aggressive
> TCP keepalive checking, to ensure the server notices if a client
> isn't there at all anymore.  See the tcp_keepalives_* settings.
>
>             regards, tom lane

Hi Tom.

Who owns the actual connections?  The server allows them, the client requests them.  The error I am getting is that the
gemI’m using uses a connection that’s dropped.  Actually I should say the “connection pool”.  The client doesn’t (the
gemauthor doesn’t elaborate on any of this), and now you’re saying connections aren’t assumed by the database.   

It’s been recommended that pg_stat_statements and pg_stat_activity to see what’s happening.


Cheers, Bee

"Bee.Lists" <bee.lists@gmail.com> writes:
> On Jun 22, 2020, at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Another idea, if you suspect that the idle connections are caused
>> by firewall timeouts or the like, is to enable more aggressive
>> TCP keepalive checking, to ensure the server notices if a client
>> isn't there at all anymore.  See the tcp_keepalives_* settings.

> Who owns the actual connections?  The server allows them, the client
> requests them.  The error I am getting is that the gem I’m using uses a
> connection that’s dropped.

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.

            regards, tom lane

> On Jun 22, 2020, at 10:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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’m not clear
whoowns the connections or the connection pool, as the pool can be outdated.  There’s a check in the gem to check the
connectionpool for valid connections, over a set amount of time (it’s variable and I can change it, but it does add
overhead). I think that default is 60 minutes.   

The client (this gem) throws an error.  I don’t think any firewall is on this server that I know of.

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

I just set the tcp_keepalives_* to around 3, so we’ll see what’s 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 (‘mydatabase')

Cheers, Bee

On Jun 22, 2020, at 10:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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.
>
>             regards, tom lane

Hi Tom.  Some more testing.

Turned off firewalld, same issue.  Some notes:

I have one simple query that’s in my activity (currently active) that’s in there 5 times.  It should have closed very
quickly,yet there’s 5 instances idling.  I have two other queries, that should have operated quickly, then closed, but
theyare idling.   

tcp_keepalives* are around 3.

Any idea where to look now?

I’ve set my max_connections to 12 (4 cores * 3).


Cheers, Bee