Persistent Connections
I have an issue with a server (v10) that’s seeing increasing connections until it’s maxxed-out. max_connections for my 4-core server is set to 12. I’ve installed pg_stat_activity and pg_stat_statements. I access this server through a web app, and another client on two machines. I also send queries through SSH on the LAN. psql queries indicate there are too many clients already. I’ve been getting errors from my workstation through a Ruby gem that says dropped connections happen. Essentially, the server isn’t giving up connections and clients from all sides are showing some issues. pg_stat_activity has shown a handful of idle queries that are quite simple. I’ve tested those queries and they seem fine on a workstation client. I’m assuming these queries somehow don’t finish and leave the connection open, but I could be wrong. All of this is new to me. It was suggested on Slack that it sounds like my workstation had some TCP issues with these connections, and that it was a firewall. I disabled the firewall and the same happened. The firewall has been restarted. I am running no connection pool tool of any sort, and I’ve never used one. At this point I don’t know what to even be watching for. Connections increase and connections “drop”, according to the gem I’m using. I have simple queries that are idle and won’t disappear for some reason. How can I figure this out so the connections remain within the max_connections limit, and connections are not dropped? Any insight appreciated. Cheers, Bee
Re: Persistent Connections
>
> On Jun 23, 2020, at 4:51 PM, Michael Lewis 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
Re: Persistent Connections
> On Jun 23, 2020, at 8:09 PM, Tim Cross 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. For some clarity, the gem is Sequel, which is on top of the PG gem (Ruby). I’ve spoken to the Sequel author and he says everything is fine. I have some evidence it’s a connection issue and the gem is doing its job, as I’m seeing it’s happening elsewhere with crontabs and other clients. > Typically, due to the overhead of making a connection, you don't want > your client app/web app to create a new connection for every query. > Instead, you would use some type of connection pool. Many development > languages support some form of pooling as part of their db connection > library (don't know about Ruby, but JS, Python, PHP, Java, Perl all do) > and there are external 3rd party solutions like pgbouncer which sit > between your client and the database and work a bit like a connection > broker which will manage a pool of connections. That’s why I’m thinking installing a connection pooler would solve all of this. pgbouncer is what I’m looking at now. > From the description of what your doing, I would first look to see what > level of connection pooling your development language supports. This > will likely be easier to configure and use than setting up a whole > additional bit of infrastructure which is probably far more powerful > than you need. I will do that. This is all new. > I would also go through your code and make sure that every time you > create a database connection, there is some code which is closing that > connection once your finished with it. This is a frequent source of > problems, especially during development when your code might throw an > error and the code you have to disconnect does not get executed or you > simply forget to issue a disconnect when your finished. 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. It’s a DSL so the workings aren’t evident when making queries. It was suggested I install a logger to see what and when queries are actually made. > 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. So installing a connection pooler seems the right thing to do. Good to know about the reservations. That is better feedback. Cheers, Bee
Re: Persistent Connections
> On Jun 24, 2020, at 6:47 AM, Peter J. Holzer wrote: > > The default is 100. What was your reason for reducing it to such a low > value? “PostgreSQL 9 High Availability” recommended core count * 3. > >> I’ve installed pg_stat_activity and pg_stat_statements. >> >> I access this server through a web app, > > So that's probably a handful connections already. Looks like 5 queries. As a comparison I have 37 queries asking for last login of a single user. No errors. (I increased the max_connections yesterday) >> and another client on two machines. > > Maybe two or more (depending on what the non-specified client does). I’m running crons on two machines, Postico, web apps on both machines. >> I also send queries through SSH on the LAN. > > And maybe some more connections. Yep. > I can see that this could easily reach 12 connections. Yep. But I can’t see all this going over 12 when connections close. Hence the confusion and issue. At least it’s a good thing I’m finding it’s not the gem alone. Cheers, Bee
