Search code examples
javasqlgoogle-app-enginegoogle-cloud-platformgoogle-cloud-sql

Best way to connect Cloud SQL via connection pooling in GAE Flex

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

Everything works fine, but when an instance is waken up or after some cold time(no request served), it takes a long time to connect to the database (up to 8s) while the execution of query only takes in ms. Also it creates serviceRegistry again and again.

Can anyone please suggest what is best approach to connect with Cloud SQL avoiding higher latency in gaining connection.

PS: I am using cp30 for connection pooling.

Logs from app engine:

enter image description here

enter image description here HibernateUtil Config:

        props.put("hibernate.hbm2ddl.auto", "validate");
        props.put("hibernate.dialect", "org.hibernate.dialect.MySQL5InnoDBDialect");
        props.put("hibernate.generate_statistics", "true");
        props.put("hibernate.cache.use_query_cache", "true");
        props.put("hibernate.transaction.coordinator_class", "org.hibernate.transaction.JDBCTransactionFactory");
        props.put("hibernate.cache.region.factory_class", "org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory");
        props.put("hibernate.cache.use_second_level_cache", "true");

        props.put("connection.provider_class", "org.hibernate.connection.C3P0ConnectionProvider");
        props.put("hibernate.c3p0.min_size", "40");
        props.put("hibernate.c3p0.max_size", "250");
        props.put("hibernate.c3p0.acquire_increment", "1");
        props.put("hibernate.c3p0.testConnectionOnCheckin", "true");
        props.put("hibernate.c3p0.idle_test_period", "300");
        props.put("hibernate.c3p0.maxIdleTimeExcessConnections", "240");
        props.put("hibernate.c3p0.preferredTestQuery", "SELECT 1");
         ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
        System.out.println("Hibernate Java Config serviceRegistry created");

        SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);

        return sessionFactory;
Solution

  • Unless you are using manual scaling, your instances are often terminated and restarted. The instances have to reload all the libraries and configurations when they start so it's expected behavior for it to take longer.

    There are two easy options for your use case:

    1) Use a Compute Engine instance, which allows you to load your code, libraries and configuration and it's a dedicated VM that will continue to serve your requests and it doesn't "scale down" (terminated). This is definitely the safest method.

    2) Use manual scaling to scale your App Engine Flex app. This is almost as effective as the first option, but instances can still be restarted if they were unhealthy. In which case, make sure you have health check setup to heal your instances if something happens. This will keep them alive and working as much as possible.