Hard-coded database connection pool size too small, requests failing

[abought]

Problem

During periods of high traffic, users are randomly unable to connect to the website. Logs trace this to a connection pooling problem: although our database can support hundreds of simultaneous connections (our default value: 304), the pool size is capped at 10.

This results in elevated 500 errors, interrupted job uploads, and generally broken stuff. As the site gets more traffic, this becomes more common.

Proposed solution(s)

1. Make maxActive connection pool size configurable when the database connection is first opened
2. ...or at least increase the default value (based on metrics, 20-50 open connections would be sufficient, -1 for unlimited)
3. (fixed, release is pending) Database indexing PR should reduce the time spent on each request, allowing open connections to be re-used sooner
4. TIS will be increasing the time between "job status" refresh from 5 -> 20 sec in the next release. This should cut the load from our most common server request by ~75%.

Root cause

Many types of API endpoint require a database connection. A small pool of connections is re-used across all requests. If the pool is full, a request will time out without having a database connection. Ours logs have many stacktraces like the following; the error can be traced to the maxActive setting of dbcp. (renamed to maxTotal in newer versions)

2024-03-08 23:10:06,057 [qtp911501858-581359] ERROR cloudgene.mapred.database.DownloadDao - update download failed.
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:114) ~[commons-dbcp-1.4.jar:1.4]
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044) ~[commons-dbcp-1.4.jar:1.4]
at org.apache.commons.dbutils.AbstractQueryRunner.prepareConnection(AbstractQueryRunner.java:319) ~[commons-dbutils-1.7.jar:1.7]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:495) ~[commons-dbutils-1.7.jar:1.7]
at cloudgene.mapred.database.util.JdbcDataAccessObject.update(JdbcDataAccessObject.java:93) ~[cloudgene.jar:?]
at cloudgene.mapred.database.DownloadDao.update(DownloadDao.java:63) ~[cloudgene.jar:?]
at cloudgene.mapred.api.v2.jobs.ShareResults.get(ShareResults.java:48) ~[cloudgene.jar:?]
at sun.reflect.GeneratedMethodAccessor210.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_402]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_402]
at org.restlet.resource.ServerResource.doHandle(ServerResource.java:511) ~[org.restlet-2.3.12.jar:?]
at org.restlet.resource.ServerResource.get(ServerResource.java:723) ~[org.restlet-2.3.12.jar:?]
at org.restlet.resource.ServerResource.doHandle(ServerResource.java:603) ~[org.restlet-2.3.12.jar:?]
at org.restlet.resource.ServerResource.doNegotiatedHandle(ServerResource.java:662) ~[org.restlet-2.3.12.jar:?]
at org.restlet.resource.ServerResource.doConditionalHandle(ServerResource.java:348) ~[org.restlet-2.3.12.jar:?]
at org.restlet.resource.ServerResource.handle(ServerResource.java:1020) ~[org.restlet-2.3.12.jar:?]
at org.restlet.resource.Finder.handle(Finder.java:236) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.handle(Filter.java:197) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Router.doHandle(Router.java:422) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Router.handle(Router.java:641) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.handle(Filter.java:197) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.handle(Filter.java:197) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[org.restlet-2.3.12.jar:?]
at org.restlet.engine.application.StatusFilter.doHandle(StatusFilter.java:140) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.handle(Filter.java:197) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.handle(Filter.java:197) ~[org.restlet-2.3.12.jar:?]
at org.restlet.engine.CompositeHelper.handle(CompositeHelper.java:202) ~[org.restlet-2.3.12.jar:?]
at org.restlet.engine.application.ApplicationHelper.handle(ApplicationHelper.java:77) ~[org.restlet-2.3.12.jar:?]
at org.restlet.Application.handle(Application.java:385) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.handle(Filter.java:197) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Router.doHandle(Router.java:422) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Router.handle(Router.java:641) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.handle(Filter.java:197) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Router.doHandle(Router.java:422) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Router.handle(Router.java:641) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[org.restlet-2.3.12.jar:?]
at org.restlet.engine.application.StatusFilter.doHandle(StatusFilter.java:140) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.handle(Filter.java:197) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.doHandle(Filter.java:150) ~[org.restlet-2.3.12.jar:?]
at org.restlet.routing.Filter.handle(Filter.java:197) ~[org.restlet-2.3.12.jar:?]
at org.restlet.engine.CompositeHelper.handle(CompositeHelper.java:202) ~[org.restlet-2.3.12.jar:?]
at org.restlet.Component.handle(Component.java:408) ~[org.restlet-2.3.12.jar:?]
at org.restlet.Server.handle(Server.java:507) ~[org.restlet-2.3.12.jar:?]
at org.restlet.engine.connector.ServerHelper.handle(ServerHelper.java:63) ~[org.restlet-2.3.12.jar:?]
at org.restlet.engine.adapter.HttpServerHelper.handle(HttpServerHelper.java:143) ~[org.restlet-2.3.12.jar:?]
at org.restlet.ext.jetty.JettyServerHelper$WrappedServer.handle(JettyServerHelper.java:256) ~[org.restlet.ext.jetty-2.3.12.jar:?]
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311) ~[jetty-server-9.2.14.v20151106.jar:9.2.14.v20151106]
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257) ~[jetty-server-9.2.14.v20151106.jar:9.2.14.v20151106]
at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544) ~[jetty-io-9.2.14.v20151106.jar:9.2.14.v20151106]
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635) ~[jetty-util-9.2.14.v20151106.jar:9.2.14.v20151106]
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555) ~[jetty-util-9.2.14.v20151106.jar:9.2.14.v20151106]
at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_402]
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1134) ~[commons-pool-1.5.4.jar:1.5.4]
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106) ~[commons-dbcp-1.4.jar:1.4]
... 57 more

[abought]

UM fork will be piloting maxActive = -1 in our next fork release, with server performance metrics to evaluate realistic load.