Showing posts with label maxLifetime. Show all posts
Showing posts with label maxLifetime. Show all posts

Saturday, May 20, 2017

Hikari Connection Pool Configuration

Here are some configuration information about Hikari Connection Pool Library


Spring Bean Configuration


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:beans="http://www.springframework.org/schema/beans"
      xmlns:context="http://www.springframework.org/schema/context"
      xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx"
      xmlns:util="http://www.springframework.org/schema/util"
      xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
      http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
      http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
      http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
      http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

      <!-- Enable Annotation based Declarative Transaction Management -->
      <tx:annotation-driven proxy-target-class="true" transaction-manager="transactionManager" />

      <bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
            <property name="poolName" value="springHikariCP" />
            <property name="connectionTestQuery" value="SELECT 1" />
            <property name="dataSourceClassName" value="org.springframework.jdbc.datasource.DriverManagerDataSource" />
            <property name="connectionTimeout" value="${database.connection.connectionTimeout}" />
            <property name="validationTimeout" value="${database.connection.validationTimeout}" />
            <property name="idleTimeout" value="${database.connection.idleTimeout}" />
            <property name="maximumPoolSize" value="${database.connection.maximumPoolSize}" />
            <property name="minimumIdle" value="${database.connection.minimumIdle}" />
            <property name="leakDetectionThreshold" value="${database.connection.leakDetectionThreshold}" />
            <property name="maxLifetime" value="${database.connection.maxLifetime}" />
            <property name="autoCommit" value="false" />
            <property name="registerMbeans" value="true" />

            <property name="dataSourceProperties">
                  <props>
                        <prop key="url">${database.url}</prop>
                        <prop key="username">${database.username}</prop>
                        <prop key="password">${database.password}</prop>
                  </props>
            </property>
      </bean>

      <!-- HikariCP configuration -->
      <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"
            destroy-method="close">
            <constructor-arg ref="hikariConfig" />
      </bean>

      <!-- JDBC Template -->
      <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <constructor-arg ref="dataSource" />
      </bean>

      <!-- Creating TransactionManager Bean, since JDBC we are creating of type DataSourceTransactionManager -->
      <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource" />
      </bean>

      <jdbc:initialize-database>
            <jdbc:script location="classpath:database/dml/parm_inserts.sql" />
      </jdbc:initialize-database>
</beans>



In properties file


database.connection.connectionTimeout=30000000
database.connection.validationTimeout=15000000
database.connection.idleTimeout=10000000
database.connection.maximumPoolSize=5
database.connection.minimumIdle=1
database.connection.leakDetectionThreshold=20000000
database.connection.maxLifetime=30000000


A utility class that logs the Hikari metrics to log file


@Service
public class DatabaseConnectionPoolMXBean {
      private static Logger logger = LoggerFactory.getLogger(DatabaseConnectionPoolMXBeam.class);

      public void logConnectionStatistics() {
            final MBeanServer mBeanServer = ManagementFactory.getPlatformMBeanServer();
            ObjectName poolName = null;
            try {
                  poolName = new ObjectName("com.zaxxer.hikari:type=Pool (springHikariCP)");
            } catch (MalformedObjectNameException e) {
                  logger.warn("failed to create ObjectName: com.zaxxer.hikari:type=Pool (springHikariCP).", e);
            }
            final HikariPoolMXBean poolProxy = JMX.newMXBeanProxy(mBeanServer, poolName, HikariPoolMXBean.class);

            final int totalConnections = poolProxy.getTotalConnections();
            final int activeConnections = poolProxy.getActiveConnections();
            final int idleConnections = poolProxy.getIdleConnections();
            final int TheadsAwaitingConnections = poolProxy.getThreadsAwaitingConnection();

            final StringBuilder result = new StringBuilder("DatabaseConnectionPoolMXBeam, DatabasePool-totalConnections=")
                        .append(totalConnections).append(", DatabasePool-activeConnections=").append(activeConnections)
                        .append(", DatabasePool-idleConnections=").append(idleConnections)
                        .append(", DatabasePool-theadsAwaitingConnections=").append(TheadsAwaitingConnections);
            logger.info(result.toString());

      }