Timeout waiting for idle object Dspace 4.1

[christian criollo]

Hi everybody

the last month, our repository is presenting faults like that org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object,  I modified dspace.cfg the variables max connection to 100, but the system still the same, I watch that  sessions in tomcat increase obstinately, i dont know whats wrong , please if somebody can tell me what can i do to fix this error, thanks for the help. 

[Alan Orth]

Hello,

Is your repository public? It could be that you are getting lots of traffic from search bots or people harvesting via REST / OAI... this would definitely increase the load on the server and create more database connections.

Ciao,

On Wed, May 24, 2017 at 11:23 PM christian criollo <ccrio...@gmail.com> wrote:

Hi everybody

the last month, our repository is presenting faults like that org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object,  I modified dspace.cfg the variables max connection to 100, but the system still the same, I watch that  sessions in tomcat increase obstinately, i dont know whats wrong , please if somebody can tell me what can i do to fix this error, thanks for the help. 

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

--

Alan Orth

alan...@gmail.com

https://picturingjordan.com

https://englishbulgaria.net

https://mjanja.ch

[christian criollo]

Hello Alan

yes the repository is public thanks for your answer

[Alan Orth]

Hello,

I've struggled with this in various forms over the seven years or so we've been running DSpace. High load on public servers can easily exhaust PostgreSQL connection slots. The easy answer is to increase the connection limits, but before that it's better to understand why the system load is increasing. Here are a few tips.

The easiest thing is to enable DSpace's XML sitemaps. Search engines like Google really hammer the repository as they crawl and click all sorts of dynamic links in the Browse and Discovery sidebar. Instead, you register your web property with Google Webmaster Tools and give them the path to your sitemap so they can get to each item directly without crawling haphazardly. Once you're sure Google is consuming your sitemap, you can block them from the dynamic pages in robots.txt. Here's the link on the wiki for DSpace 4:

https://wiki.duraspace.org/display/DSDOC4x/Search+Engine+Optimization

Second, look at your web server access logs. You might see many requests from bots like Bing, Yandex, Google, Slurp, etc, and notice they will all becoming from different IP addresses—sometimes from five or ten concurrently! Another place you might see this is in the "Current Activity" tab in the DSpace Admin UI control panel. The problem with this is that each of these connections creates a new Tomcat session, which consumes precious memory, CPU, and other resources. You can enable a Crawler Session Manager Valve in your Tomcat config which will tell Tomcat to make all user agents matching a certain pattern use a single session. There are some notes from me in the comments here:

https://wiki.duraspace.org/display/cmtygp/DCAT+Meeting+April+2017

And finally, in the last link is a discussion about updating the DSpace defaults for PostgreSQL connections from a recently developers meeting.

I hope that helps. Cheers,

[euler]

Hello Alan,

This thread is 3 years old now but our repositories are still experiencing the issues mentioned here. We are running DSpace 6.3 by the way. I've read from one of your CGSpace notes (https://alanorth.github.io/cgspace-notes/2018-11/) that when you encountered crawlers making a lot of requests from different IP addresses, you add them to your Tomcat's crawler session manager. I highly suspect that the cause of our repository 'hanging' is also because of this amount of requests from this crawlers (mostly from crawlers with user agent facebookexternalhit, Turnitin, and Unpaywall).

With regards to this, I hope you don't mind if you can share here the setting of your Tomcat's crawler session manager. Note that I have modified my postgresql.conf based from the discussion mentioned here: https://wiki.lyrasis.org/display/cmtygp/DCAT+Meeting+April+2017.

Thanks and hoping for your positive response,

euler

To unsubscribe from this group and stop receiving emails from it, send an email to dspac...@googlegroups.com.

To post to this group, send email to dspac...@googlegroups.com.
Visit this group at https://groups.google.com/group/dspace-tech.
For more options, visit https://groups.google.com/d/optout.

[Alan Orth]

Dear Euler,

It's a constant struggle. You absolutely have to get aggressive with non-human users. I have adopted a multi-faceted effort. I'm happy to share, everything of ours is in open GitHub repositories.

1. Tagging and throttling bad bots in nginx (which sits in front of Tomcat): https://github.com/ilri/rmg-ansible-public/blob/master/roles/dspace/templates/nginx/default.conf.j2

2. Force users with "bot" user agents to use the same JSESSION ID in Tomcat Crawler Session Manager Valve: https://github.com/ilri/rmg-ansible-public/blob/master/roles/dspace/templates/tomcat/server-tomcat7.xml.j2#L210

3. Update DSpace's builtin "spider" user agents lists so it doesn't record them in Solr stats (most of those come from COUNTER-Robots project): https://github.com/ilri/DSpace/tree/5_x-prod/dspace/config/spiders/agents

4. Aggressive PostgreSQL connection pooling in Tomcat JDBC (requires special configuration in Tomcat contexts as well): https://github.com/ilri/rmg-ansible-public/blob/master/roles/dspace/templates/tomcat/server-tomcat7.xml.j2#L50

This has made our site more stable, but like I said it's still a constant struggle. For a few months starting in 2020-04 or so we've had an increasing number of "waiting for lock" connections in both production 5.x and testing 6.x. I've tried upgrading PostgreSQL, upgrading the JDBC driver, downgrading Tomcat, etc. Nothing works except restarting Tomcat.

Would love to restart the discussion on all of this... BTW it helps if your systems have something like Munin configured that graphs the PostgreSQL connection status every five minutes. Helpful when some user says they couldn't log in or submit an item yesterday afternoon.

Regards,

--
All messages to this mailing list should adhere to the DuraSpace Code of Conduct: https://duraspace.org/about/policies/code-of-conduct/
---

You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/4990e2ca-88d7-453e-8f6f-9f859494637do%40googlegroups.com.

[euler]

Dear Alan,

Many thanks for your prompt response. Today, before I wrote my response to you, I restarted my Tomcat many times because of the "Cannot get a connection, pool error Timeout waiting for idle object" issue. I updated my Tomcat's crawler session manager valve based from your response here. Unfortunately, I cannot apply some of the methods you used because we are using Apache 2.4 in front of Tomcat and I don't know how to translate your Nginx configuration into Apache configuration. I also discovered that when I run 'dspace stats-util -u' to update the spider files, it resulted in java.lang.NullPointerException because the site iplists.com has been suspended.

I also modified the connection parameters by increasing the db.maxconnections, db.maxwait, and db.maxidle as suggested by Bram in the DCAT meeting that I mentioned earlier. I hope this would at least stabilize our repository for now.

Many thanks again, the methods you posted here is very valuable not only to me but also for others that may be experiencing the same issues with these crawlers.

Best regards,

Euler

To unsubscribe from this group and stop receiving emails from it, send an email to dspac...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/4990e2ca-88d7-453e-8f6f-9f859494637do%40googlegroups.com.

[Alan Orth]

Dear Euler,

Yes the iplists are long gone. In fact that should even be removed from the next 5.x and 6.x point releases. You can still update your spider *agent* lists though. Just download the one I linked, or generate one from here:

https://github.com/atmire/COUNTER-Robots

That will at least cause bot user agents to not get logged in Solr. Last year I removed 75 million (yes million) hits from our Solr statistics core by feeding this user agent list into a script I wrote:

https://github.com/ilri/DSpace/blob/5_x-prod/check-spider-hits.sh

Neither of those things will help with load directly, but will massively reduce memory and disk requirements. I cringe to think of how poorly configured most DSpace instances are.

The thing to remember with the default connection pool settings is that they are *per application*, meaning that XMLUI, REST, OAI, and any other application all use those settings to create their own pools! It is really worth looking into configuring a global pool with Tomcat JDBC.

BTW another thing I can recommend: block any request without a user agent!

Regards,

To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/584f648f-9027-4d7a-8409-cc59fe275f3fo%40googlegroups.com.