Initializing C3P0 connection pool takes 2 min

Question

I can't wrap my head around why the initialization of a c3p0 connection pool takes 2 min in my Hibernate application.

This is in my Hibernate.cfg.xml:

<hibernate-configuration>
    <session-factory>
        <property name="connection.driver_class">org.postgresql.Driver</property>
        <property name="connection.url"/>
        <property name="connection.default_schema"/>
        <property name="connection.username"/>
        <property name="connection.password"/> 

        <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>
        <property name="current_session_context_class">thread</property>

        <property name="hibernate.c3p0.acquire_increment">1</property>
        <property name="hibernate.c3p0.min_size">3</property>
        <property name="hibernate.c3p0.max_size">10</property>
        <property name="hibernate.c3p0.timeout">300</property>
        <property name="hibernate.c3p0.max_statements">50</property>
        <property name="hibernate.c3p0.idle_test_period">3000</property>
        <property name="hibernate.c3p0."></property>

        <property name="show_sql">true</property>
        <property name="format_sql">false</property>

        <property name="hbm2ddl.auto">create</property>
 </session-factory>
</hibernate-configuration>

The connection settings are set in my HibernateUtil file when building the session factory.

The pool is initialize when the first transaction in my tests is openend. Connecting and querying the db works just fine afterwards, it only hangs on the following line for a while before it will start. I formated the output a bit as I assume the problem may be with one of the settings mentioned here.:

INFO: Initializing c3p0 pool... 
com.mchange.v2.c3p0.PoolBackedDataSource@30670080 [
  connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@ecfec4d0 [
    acquireIncrement -> 1,
    acquireRetryAttempts -> 30,
    acquireRetryDelay -> 1000,
    autoCommitOnClose -> false,
    automaticTestTable -> null,
    breakAfterAcquireFailure -> false,
    checkoutTimeout -> 0,
    connectionCustomizerClassName -> null,
    connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester,        
    debugUnreturnedConnectionStackTraces -> false,
    factoryClassLocation -> null,
    forceIgnoreUnresolvedTransactions -> false,
    identityToken -> I-REMOVED-THIS,
    idleConnectionTestPeriod -> 3000,
    initialPoolSize -> 3,
    maxAdministrativeTaskTime -> 0,
    maxConnectionAge -> 0,
    maxIdleTime -> 300,
    maxIdleTimeExcessConnections -> 0,
    maxPoolSize -> 10,
    maxStatements -> 50,
    maxStatementsPerConnection -> 0,
    minPoolSize -> 3,
    nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@b17e5c65 [
      description -> null,
      driverClass -> null,
      factoryClassLocation -> null,
      identityToken -> I-REMOVED-THIS,
      jdbcUrl -> jdbc:postgresql://URL-TO-MY_DB,
      properties -> {user=******, password=******, default_schema=}
    ],
    preferredTestQuery -> null,
    propertyCycle -> 0,
    testConnectionOnCheckin -> false,
    testConnectionOnCheckout -> false,
    unreturnedConnectionTimeout -> 0,
    usesTraditionalReflectiveProxies -> false;
    userOverrides: {}
  ],
  dataSourceName -> null,
  factoryClassLocation -> null,
  identityToken -> I-REMOVED-THIS,
  numHelperThreads -> 3
]

It's the first time I'm using Hibernate and c3p0 and I was expecting it to be much quicker when starting the pool? Is it a misconception of mine?

It's no difference between using the remote DB nor a local PostgreSQL instance.

(Edit: This is not true. I made a mistake when comparing local and remote db server. Locally, initialization is pretty much immediately, remotely it takes around 2 minutes.)

Edit2: Here is a log of the connection process.

Typically it should be "immidiatly". Maybe try another pooling lib to make sure it is a c3p0 problem, like BoneCP (jolbox.com). — Adrian
hi. first, what version of c3p0 are you using? can you try upgrading to the most recent (c3p0-0.9.2-pre8), if you are using c3p0-0.9.1.x? re your settings: please choose an acquireIncrement greater than 1 (the default of three is probably ok, given your pool size). you might try setting acquireRetryDelay to a lower value -- if that speeds up your initialization, it means c3p0 is experiencing occasional failures on Connection acquisition. (if you set the level of the logger to com.mchange.v2.resourcepool.BasicResourcePool to FINE or DEBUG, you'll see these failures.) — Steve Waldman
I made a mistake when comparing initialization on local and remote db server. Sry, for the confusion. Locally, initialization is pretty much immediately, remotely it takes around 2 minutes. I tested it with a Heroku Dev instance of Postgres. Is this still an atypical long time? — bentrm
@SteveWaldman The c3p0 logging is set to debug and I could not find a way to have more granular logging of the init and connection process. Could you offer a hint how to enable logging for com.mchange.v2.resourcepool.BasicResourcePool? — bentrm
it depends on your logging library -- com.mchange.v2.resourcepool.BasicResourcePool is the logger for which you want to enable more detailed loggers. if you are using the Java standard logging library, there should be a logging.properties file. if you are using log4j, log4j.properties. c3p0 dumps which logging framework it is using on startup, look for a line like "MLog clients using", and then a string identifying the logging lib. — Steve Waldman

fabriciobc fabriciobc · Accepted Answer · 2014-04-08T14:01:47

Set the property Hibernate.temp.use_jdbc_metadata_defaults to false in the configuration of your session factory. This will indicate to Hibernate using metadata dialect instead of the connection, which makes the slow startup. You must also configure an appropriate dialect for your driver.

Initializing C3P0 connection pool takes 2 min

1 Answers