BookmarkSubscribeRSS Feed
goitzy
Calcite | Level 5

Anyone have issues with conections left open? When a process failes there are left over process still active from time to time that you cant match to the corresponding folder in the work folder. However there a ton of posgreSqlServer processes that are left running that i can not identify thier source and if they are orphaned processes.

 

I am hoping someone can share how they deal with these orphaned connections.

 

thanks,
Lane

7 REPLIES 7
JuanS_OCS
Amethyst | Level 16

Hello Lane, @goitzy,

 

I would need some additional information. Some questions that raise on my mind are:

  • Your PostgreSQL database is the WIP database from SAS, or an external PostgreSQL database that you are connecting with SAS/ACCESS to PostreSQL or SAS/ACCESS to ODBC?
  • And the open connections you mention, ydo you see them within the administration view within PostgreSQL/pgAdmin, or in the Operating System?

 

The last question, I am dropping it because one of the characteristics of PostreSQL, and some people get consused because of it, is that this is a multi-threaded database, with a pool of connections reflected on several running psql processes, which is the expected behaviour on a multi-threaded database.

goitzy
Calcite | Level 5

WIP database from SAS or SAS/ACCESS to ODBC (SQLServer) I am guessing.

 

We do not have any PostdreSQL in house so it would be all native to SAS.

 

I see the conections via OS task manager.

 

It could be connections for multithreaded application but the list never shrinks so processes leave either orphans or never close.

JuanS_OCS
Amethyst | Level 16

Hello @goitzy,

 

in that case, that is perfectly normal. From the PostreSQL's manual ( https://www.postgresql.org/docs/current/static/tutorial-arch.html 😞

 

The PostgreSQL server can handle multiple concurrent connections from clients. 
To achieve this it starts ("forks") a new process for each connection.
From that point on, the client and the new server process communicate without intervention by the original postgres process.
Thus, the master server process is always running, waiting for client connections, whereas client and associated server processes come and go.
goitzy
Calcite | Level 5

I find it disturbing that i have 153 prostgres connections using on average 2,644 k of memory per connection running at the moment. This is a waste of resources in my opinion and there seems to be no recycling of these connections.

 

 

dpage
SAS Employee

The Web Application Servers have a connection pool they allocate and re-use connections from, is it possible that's what you're seeing? They should clean up when you shut down the web application server though. The way the postgres server works, you'll see a separate process created for each incoming connection, so they may appear orphaned on the database server side, but the java process that's using them is still running. 

goitzy
Calcite | Level 5
I am seeing these connections on the Compute server and the environment that im using does not have any users currently in it (staging) but we are running data loads via odbc using DI Studio. I would imaging a reboot would take care of most of them but knowing the reason they are they are persistent would make me feel better
dpage
SAS Employee

Can you confirm the other endpoint of the connection? The database may be on the compute tier, but the initiant may be on another machine (so you'd see no corresponding local process for it besides the postgres process) The Web Application server keeps a pool of connections open at all times, user activity or not. I think if you shut the web application server down as an experiment you'd see them go away, but if that's what you're seeing, it's expected. 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 2203 views
  • 2 likes
  • 3 in conversation