postgres max_connections reached

[Sanket Gupte]

Got error
"

psql: FATAL: remaining connection slots are reserved for non-replication superuser connections

"
So, I have three questions now.

1. Is max_connections=100   , okay for a moderately used xnat system.
2. I have been running it for the past 2 years, I had never reached the limit. Only 2-3 people toyed (simple archiving and here and there) since the past 3 days, but it reached the max_connections limit. Kinda curious why that would happen.
3. Does xnat have a mechanism to kill the idle connections ??? what is the threshold ? As in, say connections idle for more than 1 hour get killed ? if not, I guess I'll probably have a cron job to do that, to avoid such a thing happening again.

Thanks :)

[George Kowalski]

Did you check the InstanceSettings.xml file in the ~tomcat/webapps/xnat/WEB-INF/conf directory. If this has not been modified recently then  the java jdk / postgres were recently upgraded and introduced a bug your running into. 

[Sanket Gupte]

I did not upgrade java or postgres.
InstanceSettings was modified recently. i added a module. Would that affect ?

[Tim Olsen]

What version of XNAT are you using?  1.6.5 will include Hibernate caching which will dramatically reduce the number of sql statements being run.

 

1. 100 should be more then adequate.  One artififact of XNAT development is that it actually maintains 2 separate connection pools (one for older XNAT code defined in InstanceSettings.xml, and one for newer Hibernate based code defined in services.properties).  Some day will get back to consolidate the two.  So, when figuring out the number of connections XNAT may use, it is the addition of those two configurations.   But, I’ve never really needed to modify any of the default connection settings (XNAT or postgres) except in situations where I was specifically doing load testing and through huge data at it.

2. Not sure why the timing was now.  The archiving process is one of the most expensive operations on the XNAT server.  Especially when you add in the AutoRun pipeline which remotely builds and uploads jpeg snapshots for each scans.    But, even with that in mind, it seems weird that it would max out on that.  Is there something else on this server that may be using postgresql.  Even open psql or PgAdmin connections.

3. XNAT uses connection pooling to manage its database connections.  Once a connection is open it will stay open and be reused (connection startup time is actually quite expensive over the course of the application).   So, No, XNAT will not close connections when it is done with them.  They will remain open (and should remain open) until tomcat is restarted.

--
You received this message because you are subscribed to the Google Groups "xnat_discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xnat_discussi...@googlegroups.com.
To post to this group, send email to xnat_di...@googlegroups.com.
Visit this group at http://groups.google.com/group/xnat_discussion.
For more options, visit https://groups.google.com/d/optout.

[Sanket Gupte]

I upgraded to Xnat 1.6.4dev from mid july, which might as well be the 1.6.5 then ???

Oh connection pooling. So, yeah that's good. I'll wait a little before I make a script to kill idle connections. May be it may not happen again. Let's hope.

[Herrick, Rick]

Try running this query in psql:

 

select* from pg_stat_activity;

 

You can also try:

 

select distinct(usename) from pg_stat_activity;

 

What you want to see if someone else has started taking connections from the database. There’s really no way that XNAT should run out of connections with only 2 or 3 people connected.

--

You received this message because you are subscribed to the Google Groups "xnat_discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xnat_discussi...@googlegroups.com.
To post to this group, send email to xnat_di...@googlegroups.com.
Visit this group at http://groups.google.com/group/xnat_discussion.
For more options, visit https://groups.google.com/d/optout.

---

The material in this message is private and may contain Protected Healthcare Information (PHI). If you are not the intended recipient, be advised that any unauthorized use, disclosure, copying or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this email in error, please immediately notify the sender via telephone or return mail.

[Sanket Gupte]

I wanted to run the update script and didnt have any free connections, so I had to restart tomcat in a hurry to free up the connections.  Should have checked all the connections and what all was taking them up, It's running smoothly since then though. I had looked at pg_stat_activity after that, and even now. It looks quite good.
With connection pooling, I don't see how I could run out of available connections if xnat is the only thing using it. I am fairly certain there is nothing else that could or even has access to postgres. strange.
O well. Will wait for it to happen again.
Thanks

[Sanket Gupte]

There is seriously something wrong, and I don't know what.
Got it again.

"

psql: FATAL: remaining connection slots are reserved for non-replication superuser connections

"

I am like so stuck, that I have to reboot the machine to kill the useless connections. Rebooting tomcat/apache only frees up a few connection for the time being. But it happens again. So, I reboot the machine, and I am good to go for a few weeks.

And it's all xnat.
So now after rebooting the webserver, "select* from pg_stat_activity;"   Gives about 99 rows, all xnat user,
And "select distinct(usename) from pg_stat_activity;"  gives 1 row "xnat"

Any ideas ?