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 On Saturday, September 29, 2018 at 7:58:18 AM UTC-5, Alan Orth wrote: > > 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! > > On Fri, Sep 28, 2018 at 7:15 PM Nicholas Woodward <woodward.n...@gmail.com > <javascript:>> wrote: > >> 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 >> >> >> On Friday, September 28, 2018 at 9:18:27 AM UTC-5, Alan Orth wrote: >>> >>> 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, >>> >>> On Wed, Sep 26, 2018 at 10:22 PM Nicholas Woodward < >>> woodward.n...@gmail.com> wrote: >>> >>>> 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 >>>> >>>> >>>> >>>> >>>> On Wednesday, January 3, 2018 at 3:29:12 PM UTC-6, Mark H. Wood wrote: >>>>> >>>>> 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. >>>>> >>>> -- >>>> 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 >>> >> -- >> 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 <javascript:>. >> To post to this group, send email to dspac...@googlegroups.com >> <javascript:>. >> 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 <javascript:> > https://picturingjordan.com > https://englishbulgaria.net > https://mjanja.ch > "In heaven all the interesting people are missing." ―Friedrich Nietzsche > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to dspace-tech@googlegroups.com. Visit this group at https://groups.google.com/group/dspace-tech. For more options, visit https://groups.google.com/d/optout.
