Hibernate connection pool provider for Cloud SQL

Question

I'm using Hibernate in combination with GAE and Cloud SQL.

Everything works fine, but when an instance is waken up, sometimes it takes a long time to connect to the database (up to 15s)

In the log I see that the most time it is Hibernate establishing a connection:

   I 17:46:35.936 org.hibernate.cfg.Configuration doConfigure: HHH000041: Configured SessionFactory: null
   W 17:46:36.209 org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure: HHH000402: Using Hibernate built-in connection pool (not for production use!)
   I 17:46:36.243 org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure: HHH000115: Hibernate connection pool size: 0
   I 17:46:36.244 org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure: HHH000006: Autocommit mode: false
   I 17:46:36.244 org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure: HHH000401: using driver [com.mysql.jdbc.GoogleDriver] at URL [jdbc:google:mysql://database?user=someUser]
   I 17:46:36.245 org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure: HHH000046: Connection properties: {}
   I 17:46:45.356 org.hibernate.dialect.Dialect <init>: HHH000400: Using dialect: org.hibernate.dialect.MySQL5InnoDBDialect

Here is the code producing this:

properties.put("hibernate.connection.driver_class", "com.mysql.jdbc.GoogleDriver");
properties.put("hibernate.connection.url", "jdbc:google:mysql://" + SQL_INSTANCE + "/" + DATABASE + "?user=someUser");

// disable schema check
properties.put("hibernate.hbm2ddl.auto", ""); // disable
properties.put("hibernate.show_sql", false);            

// single session per request/thread
properties.put("hibernate.connection.pool_size", 0);
properties.put("hibernate.current_session_context_class", "thread");

Configuration cfg = new Configuration();
cfg.configure("/META-INF/hibernate.cfg.xml");
cfg.addProperties(properties);

sessionFactory = cfg.buildSessionFactory(new ServiceRegistryBuilder().applySettings(cfg.getProperties()).buildServiceRegistry());

Following method provides the session

public Session getSession() {

        Session session;
        try {
            session = sessionFactory.getCurrentSession();
        }
        catch (org.hibernate.HibernateException he) {
            log.info("Opening new hibernate session.");
            session = sessionFactory.openSession();
        }

        return session;
    }

I'm setting the connection pool size to 0 (as recommended), but apparently Hibernates build in connection pool manager is used.

My question is, which connection pool manager should be used in this setup, and what else can be done to speed up connection time.

What are the best practises for this kind of setup, as I can not find anything but some trivial examples.

Answer 1

I recommend you using HikariCP, which is probably the fastest open-source CP.

I have a GitHub project you could clone and check my configuration for Hibernate local transaction configuration. Although the example uses DBCP you only need to replace the DataSource configuration with the HikariDataSource one.

If you are using only Hibernate API and don;t want to rely on SPring, then after you create your HikariDataSource object, you need to send it to Hibernate configuration:

@Override
protected SessionFactory newSessionFactory() {
    Properties properties = new Properties();
    ...
    //data source settings
    properties.put("hibernate.connection.datasource", hikariDataSource);
    return new Configuration()
    .addProperties(properties)
    .addAnnotatedClass(SecurityId.class)
    .buildSessionFactory(
            new StandardServiceRegistryBuilder()
            .applySettings(properties)
    .build()
);