Connection pool on app engine with Cloud SQL

Question

It sounds like the newer cloud SQL JDBC drivers for app engine (1) support connection pooling.

Our app uses Spring+Hibernate and we're trying to use one of the existing java frameworks for connection pooling (BoneCP, C3p0, Hikari), and have failed so far to use any of them because of app engine limitations. Stack trace using Spring+Hibernate+C3p0 below. Did anyone manage to get this to work?

[INFO] java.lang.NoClassDefFoundError: java.lang.management.ManagementFactory is a restricted class. Please see the Google  App Engine developer's guide for more details.
[INFO]  at com.google.appengine.tools.development.agent.runtime.Runtime.reject(Runtime.java:51)
[INFO]  at com.mchange.v2.c3p0.management.ActiveManagementCoordinator.<init>(ActiveManagementCoordinator.java:54)
[INFO]  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[INFO]  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
[INFO]  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
[INFO]  at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
[INFO]  at com.google.appengine.tools.development.agent.runtime.Runtime.newInstance_(Runtime.java:127)
[INFO]  at com.google.appengine.tools.development.agent.runtime.Runtime.newInstance(Runtime.java:148)
[INFO]  at com.mchange.v2.c3p0.C3P0Registry.<clinit>(C3P0Registry.java:146)
[INFO]  at java.lang.Class.forName0(Native Method)
[INFO]  at java.lang.Class.forName(Class.java:190)
[INFO]  at com.google.appengine.tools.development.agent.runtime.RuntimeHelper.checkRestricted(RuntimeHelper.java:70)
[INFO]  at com.google.appengine.tools.development.agent.runtime.Runtime.checkRestricted(Runtime.java:64)
[INFO]  at com.mchange.v2.c3p0.impl.DriverManagerDataSourceBase.<init>(DriverManagerDataSourceBase.java:212)
[INFO]  at com.mchange.v2.c3p0.DriverManagerDataSource.<init>(DriverManagerDataSource.java:72)
....

(1): Old driver = com.google.appengine.api.rdbms.AppEngineDriver. New driver = com.mysql.jdbc.GoogleDriver.

Tychonn

It works, but only on Basic and Manual Scaling instances. Check this post; #38397837 – Osmose

Answer 1

We ended up solving this by using Tomcat DBCP (http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html). The problem with most other pools is that they use threading, which app engine's model prevent on frontend instances (long-lived threads that is).

Tychonn

Were you using JPA, or were you using Tomcat DBCP in the context of manual JDBC operations? I'm struggling to figure out how make a EntityManagerFactory work with a connection pool... since the connection to JPA generally works via JNDI, and Google App Engine does not support JNDI at all. – Purdy

im suprised that they dont support JNDI , it was supposed to be the de factor pooling mechanism for java based web apps . – Lina

Answer 2

This is an old question, but I wanted to provide what I believe is a more accurate answer. Google App Engine does allow applications to create threads through the use of their ThreadFactory.

HikariCP allows configuring an external ThreadFactory.

So, the configuration would go something like this:

import com.google.appengine.api.ThreadManager;
...

HikariConfig config = new HikariConfig();
config.setThreadFactory(ThreadManager.backgroundThreadFactory());
...

UPDATE: addressing the comment below about frontend instances... as noted elsewhere:

"Keep in mind that instances are created and destroyed dynamically, and requests are routed
to instances based purely on availability. ... There is no guarantee that requests of a
particular sort will always be handled by the same instance, nor is it assured that an
instance will still be around after a given request is handled. Outside of a request
handler, the application is not given the opportunity to rescue data from local memory
prior to an instance being shut down."

This substantially decreases the usefulness of connection pools on the frontend. In fact, it is a bad idea if the database is not in-memory, as it can create substantial churn of connections. With respect to local in-memory databases, not only are they fragile in the context of GAE, "connection overhead" is rarely a scalability factor warranting the use of a pool.

Harlandharle

The caveat is that the application must be a backend application on GAE for this to work. For example, if you have a frontend-only application which tries to utilize an in-memory database such as H2 or HSQL, this won't work and will throw an error on start up. – Matthews

Now with backends deprecated and replaced with modules, the use of background threads is tied to the scaling type of the app. If your app is set to "Automatic Scaling" you'll get an exception. – Examinee

Is this still valid? Where do we configure it? – Florio

#38397837 – Osmose

Answer 3

I created an example using Tomcat DBCP 1.4 for GAE frontend instances, which do not allow threads to live outside of request scope. This works with Java 7.

https://github.com/kennberg/appengine-java-connection-pool

Dominions