>
> On Jun 23, 2020, at 4:51 PM, Michael Lewis <mlewis@entrata.com> wrote:
>
> Do you see anything in pg_stat_activity that stays idle for a while and then *does* disappear on its own? Perhaps
sometypes 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
connectionis 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
stillworking, 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
behavesas 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
takingtoo long to close or not closing at all. You could set up a process to snapshot pg_stat_activity every minute or
5and 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
pgconnsalready 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
seemvalid 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
lookinginto a pooler. This client (the gem is Sequel, btw) uses what it assumes are valid connections, but that’s
whereit fails as the database apparently disconnects prematurely. The gem has a procedure to check how long since the
lastpool 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
