Re: Persistent Connections

>
> On Jun 23, 2020, at 4:51 PM, Michael Lewis <mlewis(at)entrata(dot)com> wrote:
>
> Do you see anything in pg_stat_activity that stays idle for a while and then *does* disappear on its own? Perhaps some types of connections are doing client side/application stuff before telling the DB to close the connection.

I’m finding those queries sticking around. These queries are very simple. Last login type of stuff.

> Idle means the query finished and that was the last query run. It isn't active or waiting on another process, that connection is open by idle.

OK. The page that I load up is a dashboard and has a handful of queries. From the looks of it, it looks like they’re still working, but idle. But you’re saying they’re just open connections? Why would they remain open?

I check for numbackends this way:

pgconns='psql -c "select datid, datname, numbackends, xact_commit, stats_reset from pg_stat_database where datname in ('\’'mydbname'\'');”'

> It sounds like a good time to set one up.

OK, some further questions:

Who do the connections belong to? Not the client, not the server (apparently). Is there one that’s independent and behaves as the front end of connection management?

> I would increase the limit directly, or with a pooler and research which connections are behaving, and which are taking too long to close or not closing at all. You could set up a process to snapshot pg_stat_activity every minute or 5 and trace which pids are terminating properly, and/or make logging very verbose.

How do I go about researching connection behaviour? I guess a pooler should be investigated first. I have that pgconns already logging, so I’ll do one for pg_stat_activity.

Once I find culprits, what options do I have? Not sure why new connections are made when these idle past connections seem valid and usable.

There is agreement that ORMs shouldn’t be managing a connection pool, and this doesn’t achieve to do that. I’ll be looking into a pooler. This client (the gem is Sequel, btw) uses what it assumes are valid connections, but that’s where it fails as the database apparently disconnects prematurely. The gem has a procedure to check how long since the last pool was investigated for legit connections, but I think that’s irrelevant. It’s finding what it’s told are legit connections, which are not. It’s been lied to.

Cheers, Bee