Connection Pool

[javabuddy]

How to establish Connection Pool in GAE using AppEngineDriver in Cloud
SQL or native datastore??

Thanks

[Rémi Bastide]

Good question ! I'm in need of the answer as well...

[Joe Faith]

Database connections in a cloud hosted environment should be managed differently to those on a conventional server. In particular, be aware that your database instance may be taken offline while not in use, and any pooled connections would be closed. We recommend that a new connection is created to service each HTTP request, and re-used for the duration of that request (since the time to create a new connection is similar to that required to test the liveness of an existing connection).

J

--
Joe Faith | Product Manager | Google Cloud

[Dipin Narayanan]

hey, but how about doing like this?


    private Connection connection;

    public void init(ServletConfig c) throws ServletException {
      //Open the connection here
    }

    public void destroy() {
     //Close the connection here
    }

    public void doGet (HttpServletRequest req, HttpServletResponse res) throws ServletException {
      //Use the connection here
      Statement stmt = connection.createStatement();

  }

[Mauricio Aristizabal]

I have been using the commons dbcp pool successfully (with straight JDBC, no JDO/JPA).  It has a facility for testing a connection before returning from pool, which prevents any errors if the db instance 'went to sleep'.  The test query adds 10-15ms seconds before every query, which is acceptable to me.  DBCP has more efficient ways to check for dead connections (e.g. every x seconds while sitting in pool) but these can't be used because they involve starting up new maintenance threads, which aren't allowed in AppEngine.   

Of course, this will prevent any errors, but it won't keep some requests from taking upwards of 15 seconds while they wait for the db instance to come up.  I deal with that by having my monitoring server hit a url every 5 minutes that involves a db call, so even in periods of very low activity the db should stay on.

See more here:

http://commons.apache.org/dbcp/configuration.html

Here's how I wire up my datasource and transaction manager in Spring:

    <bean id="dsSearchIndex" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

        <property name="driverClassName" value="com.google.appengine.api.rdbms.AppEngineDriver"/>

        <property name="url" value="jdbc:google:rdbms://xxxx.com:web-prod:searchindex/ctssearchidx"/>

        <property name="username" value="xxx"/>

        <property name="password" value="xxx"/>

        <property name="testOnBorrow" value="true"/>

        <property name="validationQuery" value="select 1"/>

    </bean>

<bean id="tmSearchIndex" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

   <property name="dataSource" ref="dsSearchIndex"/>

   <qualifier value="searchIndex"/>

</bean>

<tx:annotation-driven mode="aspectj" transaction-manager="tmSearchIndex" />

    <bean id="daoSearchIndex" class="com.commentous.searchindex.SearchIndexJdbcDao">

        <property name="dataSource" ref="dsSearchIndex"/>

    </bean>

[Roland Beuker]

Hello Joe,

Is this the end of this discussion or is Google developing some kind of connection pooling towards Google Cloud SQL?

Regards,

Roland

Op vrijdag 2 maart 2012 18:07:05 UTC+1 schreef Joe Faith het volgende:

[Joe Faith]

Hi Roland

The discussion never ends!

I'd be interested in your use case or requirements.

In particular, since the time to create a new connection is similar to that required to test the liveness of an existing connection, then I'm not sure how a connection pool would help?

j

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Roland Beuker]

Google Cloud SQL could signal the connection pool about a pending shutdown...  Another (easy) solution would be to keep the database instance online while there are current connections. The user is paying for this instance hours so this would be no problem for Google ;)

Regards,

Roland

Op dinsdag 7 mei 2013 23:58:58 UTC+2 schreef Joe Faith het volgende:

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ricardo Meneghin Filho]

How much time is it expected to take to make a new connection? I have an appengine instance on the same region as my second gen cloud sql server and each request takes 300 ~ 1000 ms just to make a connection, which is by far the lengthiest operation in the servlet lifetime.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[paynen]

Hey Ricardo,

How are you timing the operation? Is it possible that this time could also involve the creation of objects? You could attempt to create a temporary Compute Engine instance in the same region and test connection times from there, as well, to get another angle on determining how much the time observed in your timing code is influenced by the instance itself.

Cheers,

Nick
Cloud Platform Community Support