3

I am not familiar at all with connection pooling library. I've just discovered it through this blog article) and I am not sure that I should use one in my web application based on grails/hibernate/mysql.

So my question is simple : in which situations would you suggest to integrate a connection pooling library into a grails application? Always, Never or only over some connections threshold?

P.S. : If you have ever used successfully C3P0 in your web application, I will greatly appreciate to hear your feedback (in terms of visible positive effects).

fabien7474
  • 16,300
  • 22
  • 96
  • 124

4 Answers4

2

My experience with this is pretty limited, but I ended up using C3P0 for the simple reason that Hibernate does not seem to handle MySQL restarts. I got a "Broken pipe" every morning because our hosting service restarted MySQL every night.

I googled it and the only advice I could find was to use... the connection pool of the app server or C3P0. For me, the latter works just fine.

wwwclaes
  • 1,152
  • 8
  • 12
  • I am having the same Broken pipe issue every day after midnight, we have 2 servers, and one goes down due to this. But mysql is never restarted its been running since long time. Do you still suggest using C3P0 for the same issue? – sufyan.shoaib Nov 03 '15 at 08:06
  • 1
    Well, it's been five years and circumstances differ, so I can't tell for sure. But the code for C3P0 is still there in my application and I don't have any broken pipes, so I'm not going to delete it :-) I'd say give it a try! – wwwclaes Nov 03 '15 at 08:30
2

Regardless of which pooling implementation, you should use a connection pool always in your web application. Open a connection with the database is a very expensive task and being able to reuse a already existing and idle connection greatly improves your site performance.

A connection can be managed by the application server (Tomcat, JBoss, Glassfish...) or by your application. The latter is easier to setup but it's hard to customize per deployment. Configuring a connection pool on the application and setting your site to consume it makes easy to the fine tune the connection pool parameters, like: minimum connections to keep open, max idle time and so on.

Felipe Cypriano
  • 2,727
  • 22
  • 32
  • Thx ! According to this plugin http://www.grails.org/plugin/jdbc-pool, Grails has a default Grails Commons DBCP Pool. Does it mean that grails manages DB connections by application (and not by application server)? If I replace it with Tomcat JDBC Pool, it might have better performance now? – fabien7474 May 10 '10 at 22:05
  • 1
    Yes, by default it's by application. Commons DBCP is very established connection, but if a poor performance and gets worse when the connections quantity increases. It's a great choice to change to Tomcat JDBC Pool, it's a new implementation and I'm looking into it, looks very promising. – Felipe Cypriano May 10 '10 at 23:02
2

I always use a connection pool for two reasons:

  1. Because opening connections is an expensive operation
  2. It's dead-simple to set one up to work transparently, so there's no real advantage to not using one.

If you're already using hibernate, just modify your hibernate.cfg.xml's connection.provider_class to use org.hibernate.connection.C3P0ConnectionProvider and throw the c3p0 jar file into your servlet's WEB-INF/lib folder. Done.

If you're using JNDI and a GlobalNamingResources declaration, modify the type property to point to com.mchange.v2.c3p0.ComboPooledDataSource and throw the c3p0 jar into Tomcat's /lib folder. Done.

Matt Brock
  • 5,337
  • 1
  • 27
  • 26
0

C3P0 is a very decent pool but I would still recommend to use the connection pool of your app server or servlet engine and to configure Grails to use it via a regular DataSource. Use a stand-alone connection pool when you can't do that (in which case C3P0 is a good choice).

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
  • 1
    Hi Pascal. Thx for your answer. Unfortunately it brings me more questions :-) What is the connection pool of the application server (I run my application on Tomcat)? Does Grails by default use the connection pool of the application server? Is it the pooled = true in the DataSource? – fabien7474 May 10 '10 at 15:12