JDBC connection pool compatible with App Engine

Question

Note: I know about this thread but it is quite old and moreover, the solution did not work for me.

I am using App Engine along with Cloud SQL and I would like to share a pool of open connections between all of the application's current users. I have tried several connection pool implementations and they all work perfectly with the local development server, however, when deployed to the cloud, they fail. I suppose that the reason is App Engine's restricted "sandbox" environment. Does anyone know about JDBC connection pool working on App Engine?

Apache Commons DBCP

...
Caused by: java.lang.NoClassDefFoundError: Could not initialize class org.apache.commons.dbcp2.PoolableConnection
at com.google.appengine.runtime.Request.process-a49d46300800d0ca(Request.java)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:254)
at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2162)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2148)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:1903)
at org.apache.commons.dbcp2.BasicDataSource$PaGetConnection.run(BasicDataSource.java:2267)
at org.apache.commons.dbcp2.BasicDataSource$PaGetConnection.run(BasicDataSource.java:2263)
at java.security.AccessController.doPrivileged(AccessController.java:63)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1404)
...

Tomcat JDBC Connection Pool

...
Caused by: java.lang.SecurityException: Unable to get members for class org.apache.tomcat.jdbc.pool.DataSource

...

Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
... 45 more
Caused by: java.lang.NoClassDefFoundError: javax/management/MalformedObjectNameException
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2517)
... 45 more
Caused by: java.lang.ClassNotFoundException: javax.management.MalformedObjectNameException
... 45 more

HikariCP

...
java.security.AccessControlException: access denied ("java.lang.RuntimePermission" "modifyThreadGroup")
at java.security.AccessControlContext.checkPermission(AccessControlContext.java:375)
at java.security.AccessController.checkPermission(AccessController.java:565)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at java.lang.ThreadGroup.checkAccess(ThreadGroup.java:315)
at java.lang.Thread.init(Thread.java:378)
at java.lang.Thread.<init>(Thread.java:527)
at com.zaxxer.hikari.util.DefaultThreadFactory.newThread(DefaultThreadFactory.java:32)
at java.util.concurrent.ThreadPoolExecutor$Worker.<init>(ThreadPoolExecutor.java:591)
at java.util.concurrent.ThreadPoolExecutor.addWorker(ThreadPoolExecutor.java:922)
at java.util.concurrent.ThreadPoolExecutor.ensurePrestart(ThreadPoolExecutor.java:1591)
at java.util.concurrent.ScheduledThreadPoolExecutor.delayedExecute(ScheduledThreadPoolExecutor.java:305)
at java.util.concurrent.ScheduledThreadPoolExecutor.scheduleAtFixedRate(ScheduledThreadPoolExecutor.java:542)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:161)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:114)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:102)
...

Vibur DBCP

...
Caused by: java.security.AccessControlException: access denied ("java.lang.RuntimePermission" "modifyThreadGroup")
at java.security.AccessControlContext.checkPermission(AccessControlContext.java:375)
at java.security.AccessController.checkPermission(AccessController.java:565)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at java.lang.ThreadGroup.checkAccess(ThreadGroup.java:315)
at java.lang.Thread.init(Thread.java:378)
at java.lang.Thread.<init>(Thread.java:448)
at org.vibur.objectpool.reducer.SamplingPoolReducer.<init>(SamplingPoolReducer.java:78)
at org.vibur.dbcp.pool.PoolOperations$PoolReducer.<init>(PoolOperations.java:88)
at org.vibur.dbcp.pool.PoolOperations$PoolReducer.<init>(PoolOperations.java:86)
at org.vibur.dbcp.pool.PoolOperations.<init>(PoolOperations.java:79)
at org.vibur.dbcp.ViburDBCPDataSource.start(ViburDBCPDataSource.java:197)
....

c3p0

...
Caused by: java.security.AccessControlException: access denied ("java.lang.RuntimePermission" "modifyThreadGroup")
at java.security.AccessControlContext.checkPermission(AccessControlContext.java:375)
at java.security.AccessController.checkPermission(AccessController.java:565)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at java.lang.ThreadGroup.checkAccess(ThreadGroup.java:315)
at java.lang.Thread.init(Thread.java:378)
at java.lang.Thread.<init>(Thread.java:487)
...

Diastema

Seen on some other stackoverflow questions that HikariCP supports custom ThreadManager, so you can create threads using GAE's whitelisted ThreadManager. – Stockish

Answer 1

I had to use Tomcat DBCP 1.4 (older version), because of the GAE frontend not allowing threads to live outside of request scope. Here's an example project: https://github.com/kennberg/appengine-java-connection-pool

Note that the connection pool is necessary once you have too many requests, because there is a limit on number of pending connections per instance. Re-using connections helps stay under the limit.

Selfeffacing

Thanks Alex, I was using 2.x version and just cant figure out what's the reason. Downgraded it to 1.4 and it worked like charm. – Collagen

Answer 2

You probably don't need connection pooling at all:

https://cloud.google.com/sql/faq#connections

... if the time to create a new connection is about the same as testing if an existing connection is alive and reusing it, then we recommend that you create a new connection to service each HTTP request, and reuse it for the duration of the request. In particular, the latter case may apply when you connect from Google App Engine to Google Cloud SQL.

Aleda

Yes, I know about it. However, each App Engine instance cannot have more than 12 concurrent connections to a Cloud SQL instance and that is another reason to have a connection pool since it keeps track of active connections and if there is none available, it blocks until one is available or times out. Otherwise I would end up with an error. – Diastema

As far as I know Java GAE by default only sends 10 concurrent requests to your instance anyway, so 12 concurrent db connections should be fine. – Aleda

A potential problem is that google has a daily quota of socket connections an app can use. Without pooling it becomes easier to hit that limit, and suddenly your app can't connect to the database. The limit is very high so you might never hit it, but if you do the effects are noticeable. – Heterogamete

How to best manage your database connections depends on your use case but, in general, we recommend using connection pooling. Pooling will protect the database server from large numbers of new connections created too rapidly, which can impact performance. In cloud-hosted environments, you should manage your database connections differently than managing them in a conventional, external server environment.We recommend that you design your applications to handle occasional connection failures by implementing an error handling strategy like exponential backoff. – Thickwitted

@Thickwitted has pasted the most recent text from that link, which no longer infers that connection pooling may not be needed for GAE + CloudSQL. I did find cloud.google.com/sql/docs/mysql/connect-app-engine which says "For App Engine standard environment, there is a hard limit on the number of connections an App Engine instance can have open to Cloud SQL. If your application requires more open connections, consider using connection pooling." – Ratchford

Answer 3

You may be getting the AccessControlException because your application is set to "Automatic Scaling".

It used to be that a GAE app could be a "backend" or a "frontend" and only backends could use background threads. Now with backends deprecated and replaced with modules, the use of background threads is tied to the scaling type of the app.

https://cloud.google.com/appengine/docs/java/modules/#Java_Background_threads

Heterogamete