Re: Persistent Connections - Mailing list pgsql-general
| From | David G. Johnston |
|---|---|
| Subject | Re: Persistent Connections |
| Date | |
| Msg-id | CAKFQuwY0_ATTFbSYODBvt1r+gNyO+x=ARf+ouF9ZsYZMAr+uZQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Persistent Connections ("Bee.Lists" <bee.lists@gmail.com>) |
| List | pgsql-general |
Why is there now a second thread for this topic?
On Tue, Jun 23, 2020 at 3:21 PM Bee.Lists <bee.lists@gmail.com> wrote:
>
> 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 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?
"they (queries) are still working, but idle" - your terminology is problematic and it is probably affecting your understanding. As I said on the other thread you should probably post the actual output you are commenting on if you want to actually move this discussion forward.
> 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?
As I asked on the other thread: a connection is a link between two parties. What does it mean to "belong to" in this context? You have mis-interpreted Tom's answer from the other thread.
> 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.
Until you get a better grasp of the basics you should not be introducing any more moving parts. If anything you need to remove some in order to figure out which one of the existing parts is causing your problem.
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.
Not sure how you expect an answer to "how do I fix the problem" without an understanding of what the problem is.
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.
That the pooler in your application is being lied to is probably the most likely answer, as Tom said in the other thread. But the rest of what you are saying here just sounds like nonsense. "I'll be looking into a pooler. ... The gem has a procedure to check how long since the last pool was investigated for legit connections ...". I'd like to point out that you seem to be saying that you simultaneously have a connection pool and don't have a connection pool involved here...
As I suggested on the other thread, and repeated above, you need to build up a script that can reproduce the problem. Something that can open a connection to the server and then wait for a period of time before executing a query against it to see if that session gets dropped on the client side while still remaining visible on the server.
David J.
pgsql-general by date: