AWS S3 Connection Pool Error

[jcda...@email.wm.edu]

I'm using S3 as a bitstore on DSpace 6 and I'm coming up with a lot of errors like the following:

com.amazonaws.http.AmazonHttpClient @ Unable to execute HTTP request: Timeout waiting for connection from pool
org.apache.http.conn.ConnectionPoolTimeoutException: Timeout waiting for connection from pool

It looks to me like either DSpace isn't closing the connections as it should.  Anyone else had this issue?

Thanks

[Claudia Jürgen]

Hello jcdalton,

which UI are you using and are you using google analytics?

For the time being you may set the config parameter db.maxidle to
something else than -1
see
https://jira.duraspace.org/browse/DS-3564?jql=text%20~%20%22maxidle%22

Hope this helps

Claudia Jürgen

--
Claudia Juergen
Eldorado

Technische Universität Dortmund
Universitätsbibliothek
Vogelpothsweg 76
44227 Dortmund

Tel.: +49 231-755 40 43
Fax: +49 231-755 40 32
claudia...@tu-dortmund.de
www.ub.tu-dortmund.de

Wichtiger Hinweis: Die Information in dieser E-Mail ist vertraulich. Sie ist ausschließlich für den Adressaten bestimmt. Sollten Sie nicht der für diese E-Mail bestimmte Adressat sein, unterrichten Sie bitte den Absender und vernichten Sie diese Mail. Vielen Dank.
Unbeschadet der Korrespondenz per E-Mail, sind unsere Erklärungen ausschließlich final rechtsverbindlich, wenn sie in herkömmlicher Schriftform (mit eigenhändiger Unterschrift) oder durch Übermittlung eines solchen Schriftstücks per Telefax erfolgen.

Important note: The information included in this e-mail is confidential. It is solely intended for the recipient. If you are not the intended recipient of this e-mail please contact the sender and delete this message. Thank you. Without prejudice of e-mail correspondence, our statements are only legally binding when they are made in the conventional written form (with personal signature) or when such documents are sent by fax.

[Peter Dietz]

Hi Justin,

With production use, and encountering similar issues to you, I have made fixes to our code to protect against http connection leaking with AWS S3 assetstore, and also to handle larger uploads/downloads. Here is some commit history to the (5.x) S3BitStore: https://github.com/LongsightGroup/DSpace/commits/longsight-5.6/dspace-api/src/main/java/org/dspace/storage/bitstore/impl/S3BitStore.java

________________
Peter Dietz
Longsight
www.longsight.com
pe...@longsight.com
p: 740-599-5005 x809

--
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+unsubscribe@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.

[jcda...@email.wm.edu]

I'm using XMLUI and I do have Google Analytics setup.  Does the db.maxidle configuration effect S3 connections?  I would have thought it would only effect Postgres connections.

Thanks

[Claudia Jürgen]

I'm not familiar with S3 but there hase been an issue reported for 5.4
with google analytics
https://jira.duraspace.org/browse/DS-2904

Claudia

>> claudia...@tu-dortmund.de <javascript:>

[jcda...@email.wm.edu]

Thanks Peter,

I have implemented most of your changes on the DSpace 6 code and now I'm not running out of connections.  I haven't updated to use the Transfer Manager yet and I was wondering what advantage you found using the Transfer Manager over the original implementation.

Thanks,
Justin

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

[Peter Dietz]

The TransferManager code just made the implementation simpler. Otherwise you have to write code that determines how large the bitstream is, and if its small, transfer the file to somewhere in memory, and then stream that, or if its larger, you could store it to temp file, and then stream that. You also have to ensure that you delete the temp file when complete. If its very large, larger than 5GB, you can't transfer that in a single GET. The TransferManager code handles the multipart uploads and download. Also, it can give a performance benefit, where GET and PUT can be done multi-thread multi-part, instead of a single long running GET or PUT to S3. The transfermanager also seemed to do a good job of closing resources when the transfer was complete.

http://docs.aws.amazon.com/AWSJavaSDK/latest/javadoc/com/amazonaws/services/s3/transfer/TransferManager.html

High level utility for managing transfers to Amazon S3.

TransferManager provides a simple API for uploading content to Amazon S3, and makes extensive use of Amazon S3 multipart uploads to achieve enhanced throughput, performance and reliability.

When possible, TransferManager attempts to use multiple threads to upload multiple parts of a single upload at once. When dealing with large content sizes and high bandwidth, this can have a significant increase on throughput.

TransferManager is responsible for managing resources such as connections and threads; share a single instance of TransferManager whenever possible. TransferManager, like all the client classes in the AWS SDK for Java, is thread safe. Call TransferManager.shutdownNow() to release the resources once the transfer is complete.

________________
Peter Dietz
Longsight
www.longsight.com
pe...@longsight.com
p: 740-599-5005 x809

To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech+unsubscribe@googlegroups.com.

[Jaime Solorzano]

Hi Peter

has made updates for version 6? Apparently it's a bit different from what you've posted.


I have the same problem with S3 in Dspace v.6 (pool Error)

Jaime

[Hayden Young]

This is been an ongoing concern for us and since we upgraded our infrastructure and DSpace the problem seems to have worsened so that we can predict with a fair amount of certainty that the problem will occur on one of our instances every 3 - 7 days.

The problem is not directly related to S3; it is actually a problem with connections to Postgres being left open which results in all connections being used up. We have made a number of adjustments to both DSpace's db config and to Postgres directly without success. Currently our solution is to clean up Postgres idle connections every few days on each of our DSpace instances:

SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = 'dspace'
      AND pid <> pg_backend_pid()
      AND state = 'idle'
      AND state_change < current_timestamp - INTERVAL '5' MINUTE;

Not the most ideal solution but it is keeping everything running without the need for a Tomcat restart.

Our longer term solution is to look at something like pgbouncer to manage the connections. Once implemented I will update on our findings.

Hope this helps.

[Hayden Young]

Unfortunately the Postgres statement for clearing idle connections does not work. We have again hit the connection pool error and running the psql terminate query did nothing to solve the problem.

I am now testing with the DSpace connection pool turned off:

db.statementpool = false

[Mark H. Wood]

You might want to consider moving the pool out of DSpace's control into the Servlet container -- see "Notes on PostgreSQL connection pooling with a Tomcat JNDI resource" by Alan Orth elsewhere in this group.  It would give you access to more of the pool's parameters (for testing connections before use, and the like) and also make it easy to use a newer driver.  I've had better luck with this arrangement.

I believe that db.statementpool affects pooling of prepared statements only.  Unless you've adjusted PostgreSQL, the default on its end is to do no statement pooling regardless of what the client requests.  Like most DBMS-centric webapp.s, DSpace can't operate without a connection pool.

[Nicholas Woodward]

Just out of curiosity, is this still an issue for folks on this thread? Were you able to resolve your issues by setting up a connection pool in Tomcat using Alan Orth's instructions? The reason I ask is that when we upgraded from 5.4 to 6.3 we also moved the assetstore into S3. And since then there's been an almost constant stream of errors like this in the logs.

com.amazonaws.http.AmazonHttpClient @ Unable to execute HTTP request: Timeout waiting for connection from pool
org.apache.http.conn.ConnectionPoolTimeoutException: Timeout waiting for connection from pool

When that happened I set up a connection pool using the instructions in the DSpace 6 installation docs - https://wiki.duraspace.org/display/DSDOC6x/Installing+DSpace#InstallingDSpace-Externaldatabaseconnectionpool. And I set it up with what I thought were sufficiently high values for the maximum total connections (200) and maximum idle connections (50).

But the new setup has been in place for a few days now, and we're still seeing connection pool timeouts on a daily basis. Even though the maximum total connections is set to 200, we never see near that many postgres processes at any given time.

Is there some additional configuration to the JNDI Datasource that would help our situation? Or were there other things people did to improve the performance? 

Thanks,

Nick

[Alan Orth]

Hey, Nick.

We ended up doing quite a bit more tuning in our connection pooling setup (some of it with input from Tom that original pooling thread). These are the settings we're using on our DSpace 5.x instance on Tomcat 7 (a few settings change on Tomcat 8.5, beware):

<Resource name="jdbc/dspaceWeb" auth="Container"
          type="javax.sql.DataSource"
          driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://localhost:5432/dspace?ApplicationName=dspaceWeb"
          username="dspace"
          password="dspace"
          initialSize='5'
          maxActive='250'
          maxIdle='15'
          minIdle='5'
          maxWait='5000'
          validationQuery='SELECT 1'
          removeAbandoned='true'
          removeAbandonedTimeout='180'
          abandonWhenPercentageFull='50'
          jdbcInterceptors='ResetAbandonedTimer'
          logAbandoned='true'
          testOnBorrow='true'
          testOnReturn='true' />

Our server gets a few million hits per month and we have not had any connection pooling errors since we implemented these in early 2018. The most important would probably be the ones for removing abandoned connections.

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 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
"In heaven all the interesting people are missing." ―Friedrich Nietzsche

[Nicholas Woodward]

Hi Alan,

Thanks for the response! I tried with the following config, and DSpace 6.3 was able to last about an hour before throwing pool connection errors.

    <Resource

        name='jdbc/instance'

        type='javax.sql.DataSource'

        auth='Container'

        username='dspace'

        password='dspace'

        driverClassName='org.postgresql.Driver'

        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

        url='jdbc:postgresql://localhost:5432/dspace'

        initialSize='10'

        maxActive='250'

        maxIdle='20'

        minIdle='10'

        maxWait='5000'

        validationQuery='SELECT 1'

        testOnBorrow='true'

        testOnConnect='true'

        testOnReturn='true'

        removeAbandoned='true'

        removeAbandonedTimeout='240'

        abandonWhenPercentageFull='50'

        jdbcInterceptors='ResetAbandonedTimer'

        logAbandoned='true'

        />

It's running on Tomcat 8.5, and I'm guessing that's the source of my problems. I think I have the right attribute names for 8.5 (https://tomcat.apache.org/tomcat-8.5-doc/jdbc-pool.html). Otherwise, I added the ResourceLink to each of the Contexts in /usr/share/tomcat8/conf/Catalina/localhost/oai.xml, rdf.xml, ROOT.xml, etc.

I haven't found in the tomcat docs yet if there are more setup changes I need to make to get it to work.

Thanks,

Nick

[Alan Orth]

Nick,

I just realized that you're using the org.apache.tomcat.jdbc.pool.DataSourceFactory factory with Tomcat 8.5, which means you get a Tomcat DBCP pool, while in my Tomcat 8.5 test environment I have not specified any factory, which means that I am using an Apache Commons DBCP 2.x pool. It's pretty confusing really, but as I understand it, Tomcat forked Apache Commons to address some issues with Commons DBCP 1.x. Apache Commons DBCP 2.x addressed all the original issues and is now safe to use, but I couldn't tell you if it's better or worse than Tomcat's own DBCP anymore!

Here's my Tomcat 8.5.x config that "works" (as in DSpace loads, runs, etc), but keep in mind we're using DSpace 5.x and this is only running in my test environment:

<Resource name="jdbc/dspaceWeb" auth="Container"
          type="javax.sql.DataSource"
          driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://localhost:5432/dspace?ApplicationName=dspaceWeb"
          username="dspace"
          password="dspace"
          initialSize='5'

          maxTotal=200
          maxIdle='15'
          minIdle='5'
          maxWaitMillis='5000'
          validationQuery='SELECT 1'
          removeAbandonedOnBorrow='true'
          removeAbandonedTimeout='180'

          logAbandoned='true'
          testOnBorrow='true'
          testOnReturn='true' />

Note that some of the configuration option names have changed.

Good luck!

[Nicholas Woodward]

Alan,

Thanks for your help. We have a config for 8.5 very similar to that which works for a few of our DSpace 6 instances that have fewer items and get less traffic. On our other instances that are medium size or larger and get more traffic the pool timeout is still reached about every few hours. So we moved all of them back to a local assetstore. We'll keep S3 in mind for future releases, though. Thanks, again.

Nick