Menu ▾ ▴

Using in a connection pool with ha-jdbc

Help
Amit Shah
2013-03-15
2013-03-19

  • Amit Shah

    Amit Shah - 2013-03-15

    I wanted to integrate tomcat jdbc connection pool with HA-JDBC. From the ha-jdbc documentation I read, I understand that datasource properties could be set by calling DataSourceDatabase.setProperty(key, value) method. Since tomcat jdbc pool configuration involves an object inside an object, I am looking for some help on how do I configure tomcat-jdbc-pool with ha-jdbc.

    Here is a sample configuration code for tomcat jdbc pool

        PoolConfiguration poolConfig = new PoolProperties();
    poolConfig.setName(name);
    poolConfig.setDataSource(getOracleDataSource(config));

    poolConfig.setUsername(config.getUser());
    poolConfig.setPassword(config.getPassword());

    poolConfig.setMaxActive(config.getMaxPoolSize());
    poolConfig.setMinIdle(config.getMinPoolSize());
    poolConfig.setInitialSize(config.getMinPoolSize());
    poolConfig.setMinEvictableIdleTimeMillis(config.getMaxIdleTime() * 1000);
    poolConfig.setTestWhileIdle(false);
    poolConfig.setTestOnBorrow(true);
    poolConfig.setValidationQuery("select 1 from dual");
    poolConfig.setInitSQL(config.getInitSql());
    poolConfig.setJdbcInterceptors("ConnectionState;StatementFinalizer");

    org.apache.tomcat.jdbc.pool.DataSource tomcatDataSource =
            new org.apache.tomcat.jdbc.pool.DataSource();
    tomcatDataSource.setPoolProperties(poolConfig);

    MBeanServer mBeanServer = ManagementFactory.getPlatformMBeanServer();

    try {
        tomcatDataSource.createPool();
        mBeanServer.registerMBean(tomcatDataSource.getPool().getJmxPool(), getObjectName(name));
    } catch (Exception e) {
        throw new RuntimeException("Error registering the connection pool data source", e);

    Thanks.

     

  • Paul Ferraro

    Paul Ferraro - 2013-03-15

    You have two options. Either you can configure tomcat's connection pool to access HA-JDBC's datasource (i.e. a pool of HA-JDBC connections), or you can configure HA-JDBC to access individual connection pools, one per database, where each database is a org.apache.tomcat.jdbc.pool.DataSource.

    Given your code fragments, you appear to be configuring both your connection pool and HA-JDBC programmatically, so I'll use similar conventions.

    The former method is fairly straightforward. I'm assuming that you're configuring everything programatically. This makes things easy. Your pool config would reference the HA-JDBC datasource.
    e.g.

    net.sf.hajdbc.sql.DataSource ds = new net.sf.hajdbc.sql.DataSource();
ds.setConfigurationFactory(...);
ds.setCluster("cluster");
PoolConfiguration config = new PoolProperties();
config.setDataSource(ds);

    The rest of the configuration looks the same as above.

    To configure HA-JDBC using the latter method, you would create 2 tomcat datasources, each with a pool config that references the Oracle datasource.
    e.g.

    org.apache.tomcat.jdbc.pool.DataSource tomcatDS1 = ...;
org.apache.tomcat.jdbc.pool.DataSource tomcatDS2 = ...;

    Bind each of these to JNDI, then reference them in your HA-JDBC DataSourceDatabase by jndi name.
    e.g.

    DataSourceDatabase db1 = new DataSourceDatabase();
db1.setLocation("java:comp/env/ds/tomcatDS1");
DataSourceDatabase db2 = new DataSourceDatabase();
db2.setLocation("java:comp/env/ds/tomcatDS2");
     

    Last edit: Paul Ferraro 2013-03-15

    • Kevin Richards

      Kevin Richards - 2013-03-15

      I seem to remember that with hajdbc on top of some connection pools there
      was an issue with hajdbc getting into a deadlock is that still the case.

      You have two options. Either you can configure tomcat's connection pool to
      access HA-JDBC's datasource (i.e. a pool of HA-JDBC connections), or you
      can configure HA-JDBC to access individual connection pools, one per
      database, where each database is a org.apache.tomcat.jdbc.pool.DataSource.

      Given your code fragments, you appear to be configuring both your
      connection pool and HA-JDBC programmatically, so I'll use similar
      conventions.

      The former method is fairly straightforward. I'm assuming that you're
      configuring everything programatically. This makes things easy. Your pool
      config would reference the HA-JDBC datasource.
      e.g.

      net.sf.hajdbc.sql.DataSource ds = new
      net.sf.hajdbc.sql.DataSource();ds.setConfigurationFactory(...);ds.setCluster("cluster");PoolConfiguration
      config = new PoolProperties();config.setDataSource(ds);

      The rest of the configuration looks the same as above.

      To configure HA-JDBC using the latter method, you would create 2 tomcat
      datasources, each with a pool config that references the Oracle datasource.
      e.g.

      org.apache.tomcat.jdbc.pool.DataSource tomcatDS1 =
      ...;org.apache.tomcat.jdbc.pool.DataSource tomcatDS2 = ...;

      Bind each of these to JNDI, then reference them in your HA-JDBC
      DataSourceDatabase by jndi name.
      e.g.
      DataSourceDatabase db1 = new DataSourceDatabase();
      db1.setLocation("java:comp/env/ds/tomcatDS1");
      DataSourceDatabase db2 = new DataSourceDatabase();
      db2.setLocation("java:comp/env/ds/tomcatDS2");

      Using in a connection pool with
      ha-jdbchttps://sourceforge.net/p/ha-jdbc/discussion/383397/thread/aa333c4f/?limit=25#b3e0

      Sent from sourceforge.net because you indicated interest in
      https://sourceforge.net/p/ha-jdbc/discussion/383397/

      To unsubscribe from further messages, please visit
      https://sourceforge.net/auth/prefs/

       

  • Paul Ferraro

    Paul Ferraro - 2013-03-16

    If I recall correctly, the deadlocking was because the JDK DriverManager.getConnection(...) method was synchronized. As of Java 1.6, this is no longer the case.

    I general, I recommend configuring HA-JDBC on top of your connection pool (i.e. as a cluster of connection pools, instead of a single connection pool of HA-JDBC connections). In this configuration, it is important to enable testOnBorrow to prevent a stale connection in the pool from triggering an unnecessary database deactivation.

     

    • Kevin Richards

      Kevin Richards - 2013-03-16

      No the problem was that with a round robin setup you could empty both
      connection pools and then threads were deadlocked waiting for a connection
      to become available.

      On 16 Mar 2013 16:48, "Paul Ferraro" ferraro@users.sf.net wrote:

      If I recall correctly, the deadlocking was because the JDK
      DriverManager.getConnection(...) method was synchronized. As of Java 1.6,
      this is no longer the case.
      I general, I recommend configuring HA-JDBC on top of your connection pool
      (i.e. as a cluster of connection pools, instead of a single connection pool
      of HA-JDBC connections). In this configuration, it is important to enable
      testOnBorrow to prevent a stale connection in the pool from triggering an
      unnecessary database deactivation.

      Using in a connection pool with ha-jdbchttps://sourceforge.net/p/ha-jdbc/discussion/383397/thread/aa333c4f/?limit=25#144d

      Sent from sourceforge.net because you indicated interest in
      https://sourceforge.net/p/ha-jdbc/discussion/383397/

      To unsubscribe from further messages, please visit
      https://sourceforge.net/auth/prefs/

       

    • Amit Shah

      Amit Shah - 2013-03-18

      I didn't understand the reason why you mention that using HA-JDBC on top of tomcat connection pool (which means the second option from your original mail). The point about testOnBorrow would apply to the first option if I understand it correctly.

      One issue I see with the second option is that I need to set up datasources using JNDI which could be an issue since we create datasource at runtime in our application (connecting to multiple db servers).

      I tried implementing the first option of using the tomcat over the ha-jdbc cluster. But it fails with invalid username/password error since ha-jdbc tries to connect using the cluster data source. The code snippet is as below.

      private javax.sql.DataSource getClusteredDataSourceWithTomcatPool() throws SQLException {
    PoolConfiguration poolConfig = new PoolProperties();
    String poolName = "Tomcat Pool";
    poolConfig.setName(poolName);
    poolConfig.setDataSource(getHA_JDBC_ClusteredDataSource());

    poolConfig.setMaxActive(10);
    poolConfig.setMinIdle(3);
    poolConfig.setInitialSize(3);
    poolConfig.setMinEvictableIdleTimeMillis(30 * 1000);
    poolConfig.setTestWhileIdle(false);
    poolConfig.setTestOnBorrow(true);
    poolConfig.setValidationQuery("select 1 from dual");

    poolConfig.setJdbcInterceptors("ConnectionState;StatementFinalizer");

    org.apache.tomcat.jdbc.pool.DataSource tomcatDataSource =
            new org.apache.tomcat.jdbc.pool.DataSource();
    tomcatDataSource.setPoolProperties(poolConfig);

    MBeanServer mBeanServer = ManagementFactory.getPlatformMBeanServer();

    try {
        tomcatDataSource.createPool();
        mBeanServer.registerMBean(tomcatDataSource.getPool().getJmxPool(), getObjectName(poolName));
    } catch (Exception e) {
        throw new RuntimeException("Error registering the connection pool data source for central schema", e);
    }

    return tomcatDataSource;
}

private ObjectName getObjectName(String name) throws MalformedObjectNameException {
    String jmxObjectName = "connectionpool:type=" + name;
    return new ObjectName(jmxObjectName);
}

private javax.sql.DataSource getHA_JDBC_ClusteredDataSource() throws SQLException {
    DataSourceDatabase db1 = new DataSourceDatabase();
    //...server 1 details
    db1.setWeight(2);

    DataSourceDatabase db2 = new DataSourceDatabase();
    //...server 2 details
    db2.setWeight(1);

    DataSourceDatabaseClusterConfiguration config = new DataSourceDatabaseClusterConfiguration();
    config.setDatabases(Lists.newArrayList(db1, db2));
    try {
        config.setAutoActivationExpression(new CronExpression("0 0/1 * * * ?"));
    } catch (ParseException e) {
        throw new RuntimeException("Unable to parse the auto activation schedule", e);
    }

    config.setDialectFactory(new OracleDialectFactory());
    config.setStateManagerFactory(new SimpleStateManagerFactory());
    config.setDatabaseMetaDataCacheFactory(new SimpleDatabaseMetaDataCacheFactory());
    config.setDurabilityFactory(new NoDurabilityFactory());
    config.setBalancerFactory(new SimpleBalancerFactory());

    Map<String, SynchronizationStrategy> synchronizationStrategies = Maps.newHashMap();
    synchronizationStrategies.put("passive", new PassiveSynchronizationStrategy());
    config.setSynchronizationStrategyMap(synchronizationStrategies);
    config.setDefaultSynchronizationStrategy("passive");

    DataSource ds = new DataSource();
    ds.setCluster("cluster");
    ds.setConfigurationFactory(new SimpleDatabaseClusterConfigurationFactory<>(config));

    return ds;
}

      Kindly let me know your inputs.

      Thanks.

       

      • Paul Ferraro

        Paul Ferraro - 2013-03-18

        The authentication information defined by your DataSourceDatabase instances is not used when your application attempts to obtain a connection from the connection pool. The reason for this has to do with the different authorization requirements. The user defined by the DataSourceDatabase should have administrative/superuser privileges on the database (e.g. be able to create/drop schemas/tables, constraints, indexes, etc.) whereas the database user used by your application will typically have a subset of these privileges (e.g. insert/update/delete/select). Consequently, you will need to configure your connection pool with the appropriate username/password.
        e.g.

        poolConfig.setUsername(...);
poolConfig.setPassword(...);

        When tomcat's connection pool attempts to create a connection to your actual databases, it will use the username/password defined above. Anytime HA-JDBC needs to access your database on its own (e.g. for database synchronization, when determining the initial cluster state, etc.) it will use the username/password defined in your DatabaseClusterConfiguration.

        N.B. While the admin username/password can be different on each database, the username/password used by your application must be the same on each database.

         

        • Amit Shah

          Amit Shah - 2013-03-18

          Ok. It works with your suggestion of setting the username/password in the tomcat connection pool configuration.

          Do you see any other config options which should be taken care off when I configure with the first option i.e. tomcat connection pool over ha-jdbc cluster. I have made a note of testOnBorrow setting to avoid stale connections.

           

          • Amit Shah

            Amit Shah - 2013-03-19

            One downside I realized with the first option is that the tomcat pool could at a time contain connections to different servers. DML operations could be directed to any of the db-servers at runtime. Any replication mechanism used external to ha-jdbc to synchronize the databases in the cluster would have to account for this i.e. multi-directional replication.

             

            • Paul Ferraro

              Paul Ferraro - 2013-03-19

              Actually, with the 1st option (a pool of HA-JDBC connections), your pool will always contain connections to each database. During runtime, HA-JDBC will always direct DML operations to all databases (hence the need to have connections to each database).

              As I explained in another thread, HA-JDBC was not designed to be used in conjunction with an external replication mechanism. If you're looking for a simple connection failover mechanism, I listed a few in the other thread.

               

  • Paul Ferraro

    Paul Ferraro - 2013-03-16

    Refresh my memory... Exactly which threads were deadlocking? Why would the connection pools get emptied?

     

    • Kevin Richards

      Kevin Richards - 2013-03-16

      The scenario was very heavy load which was using all of the db connections.

      Two threads would take the last connection from two pools and then block
      waiting to get a connection from the other pool which never got freed up.
      Hence the deadlock.

      I seem to recall the solution was to make the choosing of the first pool
      predictable.

      Kev
      On 16 Mar 2013 18:10, "Paul Ferraro" ferraro@users.sf.net wrote:

      Refresh my memory... Exactly which threads were deadlocking? Why would the
      connection pools get emptied?

      Using in a connection pool with ha-jdbchttps://sourceforge.net/p/ha-jdbc/discussion/383397/thread/aa333c4f/?limit=25#e190

      Sent from sourceforge.net because you indicated interest in
      https://sourceforge.net/p/ha-jdbc/discussion/383397/

      To unsubscribe from further messages, please visit
      https://sourceforge.net/auth/prefs/

       

Log in to post a comment.