Re: Persistent Connections

On 2020-06-24 13:45:21 -0400, Bee.Lists wrote:
> > Sounds like your web app may not be closing connections once it has
> > finished with them? The fact your seeing idle connections would seem to
> > support this. I would be verifying there isn't a code path in your
> > client application which is failing to close a connection correctly. Seeing
> > connections go up and down in the gem may not be telling you the full story - could
> > be that your client connection objects are being destroyed in your app,
> > but are not closing the connection correctly so PG is unaware the client
> > has finished.
>
> Hi Tim. I can’t speak for the gem. I’m assuming its garbage
> collection is working. But yes, it does look that way. I found
> someone else who was having similar issues as myself:
>
> https://stackoverflow.com/questions/60843123/djangopostgres-fatal-sorry-too-many-clients-already
>
> I’m also seeing the connection count rise overnight from crontabs.

Unlike a web-server which runs continuously and can therefore keep a
connection open indefinitely, a cron job normally has a finite live-time.

So if the number of connections from your cron jobs keeps rising that
means that your cron jobs don't finish (or at least some of them). You
should investigate why they are stuck.

> The issue here is that it’s all in the gem. The gem is actually an
> ORM, built on top of the PG gem. So all the database connection stuff
> is inside there. It’s so abstracted that I don’t know when the actual
> calls are made.

Yeah, that's one reason why I'm not overly fond of ORMs.

> It was suggested I install a logger to see what and when queries are
> actually made.

That would probably help. Alternatively you can tell PostgreSQL to log
that stuff.

> > A connection pool can help in tracking down such issues as well. Most
> > pooling solutions will allow you to set a max pool size. In addition to
> > enabling you to 'reserve' a set number of connections for a client, you
> > will know which client seems to be running out of connections, helping
> > to identify the culprit.
>
> That SO link above suggests I’m not alone.

Well, yes. Just about everyone who has ever run a web-application
against a database has run into this problem. It's part of the learning
process :-). What you should learn from this is how to analyze the
situation and how to mitigate it. The solution might be a setting in the
webserver or gem configuration. It might be a connection pooler. It
even be resetting max_connections to the default and not caring about a
few idle connections.

What you shouldn't learn from this is that a pooler will make your
problems magically go away. Because it won't.

jp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"