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