Persistent Database connections (uberJar, beta, google cloud)

Support
,
#1

Hi,

I just successfully deployed a singleUberJar app to google app engine with google cloud sql (posgres) but I have the feeling SQL connections are not persistent:

Every app action drops this on the log:

com.google.cloud.sql.postgres.SocketFactory createSocket
INFO: Connecting to Cloud SQL instance [xx] via ssl socket.
com.google.cloud.sql.core.SslSocketFactory createAndConfigureSocket
INFO: Connecting to Cloud SQL instance [xx] on IP [xx].

So, it appears that the sql connection is dropped on every request and then recreated. I really have no idea if it is because of the uberJar, app engine, cloud sql… or even the postgresql driver. So I start guessing here! :smiley:

What have I done up to here?

  • Updated the postgresql dependency in gradle (the default does not work with google)
  • Added dependency to google cloud sql socket factory
  • Configured uberJar
def postgres = 'org.postgresql:postgresql:42.2.5'  // On build.gradle where it already is
compile 'com.google.cloud.sql:postgres-socket-factory:1.0.11'  // on the configure(coreModule) dependencies

task buildUberJar(type: CubaUberJarBuilding) {
    coreJettyEnvPath = "docker-image/container-files/jetty-env.xml"
    logbackConfigurationFile = "docker-image/container-files/uber-jar-logback.xml"
    distributionDir = "${project.rootDir}/docker-image/uberJar"
    singleJar = true
    appProperties = ['cuba.automaticDatabaseUpdate': true]
}

The jetty-env and logback files are the ones generated by CUBA so won’t copy here.
I did change the DB connection information on the jetty-env though, the url is (as per google docs):

jdbc:postgresql://google/DBNAME?cloudSqlInstance=instanceUrl&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=postgres&password=paspaspaspas

Note that database connections are successful. All works perfecly. Simply, the connections are dropped too often. Maybe something in org.apache.commons.dbcp2.BasicDataSource?

marc

#3

Hi Marc,

I think the pool configuration depends on maxIdle and initialSize values (DBCP – BasicDataSource Configuration)

Try to configure pool with the same values for maxIdle, initialSize and maxTotal. And check logs for reconnections.

Sample conf:

<New class="org.apache.commons.dbcp2.BasicDataSource">
      <Set name="driverClassName">org.postgresql.Driver</Set>
      <Set name="url">jdbc:postgresql://localhost/db</Set>
      <Set name="username">pass</Set>
      <Set name="password">pass</Set>
      <Set name="initialSize">20</Set>
      <Set name="maxIdle">20</Set>
      <Set name="maxTotal">20</Set>
      <Set name="maxWaitMillis">5000</Set>
</New>
1 Like