>
> On Jun 23, 2020, at 4:51 PM, Michael Lewis <mlewis@xxxxxxxxxxx> 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
